[postgis-users] slow sql queries in postgis
Greg Williamson
gwilliamson39 at yahoo.com
Thu Jan 12 14:34:58 PST 2012
Try running the query with EXPLAIN ANALYZE ... seeing sequential scans is often a tip-off to indexes being needed, although there are times when the planner decides a sequential scan is faster than doing index-driven reads since those involve more I/O. Post the results here (and if no response is forthcoming you might post to the postgres Performance list).
Postgres 8.4 is rather old, and if you are not running the latest and greatest version of postgres 8.4 (8.4.10) should be what you are running -- those point releases are critical for getting bug fixes! If you are on 8.4 the upgrade to 8.4.10 should be fairly easy -- they never introduce changes that prevent such an upgrade in place AFAIK. It is possible you are running into issues caused by an out-dated version of postgres itself.
You might also post any postgresql.conf changes that you have made since they can effect the planner (work_mem for instance has an effect on sorts).
Greg W.
----- Original Message -----
> From: Smith Roman <autisi at yahoo.com>
> To: postgis-users at postgis.refractions.net
> Cc:
> Sent: Thursday, January 12, 2012 2:21 PM
> Subject: Re: [postgis-users] slow sql queries in postgis
>
>
> 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