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

[Mark Cave-Ayland]

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.