[postgis-users] Re: GiST index seems to be ignored?

[Obe, Regina]

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.
 
Although I guess usually when I do this kind of thing my index is the primary key so I guess that helps a bit.
 
Does just doing a simple
SELECT * FROM kc_block_groups  WHERE fips = '530330001001'
 
use the fips index.   If it doesn't I would be highly suspicious.

________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of Mark Cave-Ayland
Sent: Fri 3/9/2007 2:57 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Re: GiST index seems to be ignored?



On Fri, 2007-03-09 at 11:46 -0800, Josh Livni wrote:
> Regina,
>
> Yeah - it seems like it's not wanting to limit the buffering to the
> single fips result without some serious hand holding.
>
> Regarding your first post:
> We actually tried a similar query earlier (I love IRC) - but in any
> case, I swapped the AND in the query you posted in your last email for a
> WHERE, and it took about 115 seconds.
>
> As for your point below:  You are right - my fips field is indeed a
> varchar.  I tried the query you suggest below (quoting the fips) but it
> did not help.  In addition, I made a fips_int column and added an index
> - same thing (each of these took about 115s).
>
> Also, just want to reiterate thx again for everyone's input from this -
> I've learned quite a bit about query optimizing today (still quite a
> ways to go, however).
>
>   -Josh

Yeah, as I understand it, the planner is pulling up the buffer() into
the WHERE clause rather than materializing the table, maybe because it
realises only a single result is being returned :(

Thinking about this, I wonder if it is possible to use an ORDER BY to
force the planner to materialize the dataset - something like this?

SELECT p.* FROM (SELECT buffer(kc_block_groups.the_geom, 1320) as
thebuffer FROM kc_block_groups AND kc_block_groups.fips = 530330001001
ORDER BY kc_block_groups.fips)  b INNER JOIN parcels_06  p   ON
b.thebuffer && centroid(p.the_geom)  WHERE within(centroid(p.the_geom),
b.thebuffer)

Josh, does an EXPLAIN ANALYZE on this query show a Materialize
somewhere, and does this keep the 300ms execution time?


Kind regards,

Mark.


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users





-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070309/11c2e20c/attachment.html>