[postgis-users] postgis slow performance

[Paragon Corporation]

Yes an explain would help.  My guess is that since both PostGIS and TSearch
use GIST.  I assume there is a GIST index on your tsearch vectory query?
 
One will be selected first over the other and perhaps that order is wrong.
I'm not sure what the difference in speeds are between the 2 if any and
probably depends on your dataset as well.
 
My guess is though that the btree index will most always win out first so in
the first case -- teh order of application of filters is more predicatable
and probably more efficient.
 
8.1 is also kind of old and there have been a lot of planner improvements
made since then and improvements in GIST as well as I recall.
 
Hope that helps,
Regina

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Ben
Madin
Sent: Thursday, August 27, 2009 9:49 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] postgis slow performance


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/ba8ad091/attachment.html>