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

[Josh Livni]

Hi all,

I've successfully done similar things in the past, but for some reason I 
1) can't find my old code, and 
2) have no idea why this isn't working...

I am basically trying to find points within a buffered polygon.  I've created a GiST index on my polygons:
 CREATE INDEX kc_block_groups_qm_buffer_the_geom_gist
  ON kc_block_groups
  USING gist
  (buffer(the_geom, 1320::double precision));

But, it does not seem to be taken into account with this query:


SELECT
    count(j."lfc_code") as count,
    sum(J."gross_sqft") as Sum_Gross_Sqft,
    sum(j."units") as Sum_Units,
    j."lfc_code"
    FROM kc_block_groups b, parcels_06 p inner join lfc_comm j on p.pin_key = j.pin_key
    WHERE buffer(b.the_geom,90) && centroid(p.the_geom)
    and within(centroid(p.the_geom), buffer(b.the_geom,1320))
    and b.fips = 530330001001
    GROUP BY j."lfc_code" 


----
I get the ol 'NOTICE:  LWGEOM_gist_joinsel called with arguments that are not column references' warning when I try run it...

If I remove the buffer part of the query, or make the buffer 0 (or even a very small #), it runs really fast.

And, if I create a new table:
 Create table kcbg_buffer as (select fips, buffer(the_geom,1320) from kc_block_groups);
then the same query runs on this table are very quick....

Here is the explain from my slow query:
HashAggregate  (cost=237.07..237.09 rows=1 width=24)
  ->  Nested Loop  (cost=5.03..237.06 rows=1 width=24)
        ->  Nested Loop  (cost=5.03..225.86 rows=1 width=8)
              Join Filter: within(centroid(p.the_geom), buffer(b.the_geom, 1320::double precision))
              ->  Seq Scan on kc_block_groups b  (cost=0.00..208.75 rows=1 width=1895)
                    Filter: ((fips)::text = '530330001001'::text)
              ->  Bitmap Heap Scan on parcels_06 p  (cost=5.03..17.04 rows=3 width=469)
                    Filter: (buffer(b.the_geom, 1320::double precision) && centroid(p.the_geom))
                    ->  Bitmap Index Scan on parcels_06_centroid_geom_gist  (cost=0.00..5.03 rows=3 width=0)
                          Index Cond: (buffer(b.the_geom, 1320::double precision) && centroid(p.the_geom))
        ->  Index Scan using lfc_comm_pin_key_idx on lfc_comm j  (cost=0.00..11.17 rows=2 width=32)
              Index Cond: (p.pin_key = j.pin_key)

If you have any ideas on the probably very obvious thing I am missing, I would love to hear them.

Thx,

  -Josh