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

[Josh Livni]

Regina,

Thanks a lot for your detailed response (and to Reid too).

First, the 90 was a typo in my message, sorry -- the query was always 
using just 1320 with it's slowness.

Also, I did have both a btree index on fips, and a GiST index on 
centroid(parcels_06.the_geom)

I tried your query, and it too, took a couple minutes.

Moving on to some good news for me (and my not having to use my hack 
workaround of temp tables with pre-calculated buffers):

Turns out, Mark Cave-Ayland was exceedingly helpful on IRC and managed 
to solve this little issue, where it looks like forcing the planner's 
hand was the key.

He narrowed it down, so we noticed that:
SELECT p.*
FROM kc_block_groups b, parcels_06 p    
WHERE buffer(b.the_geom,1320) && centroid(p.the_geom)   
and b.fips = 530330001001;

took > 110,000 ms to run.


whereas:
SELECT *
FROM parcels_06 AS p
WHERE   
     (SELECT buffer(the_geom, 1320)    AS the_geomx   
                   FROM kc_block_groups     WHERE fips = 530330001001)   
     && centroid(p.the_geom)
AND within(centroid(p.the_geom),
    (SELECT buffer(the_geom, 1320)  AS the_geomx
                  FROM kc_block_groups  WHERE fips = 530330001001) );

took ~300ms to run.

Nice!

I put this back in my orig query, and it took about 1500ms:
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
   (SELECT buffer(the_geom, 1320)
   AS the_geomx
   FROM kc_block_groups
    WHERE fips = 530330001001)    && centroid(p.the_geom)
   and within(centroid(p.the_geom), (SELECT buffer(the_geom, 1320)
   AS the_geomx
   FROM kc_block_groups
    WHERE fips = 530330001001) )
   and b.fips = 530330001001
   GROUP BY j."lfc_code" ;

However, this compared to a ~350ms return time for the query on my temp 
table of pre-buffered polygons:
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_bg_qm_buffer b, parcels_06 p inner join lfc_comm j on 
p.pin_key = j.pin_key
   WHERE b.the_geom && centroid(p.the_geom)
   and within(centroid(p.the_geom), b.the_geom)
   and b.fips = 530330001001
   GROUP BY j."lfc_code" ;

As I think Mark noted, since my queries are pretty much spatial, further 
subselects to ensure the spatial stuff is done first might help.  This 
is my next step, but meanwhile here is a really significant improvement 
- I hope the notes above might aid someone in a similar situation.   
Thanks Mark!

Cheers,


  -Josh

Obe, Regina wrote:
> Do you have a gist index on your p.the_geom.
>  
> I think expand is much more efficient than buffer for your && and I 
> think your centroid && instead of just using p.the_geom is totally 
> wasteful.  So I would change to do the falling
>  
> 1) Add a btree index on fips on your block groups  - a attribute index 
> is almost always more selective than a spatial index.
> 2) I think you can drop that buffer index you have going, but if you 
> do need to keep it change it to expand(b.the_geom, 90) - for this 
> particular query, it probably won't be used anyway if you have an 
> index on fips.
> 2) Make sure you have an index on p.the_geom
> 3) Change your query to
>  
>  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 expand(b.the_geom,90) && p.the_geom
>     and within(centroid(p.the_geom), buffer(b.the_geom,1320))
>     and b.fips = 530330001001
>     GROUP BY j."lfc_code"
>
> ------------------------------------------------------------------------
> *From:* postgis-users-bounces at postgis.refractions.net on behalf of 
> Reid Priedhorsky
> *Sent:* Fri 3/9/2007 12:40 PM
> *To:* PostGIS Users Discussion
> *Subject:* [postgis-users] Re: GiST index seems to be ignored?
>
> 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
> _______________________________________________
> 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.
> *
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>