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 rwburghol...@deq.virginia.gov 804-698-4405 Open Source Modeling Tools: http://sourceforge.net/projects/npsource/ -----Original Message----- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@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-boun...@postgis.refractions.net [mailto:postgis-users-boun...@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 rwburghol...@deq.virginia.gov 804-698-4405 Open Source Modeling Tools: http://sourceforge.net/projects/npsource/ _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
