[postgis-users] slow sql queries in postgis
Nicklas Avén
nicklas.aven at jordogskog.no
Thu Jan 12 14:50:57 PST 2012
Hallo
1) what do you mean by slow? How many milliseconds or seconds are we
talking about?
2) What do you get from :
SELECT sum(npoints(the_geom)), avg(npoints(the_geom)) from mangrove
That will tell us about how much materia the function have to deal with
3) What do you get from :
SELECT sum(npoints(the_geom)), count(*) from mangrove this_ where
ST_Intersects(this_.the_geom, ?);
(the && part is included in ST_Intersects)
this will give a hint if it is a lot of data that is returned. A commen
performance problem is the work to send the result back to the client.
4) What do you get from
SELECT count(*) from mangrove where this_.the_geom && ?
If count(*) here gives a lot of more geometries here than when running
with ST_Intersects it indicates that the index cannot do a very big part
of the job so the harder work on the real geometries rather than the
bboxes will be timeconsuming.
And of course as mentioned before, check with explain analyze thaat the
index is really used.
HTH
Nicklas
On Thu, 2012-01-12 at 14:21 -0800, Smith Roman wrote:
> Thanks for your responses
>
> the complete specs are as follows
>
> The OS version is windows server 2008 R2
> Postgres version= 8.4
> Postgis version = 1.5
> Processor speed: 1.87ghz.
> Processor type: Intel Xenon.
> RAM: 2GB.
>
>
> My sql query
>
> Hibernate: select this_.gid as gid0_0_, this_.area as area0_0_, this_.class_id as class3_0_0_, this_.class_name as class4_0_0_, this_.the_geom as the5_0_0_, this_.mangrove_length as mangrove6_0_0_, this_.parts as parts0_0_ from mangrove this_ where ((this_.the_geom && ? ) and (this_.the_geom && ? AND intersects(this_.the_geom, ?)))
>
>
> How do I can i ensure that my spatial indexes are proper ?
>
> I have just upgraded my ram to 3gb and still can't really notice a significant performance improvement. I have even increased shared buffer to 512mb.
>
> cheers,
>
>
>
> --- On Thu, 1/12/12, postgis-users-request at postgis.refractions.net <postgis-users-request at postgis.refractions.net> wrote:
>
> > From: postgis-users-request at postgis.refractions.net <postgis-users-request at postgis.refractions.net>
> > Subject: postgis-users Digest, Vol 119, Issue 11
> > To: postgis-users at postgis.refractions.net
> > Date: Thursday, January 12, 2012, 8:00 PM
> > Send postgis-users mailing list
> > submissions to
> > postgis-users at postgis.refractions.net
> >
> > To subscribe or unsubscribe via the World Wide Web, visit
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > or, via email, send a message with subject or body 'help'
> > to
> > postgis-users-request at postgis.refractions.net
> >
> > You can reach the person managing the list at
> > postgis-users-owner at postgis.refractions.net
> >
> > When replying, please edit your Subject line so it is more
> > specific
> > than "Re: Contents of postgis-users digest..."
> >
> >
> > Today's Topics:
> >
> > 1. slow sql queries in postgis (Smith
> > Roman)
> > 2. Re: slow sql queries in postgis (Mr.
> > Puneet Kishor)
> > 3. IFC2PG 3D : Industry Foundation
> > Classes to PostGIS 3rd Part
> > (j.rolland)
> > 4. Re: slow sql queries in postgis
> > (Nicolas Ribot)
> >
> >
> > ----------------------------------------------------------------------
> >
> > Message: 1
> > Date: Thu, 12 Jan 2012 06:21:51 -0800 (PST)
> > From: Smith Roman <autisi at yahoo.com>
> > Subject: [postgis-users] slow sql queries in postgis
> > To: Users Postgis <postgis-users at postgis.refractions.net>
> > Message-ID:
> > <1326378111.12562.YahooMailClassic at web162302.mail.bf1.yahoo.com>
> > Content-Type: text/plain; charset=us-ascii
> >
> >
> > Hello Everyone !
> >
> > I have a postgis table containing mangrove data with 5
> > fields and 950,000 records. I am accessing the table using
> > hibernate spatial in a web gis application. I have
> > done the following optimizations:
> >
> > - Ran vacuum analyze.
> >
> > - Clustering.
> >
> > - Increased shared buffer from 32mb to 128mb.
> >
> >
> > Inspite of this optimizations, the sql queries accessing
> > the mangrove data are very slow.
> >
> > I will like to ask what could be the performance problem ?
> >
> > My server specs are as follows:
> >
> > Processor speed: 1.87ghz.
> > Processor type: Intel Xenon.
> > RAM: 2GB.
> >
> >
> > Are this specs good enough ?
> >
> > Cheers,
> >
> >
> >
> >
> >
> >
> >
> >
> > ------------------------------
> >
> > Message: 2
> > Date: Thu, 12 Jan 2012 08:40:15 -0600
> > From: "Mr. Puneet Kishor" <punk.kish at gmail.com>
> > Subject: Re: [postgis-users] slow sql queries in postgis
> > To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> > Message-ID: <13741AE4-7D5A-4EC1-A1E9-035B634F9003 at gmail.com>
> > Content-Type: text/plain; charset=us-ascii
> >
> >
> > On Jan 12, 2012, at 8:21 AM, Smith Roman wrote:
> >
> > >
> > > Hello Everyone !
> > >
> > > I have a postgis table containing mangrove data with 5
> > fields and 950,000 records. I am accessing the table using
> > hibernate spatial in a web gis application. I have
> > done the following optimizations:
> > >
> > > - Ran vacuum analyze.
> > >
> > > - Clustering.
> > >
> > > - Increased shared buffer from 32mb to 128mb.
> > >
> > >
> > > Inspite of this optimizations, the sql queries
> > accessing the mangrove data are very slow.
> >
> >
> > You would have to give more details on the query... certain
> > queries may be slow no matter what you do, while others
> > could be sped up.
> >
> > Do you have proper indexes?
> >
> > >
> > > I will like to ask what could be the performance
> > problem ?
> > >
> > > My server specs are as follows:
> > >
> > > Processor speed: 1.87ghz.
> > > Processor type: Intel Xenon.
> > > RAM: 2GB.
> > >
> >
> >
> > RAM is way too low. Heck, even a modern word-processing
> > program (whereby "modern" I mean feature-bloated) will run
> > sluggish in 2 GB RAM. What is the operating system on your
> > machine?
> >
> >
> >
> > >
> > > Are this specs good enough ?
> > >
> > > Cheers,
> > >
> > >
> > >
> > >
> > >
> > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> >
> > ------------------------------
> >
> > Message: 3
> > Date: Thu, 12 Jan 2012 07:27:54 -0800 (PST)
> > From: "j.rolland" <jrmrolland at aol.com>
> > Subject: [postgis-users] IFC2PG 3D : Industry Foundation
> > Classes to
> > PostGIS 3rd Part
> > To: postgis-users at postgis.refractions.net
> > Message-ID: <1326382074993-3665545.post at n6.nabble.com>
> > Content-Type: text/plain; charset=UTF-8
> >
> > Hello,
> > I have just set up on my blog at the following
> > address
> > http://ageoguy.blogspot.com/2012/01/ifc2pg-3d-industry-foundation-classes.html
> > A GeoSpatial World
> >
> > the third part of a tutorial which presents IFC2PG a tool
> > under development
> > which makes it possible to export the objects of a file IFC
> > towards PostGIS.
> > http://postgis.17.n6.nabble.com/file/n3665545/ifc2pg_splashscreen3.gif
> >
> >
> > This 3rd part inserts to us in the world of the third
> > dimension under
> > PostGIS 2.0,
> > the objects imported in the database will be it in 3D.
> >
> >
> > Regards,
> >
> > J?r?me
> >
> > --
> > View this message in context: http://postgis.17.n6.nabble.com/IFC2PG-3D-Industry-Foundation-Classes-to-PostGIS-3rd-Part-tp3665545p3665545.html
> > Sent from the PostGIS - User mailing list archive at
> > Nabble.com.
> >
> >
> > ------------------------------
> >
> > Message: 4
> > Date: Thu, 12 Jan 2012 17:11:37 +0100
> > From: Nicolas Ribot <nicolas.ribot at gmail.com>
> > Subject: Re: [postgis-users] slow sql queries in postgis
> > To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> > Message-ID:
> > <CAGAwT=3Ldb=cYMaFv8AuvNBiczAsqsn4YKw23gvrUSjRbYda9g at mail.gmail.com>
> > Content-Type: text/plain; charset=ISO-8859-1
> >
> > Hi,
> >
> > What are the PostgreSQL/Postgis versions ?
> >
> > Nicolas
> >
> > On 12 January 2012 15:40, Mr. Puneet Kishor <punk.kish at gmail.com>
> > wrote:
> > >
> > > On Jan 12, 2012, at 8:21 AM, Smith Roman wrote:
> > >
> > >>
> > >> Hello Everyone !
> > >>
> > >> I have a postgis table containing mangrove data
> > with 5 fields and 950,000 records. I am accessing the table
> > using hibernate spatial in a web gis application. ?I have
> > done the following optimizations:
> > >>
> > >> - Ran vacuum analyze.
> > >>
> > >> - Clustering.
> > >>
> > >> - Increased shared buffer from 32mb to 128mb.
> > >>
> > >>
> > >> Inspite of this optimizations, the sql queries
> > accessing the mangrove data are very slow.
> > >
> > >
> > > You would have to give more details on the query...
> > certain queries may be slow no matter what you do, while
> > others could be sped up.
> > >
> > > Do you have proper indexes?
> > >
> > >>
> > >> I will like to ask what could be the performance
> > problem ?
> > >>
> > >> My server specs are as follows:
> > >>
> > >> Processor speed: 1.87ghz.
> > >> Processor type: Intel Xenon.
> > >> RAM: 2GB.
> > >>
> > >
> > >
> > > RAM is way too low. Heck, even a modern
> > word-processing program (whereby "modern" I mean
> > feature-bloated) will run sluggish in 2 GB RAM. What is the
> > operating system on your machine?
> > >
> > >
> > >
> > >>
> > >> Are this specs good enough ?
> > >>
> > >> Cheers,
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> _______________________________________________
> > >> postgis-users mailing list
> > >> postgis-users at postgis.refractions.net
> > >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> > ------------------------------
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> > End of postgis-users Digest, Vol 119, Issue 11
> > **********************************************
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list