[postgis-users] slow sql queries in postgis
Smith Roman
autisi at yahoo.com
Thu Jan 12 14:21:50 PST 2012
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
> **********************************************
>
More information about the postgis-users
mailing list