[postgis-users] postgis slow performance

[Ben Madin]

I probably can't help, but am interested in the optimisation of my own  
tables.

However, without knowing a bit more about your setup I doubt anyone  
else can either.

The output of

	select postgis_full_version();

is a good starting point.

Other details like EXPLAIN output, how big is the table, what other  
columns are there, is it in a separate tablespace, etc. are often  
useful.

Maybe submit that now so when other's look at it they have a bit more  
to work on.

cheers

Ben


On 28/08/2009, at 1:35 AM, Narayanan, Divya wrote:

> We were testing some queries to improve performance and found that  
> the queries using PostGIS were slower than those that did not.
>
> I’ve included one of the queries tested
>
> Query with PostGIS
>
> SELECT ntb.id
> FROM navteq as ntb
> WHERE ( ntb.positioned_tsv @@ '(hotel|hodels|hoteles)'::tsquery)
> AND (latitude>34.00846 AND latitude<34.09855 AND  
> longitude>-118.29926 AND longitude<-118.1907)
> LIMIT 250
>
> Query time: 1 second
>
> Query using postgis:
>
> SELECT ntb.id
> FROM navteq_xtd_noothers as ntb
> WHERE ( ntb.positioned_tsv @@ '(hotel|hodels|hoteles)'::tsquery)
> AND geom && ST_SetSRID(ST_MakeBox2D(ST_Point(-118.29926,  
> 34.00846),ST_Point(-118.1907, 34.09855)),4269)
> LIMIT 250
>
> Query time: 4 seconds
>
>
> The latitude and longitude columns are indexed. The geom column uses  
> a GIST index. We’re using Postgres v 8.1. Is there something we’re  
> doing wrong? Please let me know if you need more information.
>
>
> Thanks,
> Divya

-- 

Ben Madin
REMOTE INFORMATION

t : +61 8 9192 5455
f : +61 8 9192 5535
m : 0448 887 220
Broome   WA   6725

ben at remoteinformation.com.au



							Out here, it pays to know...


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090828/710504a1/attachment.html>