[postgis-users] Index Use Craziness

Paragon Corporation lr at pcorp.us
Thu Mar 19 09:11:26 PDT 2009


Robert,

Bummer.  Well here are 2 more hackish thoughts 

 1) Try to materialize the date query by putting in a pointless order by

select count(a.*) from (select * from precip_gridded where thisdate =
'2009-01-12' ORDER BY the_geom) as a where a.the_geom && st_setsrid((select
St_extent(the_geom) from proj_seggroups where gid = 448),4326);


2) It also occurred to me that I'm usually not in the habit of putting
non-correlated sub selects the way you have it, so maybe you'd have better
luck doing it the way I usually do -- like

select count(a.*) from (select * from precip_gridded where thisdate =
'2009-01-12' ) as a INNER JOIN 
(SELECT st_setsrid((select
St_extent(the_geom) from proj_seggroups where gid = 448),4326) As the_geom)
As b ON (a.the_geom && b.the_geom)



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: Thursday, March 19, 2009 11:47 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Index Use Craziness

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

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


__________ Information from ESET NOD32 Antivirus, version of virus signature
database 3948 (20090319) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com







More information about the postgis-users mailing list