[postgis-users] Index Use Craziness

[Paul Ramsey]

Robert, if you could EXPLAIN ANALYZE your bad query, that would point
out where the mis-estimation is occurring. It sounds much like the
planner thinks the spatial index is more selective than it is.

Also, the advice below on partitioning is good, for any service based
on a large continuous data stream. However, you still shouldn't be
getting a bad plan :)

P.

On Wed, Mar 18, 2009 at 6:04 AM, Burgholzer,Robert
<rwburgholzer at deq.virginia.gov> wrote:
> 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
>