[postgis-devel] geometry_columns improving performance for constraint columns

[Paragon Corporation]

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