<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6001.18294" name=GENERATOR></HEAD>
<BODY
style="WORD-WRAP: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space">
<DIV dir=ltr align=left><SPAN class=992042604-28082009><FONT face=Arial
color=#0000ff size=2>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?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=992042604-28082009><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=992042604-28082009><FONT face=Arial
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=992042604-28082009><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=992042604-28082009><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=992042604-28082009><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=992042604-28082009><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=992042604-28082009><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=992042604-28082009><FONT face=Arial color=#0000ff size=2>Hope
that helps,</FONT></SPAN></DIV>
<DIV><SPAN class=992042604-28082009><FONT face=Arial color=#0000ff
size=2>Regina</FONT></SPAN></DIV>
<DIV><BR></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Ben
Madin<BR><B>Sent:</B> Thursday, August 27, 2009 9:49 PM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> Re: [postgis-users] postgis slow
performance<BR></FONT><BR></DIV>
<DIV></DIV>I probably can't help, but am interested in the optimisation of my
own tables.
<DIV><BR></DIV>
<DIV>However, without knowing a bit more about your setup I doubt anyone else
can either.
<DIV><BR></DIV>
<DIV>The output of </DIV>
<DIV><BR></DIV>
<DIV><SPAN class=Apple-tab-span style="WHITE-SPACE: pre"></SPAN>select
postgis_full_version();</DIV>
<DIV><BR></DIV>
<DIV>is a good starting point.</DIV>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>Maybe submit that now so when other's look at it they have a bit more to
work on.</DIV>
<DIV><BR></DIV>
<DIV>cheers</DIV>
<DIV><BR></DIV>
<DIV>Ben</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>
<DIV>
<DIV>On 28/08/2009, at 1:35 AM, Narayanan, Divya wrote:</DIV><BR
class=Apple-interchange-newline>
<BLOCKQUOTE type="cite"><SPAN class=Apple-style-span
style="WORD-SPACING: 0px; FONT: medium Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px">
<DIV lang=EN-US vlink="purple" link="blue">
<DIV>
<DIV class=Section1>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><SPAN
class=Apple-style-span style="FONT-SIZE: 13px; FONT-FAMILY: Arial">We were
testing some queries to improve performance and found that the queries using
PostGIS were slower than those that did not.</SPAN></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">I’ve
included one of the queries tested<O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><B><FONT
face=Arial size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial">Query with
PostGIS<O:P></O:P></SPAN></FONT></B></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><B><FONT
face=Arial size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></B></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SELECT
ntb.id<O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">FROM
navteq as ntb<O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">WHERE (
ntb.positioned_tsv @@
'(hotel|hodels|hoteles)'::tsquery)<O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">AND
(latitude>34.00846 AND latitude<34.09855 AND longitude>-118.29926 AND
longitude<-118.1907)<O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">LIMIT
250<O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><B><FONT
face=Arial size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial">Query time: 1
second<O:P></O:P></SPAN></FONT></B></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><B><FONT
face=Arial size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></B></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><B><FONT
face=Arial size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial">Query using
postgis:<O:P></O:P></SPAN></FONT></B></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">SELECT
ntb.id<O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">FROM
navteq_xtd_noothers as ntb<O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">WHERE (
ntb.positioned_tsv @@
'(hotel|hodels|hoteles)'::tsquery)<O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">AND geom
&& ST_SetSRID(ST_MakeBox2D(ST_Point(-118.29926,
34.00846),ST_Point(-118.1907, 34.09855)),4269)<O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">LIMIT
250<O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><B><FONT
face=Arial size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial">Query time: 4
seconds<O:P></O:P></SPAN></FONT></B></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">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.<O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Thanks,<O:P></O:P></SPAN></FONT></DIV>
<DIV
style="FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: 'Times New Roman'"><FONT
face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Divya<O:P></O:P></SPAN></FONT></DIV></DIV></DIV></DIV></SPAN></BLOCKQUOTE></DIV><BR>
<DIV apple-content-edited="true"><SPAN class=Apple-style-span
style="WORD-SPACING: 0px; FONT: 12px Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2; border-spacing: 0px 0px; -khtml-text-decorations-in-effect: none; -apple-text-size-adjust: auto">
<DIV
style="WORD-WRAP: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space">
<DIV style="MARGIN: 0px">-- </DIV>
<DIV style="MIN-HEIGHT: 14px; MARGIN: 0px; FONT: 12px Helvetica"><BR></DIV>
<DIV style="MARGIN: 0px">Ben Madin</DIV>
<DIV style="MARGIN: 0px">REMOTE INFORMATION</DIV>
<DIV style="MIN-HEIGHT: 14px; MARGIN: 0px; FONT: 12px Helvetica"><BR></DIV>
<DIV style="MARGIN: 0px">t : +61 8 9192 5455</DIV>
<DIV style="MARGIN: 0px">f : +61 8 9192 5535</DIV>
<DIV style="MARGIN: 0px">m : 0448 887 220</DIV>
<DIV style="MARGIN: 0px">Broome WA 6725</DIV>
<DIV style="MIN-HEIGHT: 14px; MARGIN: 0px; FONT: 12px Helvetica"><BR></DIV>
<DIV style="MIN-HEIGHT: 14px; MARGIN: 0px; FONT: 12px Helvetica"><A
href="mailto:ben@remoteinformation.com.au">ben@remoteinformation.com.au</A></DIV>
<DIV style="MIN-HEIGHT: 14px; MARGIN: 0px; FONT: 12px Helvetica"><BR></DIV>
<DIV style="MIN-HEIGHT: 14px; MARGIN: 0px; FONT: 12px Helvetica"><BR></DIV>
<DIV style="MIN-HEIGHT: 14px; MARGIN: 0px; FONT: 12px Helvetica"><BR></DIV>
<DIV style="MARGIN: 0px"><SPAN class=Apple-tab-span
style="WHITE-SPACE: pre"><SPAN class=Apple-style-span
style="WHITE-SPACE: pre"></SPAN></SPAN>Out here, it pays to
know...</DIV></DIV><BR
class=Apple-interchange-newline></SPAN></DIV><BR></DIV></DIV></BODY></HTML>