Robert,<br><br>I have been haunted by similar issues but I haven't had time to figure out some explanation I really believe in.<br><br>Does this post with a reproducible case seem similar? It seems like it to me.<br><br>
<a href="http://postgis.refractions.net/pipermail/postgis-devel/2008-September/003547.html">http://postgis.refractions.net/pipermail/postgis-devel/2008-September/003547.html</a><br><br>For now when these situations aren't working (the planner favors the spatial index due to a weird estimate when it is really going to be lots) I am using a constant geometry [e.g. geometryfromwkt(wkt geometry string)] for the join condition. I realize the feasibility of this depends on your application, or your desire to write a user-defined function.<br>
<br>Why does the planner get different estimates for the same geometry being used in a constraint based on it coming from a table, or from a string in the query? I think the reason this is happening might be apparent in lwgeom_estimate.c, but I'm open to being totally wrong :).<br>
<br>-Mose<br><br><br><br><br><br><div class="gmail_quote">On Wed, Mar 18, 2009 at 11:32 AM, Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Robert,<br>
<br>
Which version of PostgreSQL are you running? I wander if you are being<br>
bitten by one of these bugs<br>
<br>
--the description doesn't quite match any of these 2 - but it could be<br>
another side effect of these<br>
<a href="http://www.postgresql.org/docs/8.3/static/release-8-3-7.html" target="_blank">http://www.postgresql.org/docs/8.3/static/release-8-3-7.html</a><br>
<br>
Fix planner problem with sub-SELECT in the output list of a larger subquery<br>
(Tom)<br>
<br>
Or<br>
<a href="http://www.postgresql.org/docs/8.3/static/release-8-3-6.html" target="_blank">http://www.postgresql.org/docs/8.3/static/release-8-3-6.html</a><br>
Fix planner misestimation of selectivity when transitive equality is applied<br>
to an outer-join clause (Tom)<br>
<br>
This could result in bad plans for queries like ... from a left join b on<br>
a.a1 = b.b1 where a.a1 = 42 ...<br>
<br>
<br>
<br>
Hope that helps,<br>
Regina<br>
<div class="im"><br>
-----Original Message-----<br>
From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>
[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of<br>
</div><div class="im">Burgholzer,Robert<br>
Sent: Wednesday, March 18, 2009 9:05 AM<br>
To: PostGIS Users Discussion<br>
</div><div><div></div><div class="h5">Subject: [postgis-users] Index Use Craziness<br>
<br>
I am having much trouble getting postgres to use the proper index.<br>
<br>
I have a table with about 20 million daily rainfall observation points, with<br>
between 0 and 19,000 points on any given day. In order to speed up<br>
querying, I have created a GIST index on the geometry column, and an index<br>
on the date column.<br>
<br>
What I want to do is to summarize the observed rainfall in another shape<br>
table on any given day. If I just want to query points by date, this uses<br>
the date index, and performs very quickly (see "CASE 1" below).<br>
<br>
When I try to introduce a geometry operator, the GIST index takes over, and<br>
the index on the "thisdate" column is ignored. The weird thing is, I can<br>
hear it churning the heck out of my hard drive checking on all the points.<br>
(See "CASE 2" below).<br>
<br>
I even tried to sub-query the points for the given date, but the index did<br>
not seem to be fooled ("CASE 3"). It gave me the exact same query plan!! I<br>
am unsure if I really understand the way a sub-query functions given this<br>
result.<br>
<br>
Any help would be appreciated!<br>
<br>
Robert<br>
<br>
CASE 1:<br>
wsp=# explain select count(*) from precip_gridded where thisdate =<br>
'2009-01-12';<br>
<br>
QUERY PLAN<br>
------------------------------------------------------------------------<br>
----<br>
Aggregate (cost=479.43..479.44 rows=1 width=0)<br>
-> Index Scan using pg_tdix on precip_gridded (cost=0.00..451.92<br>
rows=11004<br>
width=0)<br>
Index Cond: (thisdate = '2009-01-12 00:00:00'::timestamp without<br>
time zone)<br>
(3 rows)<br>
<br>
<br>
CASE 2:<br>
explain select count(*) from precip_gridded where thisdate = '2009-01-12'<br>
and the_geom && setsrid((select extent(the_geom) from proj_seggroups where<br>
gid = 448),4326);<br>
<br>
QUERY PLAN<br>
------------------------------------------------------------------------<br>
----<br>
Aggregate (cost=100.23..100.24 rows=1 width=0)<br>
InitPlan<br>
-> Aggregate (cost=5.87..5.88 rows=1 width=2807)<br>
-> Index Scan using psg_giix on proj_seggroups<br>
(cost=0.00..5.87 rows=1 width=2807)<br>
Index Cond: (gid = 448)<br>
-> Index Scan using pg_gix on precip_gridded (cost=0.00..94.35<br>
rows=1 width=0)<br>
Index Cond: (the_geom && setsrid(($0)::geometry, 4326))<br>
Filter: ((thisdate = '2009-01-12 00:00:00'::timestamp without time<br>
zone) AND (the_geom && setsrid(($0)::geometry, 4326)))<br>
(8 rows)<br>
<br>
<br>
CASE 3: Sub-query by date to try and force the use of date index wsp=#<br>
explain select count(a.*) from (select * from precip_gridded where thisdate<br>
= '2009-01-12') as a where a.the_geom && setsrid((select<br>
extent(the_geom) from proj_seggroups where gid = 448),4326);<br>
<br>
QUERY PLAN<br>
<br>
------------------------------------------------------------------------<br>
----<br>
Aggregate (cost=100.23..100.24 rows=1 width=81)<br>
InitPlan<br>
-> Aggregate (cost=5.87..5.88 rows=1 width=2807)<br>
-> Index Scan using psg_giix on proj_seggroups<br>
(cost=0.00..5.87 rows=1 width=2807)<br>
Index Cond: (gid = 448)<br>
-> Index Scan using pg_gix on precip_gridded (cost=0.00..94.35<br>
rows=1 width=81)<br>
Index Cond: (the_geom && setsrid(($0)::geometry, 4326))<br>
Filter: ((thisdate = '2009-01-12 00:00:00'::timestamp without time<br>
zone) AND (the_geom && setsrid(($0)::geometry, 4326)))<br>
(8 rows)<br>
<br>
<br>
Robert W. Burgholzer<br>
Surface Water Modeler<br>
Office of Water Supply and Planning<br>
Virginia Department of Environmental Quality <a href="mailto:rwburgholzer@deq.virginia.gov">rwburgholzer@deq.virginia.gov</a><br>
804-698-4405<br>
Open Source Modeling Tools:<br>
<a href="http://sourceforge.net/projects/npsource/" target="_blank">http://sourceforge.net/projects/npsource/</a><br>
<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br>