[postgis-devel] geometry_columns improving performance for constraint columns

[Paul Ramsey]

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
>