<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7638.1">
<TITLE>Re: [postgis-users] Re: GiST index seems to be ignored?</TITLE>
</HEAD>
<BODY>
<DIV id=idOWAReplyText61959 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>I'm just really baffled
its not using the fips index. I do this thing all the time and it always
uses the attribute index first.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>Although I guess usually when I do this
kind of thing my index is the primary key so I guess that helps a
bit.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>Does just doing a simple</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>SELECT * FROM <FONT
face="Times New Roman">kc_block_groups WHERE fips =
'530330001001'</FONT></FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>use the fips index. If it doesn't I would
be highly suspicious.</FONT></DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net on behalf of Mark
Cave-Ayland<BR><B>Sent:</B> Fri 3/9/2007 2:57 PM<BR><B>To:</B> PostGIS Users
Discussion<BR><B>Subject:</B> Re: [postgis-users] Re: GiST index seems to be
ignored?<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>On Fri, 2007-03-09 at 11:46 -0800, Josh Livni wrote:<BR>>
Regina,<BR>><BR>> Yeah - it seems like it's not wanting to limit the
buffering to the<BR>> single fips result without some serious hand
holding.<BR>><BR>> Regarding your first post:<BR>> We actually tried a
similar query earlier (I love IRC) - but in any<BR>> case, I swapped the AND
in the query you posted in your last email for a<BR>> WHERE, and it took
about 115 seconds.<BR>><BR>> As for your point below: You are right
- my fips field is indeed a<BR>> varchar. I tried the query you suggest
below (quoting the fips) but it<BR>> did not help. In addition, I made
a fips_int column and added an index<BR>> - same thing (each of these took
about 115s).<BR>><BR>> Also, just want to reiterate thx again for
everyone's input from this -<BR>> I've learned quite a bit about query
optimizing today (still quite a<BR>> ways to go,
however).<BR>><BR>> -Josh<BR><BR>Yeah, as I understand it, the
planner is pulling up the buffer() into<BR>the WHERE clause rather than
materializing the table, maybe because it<BR>realises only a single result is
being returned :(<BR><BR>Thinking about this, I wonder if it is possible to use
an ORDER BY to<BR>force the planner to materialize the dataset - something like
this?<BR><BR>SELECT p.* FROM (SELECT buffer(kc_block_groups.the_geom, 1320)
as<BR>thebuffer FROM kc_block_groups AND kc_block_groups.fips =
530330001001<BR>ORDER BY kc_block_groups.fips) b INNER JOIN
parcels_06 p ON<BR>b.thebuffer &&
centroid(p.the_geom) WHERE
within(centroid(p.the_geom),<BR>b.thebuffer)<BR><BR>Josh, does an EXPLAIN
ANALYZE on this query show a Materialize<BR>somewhere, and does this keep the
300ms execution time?<BR><BR><BR>Kind
regards,<BR><BR>Mark.<BR><BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR>postgis-users@postgis.refractions.net<BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></DIV>
</BODY>
</HTML>
<HTML><BODY><P><hr size=1></P><br>
<P><STRONG><br>
The substance of this message, including any attachments, may be<br>
confidential, legally privileged and/or exempt from disclosure<br>
pursuant to Massachusetts law. It is intended solely for the<br>
addressee. If you received this in error, please contact the sender<br>
and delete the material from any computer.<br>
</STRONG></P></BODY></HTML>