[postgis-users] Index Use Craziness

[Paragon Corporation]

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