[postgis-devel] geometry_columns improving performance for constraint columns

[Paul Ramsey]

I’m pretty sure everyone was suffering, on behalf of the constraint users :)
P 


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


On April 27, 2015 at 6:08:11 AM, Paragon Corporation (lr at pcorp.us) wrote:
> 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
> >
>  
>  
>  
>