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

[Reid Priedhorsky]

Josh Livni wrote:

> 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));

> 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"

within() won't take advantage of the index, and with the operator that 
does (&&), your buffer() call uses a different buffer distance. What 
happens if you create the index on buffer(the_geom, 90)?

My other guess is that you use a typecast in the index creation but not 
the query.

Good luck,

Reid