[postgis-users] Index Use Craziness
Burgholzer,Robert
rwburgholzer at deq.virginia.gov
Thu Mar 19 08:46:40 PDT 2009
Regina,
Thanks for the insight, however, I just upgraded to 8.13 this morning,
and still no luck. The MSI installer for 8.3 seems to be lacking
postgis (or perhaps the installer has changed significantly) and I am
reticent to tackle this until I return.
Thanks again,
r.b.
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer at deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Paragon Corporation
Sent: Wednesday, March 18, 2009 4:00 PM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] Index Use Craziness
Robert,
I think you just need to replace your binaries. The same fix was put in
the
8.2 release so if you are running lower than 8.2.12, might help to
upgrade.
http://www.postgresql.org/docs/8.2/static/release-8-2-12.html
Fix planner misestimation of selectivity when transitive equality is
applied
to an outer-join clause (Tom)
This could result in bad plans for queries like ... from a left join b
on
a.a1 = b.b1 where a.a1 = 42 ...
http://www.postgresql.org/docs/8.2/static/release-8-2-10.html
(has a bunch of planner fixes too)
Fix planner bug with nested sub-select expressions (Tom)
If the outer sub-select has no direct dependency on the parent query,
but
the inner one does, the outer value might not get recalculated for new
parent query rows.
Fix planner to estimate that GROUP BY expressions yielding boolean
results
always result in two groups, regardless of the expressions' contents
(Tom)
Hope that helps,
Regina
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Burgholzer,Robert
Sent: Wednesday, March 18, 2009 3:27 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Index Use Craziness
Regina,
Thanks, I am using 8.2, and you may be right. I need to migrate to 8.3,
but
am going on vacation in 3 days, and don't want to kill the production
box -
it is our only box currently...
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer at deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Paragon
Corporation
Sent: Wednesday, March 18, 2009 2:32 PM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] Index Use Craziness
Robert,
Which version of PostgreSQL are you running? I wander if you are being
bitten by one of these bugs
--the description doesn't quite match any of these 2 - but it could be
another side effect of these
http://www.postgresql.org/docs/8.3/static/release-8-3-7.html
Fix planner problem with sub-SELECT in the output list of a larger
subquery
(Tom)
Or
http://www.postgresql.org/docs/8.3/static/release-8-3-6.html
Fix planner misestimation of selectivity when transitive equality is
applied
to an outer-join clause (Tom)
This could result in bad plans for queries like ... from a left join b
on
a.a1 = b.b1 where a.a1 = 42 ...
Hope that helps,
Regina
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Burgholzer,Robert
Sent: Wednesday, March 18, 2009 9:05 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Index Use Craziness
I am having much trouble getting postgres to use the proper index.
I have a table with about 20 million daily rainfall observation points,
with
between 0 and 19,000 points on any given day. In order to speed up
querying, I have created a GIST index on the geometry column, and an
index
on the date column.
What I want to do is to summarize the observed rainfall in another shape
table on any given day. If I just want to query points by date, this
uses
the date index, and performs very quickly (see "CASE 1" below).
When I try to introduce a geometry operator, the GIST index takes over,
and
the index on the "thisdate" column is ignored. The weird thing is, I
can
hear it churning the heck out of my hard drive checking on all the
points.
(See "CASE 2" below).
I even tried to sub-query the points for the given date, but the index
did
not seem to be fooled ("CASE 3"). It gave me the exact same query
plan!! I
am unsure if I really understand the way a sub-query functions given
this
result.
Any help would be appreciated!
Robert
CASE 1:
wsp=# explain select count(*) from precip_gridded where thisdate =
'2009-01-12';
QUERY PLAN
------------------------------------------------------------------------
----
Aggregate (cost=479.43..479.44 rows=1 width=0)
-> Index Scan using pg_tdix on precip_gridded (cost=0.00..451.92
rows=11004
width=0)
Index Cond: (thisdate = '2009-01-12 00:00:00'::timestamp
without
time zone)
(3 rows)
CASE 2:
explain select count(*) from precip_gridded where thisdate =
'2009-01-12'
and the_geom && setsrid((select extent(the_geom) from proj_seggroups
where
gid = 448),4326);
QUERY PLAN
------------------------------------------------------------------------
----
Aggregate (cost=100.23..100.24 rows=1 width=0)
InitPlan
-> Aggregate (cost=5.87..5.88 rows=1 width=2807)
-> Index Scan using psg_giix on proj_seggroups
(cost=0.00..5.87 rows=1 width=2807)
Index Cond: (gid = 448)
-> Index Scan using pg_gix on precip_gridded (cost=0.00..94.35
rows=1 width=0)
Index Cond: (the_geom && setsrid(($0)::geometry, 4326))
Filter: ((thisdate = '2009-01-12 00:00:00'::timestamp without
time
zone) AND (the_geom && setsrid(($0)::geometry, 4326)))
(8 rows)
CASE 3: Sub-query by date to try and force the use of date index wsp=#
explain select count(a.*) from (select * from precip_gridded where
thisdate
= '2009-01-12') as a where a.the_geom && setsrid((select
extent(the_geom) from proj_seggroups where gid = 448),4326);
QUERY PLAN
------------------------------------------------------------------------
----
Aggregate (cost=100.23..100.24 rows=1 width=81)
InitPlan
-> Aggregate (cost=5.87..5.88 rows=1 width=2807)
-> Index Scan using psg_giix on proj_seggroups
(cost=0.00..5.87 rows=1 width=2807)
Index Cond: (gid = 448)
-> Index Scan using pg_gix on precip_gridded (cost=0.00..94.35
rows=1 width=81)
Index Cond: (the_geom && setsrid(($0)::geometry, 4326))
Filter: ((thisdate = '2009-01-12 00:00:00'::timestamp without
time
zone) AND (the_geom && setsrid(($0)::geometry, 4326)))
(8 rows)
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer at deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list