<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7638.1">
<TITLE>Re: [postgis-users] Re: GiST index seems to be ignored?</TITLE>
</HEAD>
<BODY>
<DIV id=idOWAReplyText11921 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Interesting. So was it
not using the fips index at all? Just curious can you try this
query</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>SELECT p.*<BR>FROM (SELECT
buffer(kc_block_groups.the_geom, 1320) as thebuffer FROM kc_block_groups AND
kc_block_groups.fips = 530330001001) b INNER JOIN parcels_06
p ON b.thebuffer &&
centroid(p.the_geom) </FONT></DIV>
<DIV dir=ltr><FONT size=2>WHERE within(centroid(p.the_geom),
b.thebuffer)<BR></DIV></FONT></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net on behalf of Josh
Livni<BR><B>Sent:</B> Fri 3/9/2007 1:29 PM<BR><B>To:</B> PostGIS Users
Discussion<BR><B>Subject:</B> Re: [postgis-users] Re: GiST index seems to be
ignored?<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>Regina,<BR><BR>Thanks a lot for your detailed response (and to
Reid too).<BR><BR>First, the 90 was a typo in my message, sorry -- the query was
always<BR>using just 1320 with it's slowness.<BR><BR>Also, I did have both a
btree index on fips, and a GiST index
on<BR>centroid(parcels_06.the_geom)<BR><BR>I tried your query, and it too, took
a couple minutes.<BR><BR>Moving on to some good news for me (and my not having
to use my hack<BR>workaround of temp tables with pre-calculated
buffers):<BR><BR>Turns out, Mark Cave-Ayland was exceedingly helpful on IRC and
managed<BR>to solve this little issue, where it looks like forcing the
planner's<BR>hand was the key.<BR><BR>He narrowed it down, so we noticed
that:<BR>SELECT p.*<BR>FROM kc_block_groups b, parcels_06
p <BR>WHERE buffer(b.the_geom,1320) &&
centroid(p.the_geom) <BR>and b.fips = 530330001001;<BR><BR>took >
110,000 ms to run.<BR><BR><BR>whereas:<BR>SELECT *<BR>FROM parcels_06 AS
p<BR>WHERE <BR> (SELECT buffer(the_geom,
1320) AS
the_geomx <BR>
FROM kc_block_groups WHERE fips =
530330001001) <BR> &&
centroid(p.the_geom)<BR>AND within(centroid(p.the_geom),<BR>
(SELECT buffer(the_geom, 1320) AS
the_geomx<BR>
FROM kc_block_groups WHERE fips = 530330001001) );<BR><BR>took ~300ms to
run.<BR><BR>Nice!<BR><BR>I put this back in my orig query, and it took about
1500ms:<BR>SELECT<BR> count(j."lfc_code") as count,<BR>
sum(J."gross_sqft") as Sum_Gross_Sqft,<BR> sum(j."units") as
Sum_Units,<BR> j."lfc_code"<BR> FROM kc_block_groups b,
parcels_06 p inner join lfc_comm j on<BR>p.pin_key = j.pin_key<BR>
WHERE<BR> (SELECT buffer(the_geom, 1320)<BR> AS
the_geomx<BR> FROM kc_block_groups<BR> WHERE fips
= 530330001001) &&
centroid(p.the_geom)<BR> and within(centroid(p.the_geom), (SELECT
buffer(the_geom, 1320)<BR> AS the_geomx<BR> FROM
kc_block_groups<BR> WHERE fips = 530330001001)
)<BR> and b.fips = 530330001001<BR> GROUP BY
j."lfc_code" ;<BR><BR>However, this compared to a ~350ms return time for the
query on my temp<BR>table of pre-buffered polygons:<BR>SELECT<BR>
count(j."lfc_code") as count,<BR> sum(J."gross_sqft") as
Sum_Gross_Sqft,<BR> sum(j."units") as Sum_Units,<BR>
j."lfc_code"<BR> FROM kc_bg_qm_buffer b, parcels_06 p inner join
lfc_comm j on<BR>p.pin_key = j.pin_key<BR> WHERE b.the_geom
&& centroid(p.the_geom)<BR> and within(centroid(p.the_geom),
b.the_geom)<BR> and b.fips = 530330001001<BR> GROUP BY
j."lfc_code" ;<BR><BR>As I think Mark noted, since my queries are pretty much
spatial, further<BR>subselects to ensure the spatial stuff is done first might
help. This<BR>is my next step, but meanwhile here is a really significant
improvement<BR>- I hope the notes above might aid someone in a similar
situation. <BR>Thanks Mark!<BR><BR>Cheers,<BR><BR><BR>
-Josh<BR><BR>Obe, Regina wrote:<BR>> Do you have a gist index on your
p.the_geom.<BR>> <BR>> I think expand is much more efficient than
buffer for your && and I<BR>> think your centroid && instead
of just using p.the_geom is totally<BR>> wasteful. So I would change to
do the falling<BR>> <BR>> 1) Add a btree index on fips on your block
groups - a attribute index<BR>> is almost always more selective than a
spatial index.<BR>> 2) I think you can drop that buffer index you have going,
but if you<BR>> do need to keep it change it to expand(b.the_geom, 90) - for
this<BR>> particular query, it probably won't be used anyway if you have
an<BR>> index on fips.<BR>> 2) Make sure you have an index on
p.the_geom<BR>> 3) Change your query to<BR>> <BR>>
SELECT<BR>> count(j."lfc_code") as
count,<BR>> sum(J."gross_sqft") as
Sum_Gross_Sqft,<BR>> sum(j."units") as
Sum_Units,<BR>>
j."lfc_code"<BR>> FROM kc_block_groups b, parcels_06 p
inner join lfc_comm j on<BR>> p.pin_key =
j.pin_key<BR>> WHERE expand(b.the_geom,90) &&
p.the_geom<BR>> and within(centroid(p.the_geom),
buffer(b.the_geom,1320))<BR>> and b.fips =
530330001001<BR>> GROUP BY
j."lfc_code"<BR>><BR>>
------------------------------------------------------------------------<BR>>
*From:* postgis-users-bounces@postgis.refractions.net on behalf of<BR>> Reid
Priedhorsky<BR>> *Sent:* Fri 3/9/2007 12:40 PM<BR>> *To:* PostGIS Users
Discussion<BR>> *Subject:* [postgis-users] Re: GiST index seems to be
ignored?<BR>><BR>> Josh Livni wrote:<BR>><BR>> > I am basically
trying to find points within a buffered polygon. I've<BR>> > created
a GiST index on my polygons:<BR>> > CREATE INDEX
kc_block_groups_qm_buffer_the_geom_gist<BR>> > ON
kc_block_groups<BR>> > USING gist<BR>> >
(buffer(the_geom, 1320::double precision));<BR>><BR>> > SELECT<BR>>
> count(j."lfc_code") as count,<BR>>
> sum(J."gross_sqft") as Sum_Gross_Sqft,<BR>>
> sum(j."units") as Sum_Units,<BR>>
> j."lfc_code"<BR>> > FROM
kc_block_groups b, parcels_06 p inner join lfc_comm j on<BR>> > p.pin_key
= j.pin_key<BR>> > WHERE buffer(b.the_geom,90)
&& centroid(p.the_geom)<BR>> > and
within(centroid(p.the_geom), buffer(b.the_geom,1320))<BR>>
> and b.fips = 530330001001<BR>> >
GROUP BY j."lfc_code"<BR>><BR>> within() won't take advantage of the
index, and with the operator that<BR>> does (&&), your buffer() call
uses a different buffer distance. What<BR>> happens if you create the index
on buffer(the_geom, 90)?<BR>><BR>> My other guess is that you use a
typecast in the index creation but not<BR>> the query.<BR>><BR>> Good
luck,<BR>><BR>> Reid<BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> postgis-users@postgis.refractions.net<BR>> <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>>
------------------------------------------------------------------------<BR>><BR>>
*<BR>> The substance of this message, including any attachments, may
be<BR>> confidential, legally privileged and/or exempt from
disclosure<BR>> pursuant to Massachusetts law. It is intended solely for
the<BR>> addressee. If you received this in error, please contact the
sender<BR>> and delete the material from any computer.<BR>>
*<BR>><BR>>
------------------------------------------------------------------------<BR>><BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> postgis-users@postgis.refractions.net<BR>> <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>> <BR><BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR>postgis-users@postgis.refractions.net<BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></DIV>
</BODY>
</HTML>