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

[Josh Livni]

Mark,

Nice followup:

"Nested Loop  (cost=5.03..25.13 rows=1 width=586) (actual 
time=56.236..183.194 rows=1734 loops=1)"
"  Join Filter: within(centroid(p.the_geom), b.thebuffer)"
"  ->  Index Scan using bg_fips on kc_block_groups  (cost=0.00..8.02 
rows=1 width=1911) (actual time=53.812..53.818 rows=1 loops=1)"
"        Index Cond: ((fips)::text = '530330001001'::text)"
"  ->  Bitmap Heap Scan on parcels_06 p  (cost=5.03..17.04 rows=3 
width=586) (actual time=2.356..56.674 rows=2038 loops=1)"
"        Filter: (b.thebuffer && centroid(p.the_geom))"
"        ->  Bitmap Index Scan on parcels_06_centroid_geom_gist  
(cost=0.00..5.03 rows=3 width=0) (actual time=1.903..1.903 rows=2038 
loops=1)"
"              Index Cond: (b.thebuffer && centroid(p.the_geom))"
"Total runtime: 186.480 ms"


Sweet.

  -Josh

Mark Cave-Ayland wrote:
> 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
>