[postgis-devel] geometry_columns improving performance for constraint columns

[Paragon Corporation]

Hey you're the one that wanted to speed it up. I was fine with letting constraint users suffer :).

-----Original Message-----
From: Paul Ramsey [mailto:pramsey at cleverelephant.ca] 
Sent: Monday, April 27, 2015 8:27 AM
To: PostGIS Development Discussion; Paragon Corporation
Subject: Re: [postgis-devel] geometry_columns improving performance for constraint columns

Fine w/ me.
So much effort to check constraints  (which is a 1.x compatibility layer ) P 


--
http://postgis.net
http://cleverelephant.ca


On April 27, 2015 at 3:55:39 AM, Paragon Corporation (lr at pcorp.us) wrote:
> I clicked sent too quickly, but I did put on the ticket.
> http://trac.osgeo.org/postgis/ticket/3092 (see my note to you on that 
> ticket :) )
>  
> I've committed to both 2.2 and 2.1 already but forgot to do the test 
> of if you ONLY have typmod columns. I suspect we'll have a regress in 
> performance, but have to check how bad that is.
>  
> Below stats are based on the 3 LEFT JOIN solution I settled on 
> compared to what we had before. Also if we can agree to get rid of the 
> varchar(256) casting in 2.2, we'll gain more performance to 
> table/column lookups which I think is used often in mapserver (when it 
> does the find_srid check). Also need to check 9.1 and 9.0.
>  
> -- 30000, 9.5 old 340 ms, new 740ms
> -- 9.3 old 270 ms, new 480ms
> -- 9.2 old 330 ms, new 450 ms
> SELECT count(*) from geometry_columns;
>  
> -- 30000 records, 9.5alpha old 222,260 ms, new 2030 ms
> -- 9.3 old 349,659ms, new 2080 ms
> -- 9.2.10 old > 730,000 ms (stopped the process), new 2320 ms SELECT * 
> FROM geometry_columns;
>  
> -- 5000 records, 9.5 old 147,280 ms, new 560 ms
> -- 9.3 old 127,900 ms, new 380 ms
> -- 9.2 old - didn't bother, new 570 ms SELECT * FROM geometry_columns 
> WHERE type = 'POINT';
>  
> -- 20000 records, old 9.5 148,270 ms, new 1370 ms
> -- 9.3 old 258,801 ms, new 1410 ms
> -- 9.2.10 old didn't bother, new 1630 ms SELECT * FROM 
> geometry_columns WHERE type = 'LINESTRING';
>  
> -- 2 records, 9.5 old 210ms, new 9.5 70-160 ms
> -- 9.3 old 220ms, new 170-180 ms
> -- 9.2.10 new 200-210ms
> SELECT * FROM geometry_columns
> WHERE f_table_name = 'constraint_test_linestring_1382';
>  
> Thanks,
> Regina
>  
> -----Original Message-----
> From: postgis-devel-bounces at lists.osgeo.org
> [mailto:postgis-devel-bounces at lists.osgeo.org] On Behalf Of Sandro 
> Santilli
> Sent: Monday, April 27, 2015 3:54 AM
> To: PostGIS Development Discussion
> Subject: Re: [postgis-devel] geometry_columns improving performance 
> for constraint columns
>  
> On Sat, Apr 25, 2015 at 03:23:45PM -0400, Paragon Corporation wrote:
>  
> > I'll post to the ticket and commit if we are all in agreement, but 
> > the stats for this are as follows:
> >
> > Thanks,
> > Regina
>  
> ... nothing followed ...
>  
> --strk;
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
>  
>  
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
>