[postgis-devel] geometry_columns improving performance for constraint columns

Paul Ramsey pramsey at cleverelephant.ca
Sat Apr 25 08:56:31 PDT 2015


I could probably strip the CTE out of mine, and maybe even the functions, I was just too intellectually lazy to try. This is why I should have left it to the SQL master (Although apparently not the regex master? What is your objection to regular expressions?)

I’m  fine w/ whatever you like, though I think filtering is probably not as common a use case as just “gimmee the list of tables”

P.


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


On April 24, 2015 at 10:23:36 PM, Paragon Corporation (lr at pcorp.us) wrote:
> Slight correction. My SOLUTION 2 test metrics were wrong. So solution 2
> does much better than I had stated. Not sure if that was a fluke or I was
> accidentally testing the old view for the POINT test.
> Also the LINESTRING test is a mix of typmod and constraint (I forgot my
> script also creates a constraint linestring column) and alsways has a tymod
> column.
>  
> SOLUTION 2 corrections
> -------------------------------
>  
> -- -- 84,570 - 85,371 ms, 30,000 rows (slow for some reason)
> SELECT * FROM geometry_columns;
>  
> -- 410 - 420 ms - 5,000 rows (constraint columns)
> SELECT * FROM geometry_columns WHERE type = 'POINT';
>  
> -- 1490ms- 1500 ms - 20,000 rows (mix of constraint and columns)
> SELECT * FROM geometry_columns WHERE type = 'LINESTRING';
>  
> -- 220 - 240 ms
> SELECT * from geometry_columns
> WHERE f_table_name = 'constraint_test_linestring_1382';
>  
> -----Original Message-----
> From: Paragon Corporation [mailto:lr at pcorp.us]
> Sent: Saturday, April 25, 2015 12:54 AM
> To: 'PostGIS Development Discussion'
> Subject: geometry_columns improving performance for constraint columns
>  
> The geometry_columns table is very slow for large numbers of geometry
> columns when querying constraint based columns.
> BTW this idea probably applies to raster_columns as well. Though I don't
> think raster_columns view is used quite as much as geometry_columns.
>  
> There are 3 revisions to this view that I have tested that are a significant
> improvement to the existing view.
>  
> Detailed here: http://trac.osgeo.org/postgis/ticket/3092
>  
> To test I ran this script that created 30,000 geometry columns (still need
> to test on 9.3 and 9.2). I was testing on 9.4
>  
> -- do this and then restart service (can't create that many columns or even
> drop that many tables in one transaction without this) ALTER SYSTEM SET
> max_locks_per_transaction = 100000;
>  
>  
> -- build multigeom column tables one with typmod and one with constraint -
> 30,000 columns total
>  
> DO language plpgsql
> $$
> DECLARE var_sql text;
> BEGIN
> SELECT string_agg('CREATE TABLE test.constraint_test_' || lower(f.geom) ||
> '_' || i::text || '(gid serial, geom2 geometry(LINESTRING,2249) );
> SELECT addgeometrycolumn(''test'', '
> || quote_literal('constraint_test_' || lower(f.geom) || '_' || i::text) ||
> ' , ''geom'',
> ' || f.srid::text || ',' || quote_literal(f.geom) || ', 2, false);',
> '') INTO var_sql
> FROM (VALUES ('POINT'::text, 4326), ('MULTIPOLYGON'::text, 4269),
> ('LINESTRING'::text, 4326)) f(geom,srid)
> , generate_series(1,5000) As i;
> EXECUTE var_sql;
> END;
> $$
>  
> To test I generated 15,000 tables each with 2 geometry columns (1 constraint
> and 1 typmod) for a total of 30,000 columns script futher down And then ran
> these tests:
>  
>  
> One Paul has already committed to trunk. The 3 solutions and issues with
> each are as follows:
>  
> SOLUTION 1
> -----------------
> One committed introduces 3 new functions (albeit they are only used by the
> view) so one could argue it could go in a micro release because there is no
> API change.
> That's a borderline argument and I hate having 3 new functions whose only
> purpose is to be used in a view.
>  
> This solution is a mid-range
> -- 2520ms - 2830 ms - 30,000 rows
> SELECT * FROM geometry_columns;
> -- 1260 ms -- 5,000 rows
> SELECT * FROM geometry_columns WHERE type = 'POINT';
>  
> -- 1840 ms - 20,000 rows (this is the typmod row) SELECT * FROM
> geometry_columns WHERE type = 'LINESTRING';
>  
> -- 500 - 510 ms
> SELECT * from geometry_columns
> WHERE f_table_name = 'constraint_test_linestring_1382';
>  
>  
> SOLUTION 2
> ----------------
> This one has no extra functions -- just changes the geometry_columns view,
> so no issue with pushing to a micro (and we could deprecate the 3 functions
> we are currently using). Also doesn't use any constructs we don't have
> available in PostgreSQL 9.0 It's slower than solution 1 for a full column
> list pull or query against constraint based columns, but faster for a
> single column pull or typmod columns.
>  
> -- -- 85,371 ms, 30,000 rows
> SELECT * FROM geometry_columns;
>  
> -- 154,480 ms - 5,000 rows (constraint columns) SELECT * FROM
> geometry_columns WHERE type = 'POINT';
>  
> -- 1500 ms - 20,000 rows (typmod columns) SELECT * FROM geometry_columns
> WHERE type = 'LINESTRING';
>  
> -- 220 ms
> SELECT * from geometry_columns
> WHERE f_table_name = 'constraint_test_linestring_1382';
>  
>  
> SOLUTION 3
> ---------------------
> This one is my favorite solution (wins on all contests) but would require
> putting in some conditional logic since it uses the LATERAL construct only
> available in PostgreSQL 9.3 or above. For lower we'd have to fall back on
> solutions 1 or 2 (I would choose solution 2 even though it's slower for full
> table list because has no extra functions and it is very similar in
> structure so just requires a change to the JOIN with constraint catalog
> table.
>  
> -- -- 2080ms - 2090 ms, 30,000 rows
> SELECT * FROM geometry_columns;
>  
> -- 450 - 460 ms (constraint columns) 5000 rows SELECT * FROM
> geometry_columns WHERE type = 'POINT';
>  
> -- 1400 ms - 20,000 rows (typmod columns) SELECT * FROM geometry_columns
> WHERE type = 'LINESTRING';
>  
> -- 220 ms- 2 records
> SELECT * FROM geometry_columns
> WHERE f_table_name = 'constraint_test_linestring_1382';
>  
>  
>  
> Anyone have thoughts?
>  
> Thanks,
> Regina
> http://www.postgis.us
> http://postgis.net
>  
>  
> -
>  
>  
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
>  




More information about the postgis-devel mailing list