Sub-optimal plan chosen

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



In the following query, We are seeing a sub-optimal plan being chosen. The following results are after running the query several times (after each change).

dev1=# select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.13 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)



dev1=# EXPLAIN ANALYZE SELECT SUM (revenue) as revenue FROM statsdaily WHERE ofid = 38 AND date >= '2009-09-01' AND date <= '2999-01-01';
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=11796.19..11796.20 rows=1 width=8) (actual time=28.598..28.599 rows=1 loops=1)
   ->  Index Scan using statsdaily_unique_idx on statsdaily  (cost=0.00..11783.65 rows=5017 width=8) (actual time=0.043..25.374 rows=3125 loops=1)
         Index Cond: ((date >= '2009-09-01'::date) AND (date <= '2999-01-01'::date) AND (ofid = 38))
 Total runtime: 28.650 ms


dev1=# set enable_indexscan to off;


dev1=# EXPLAIN ANALYZE SELECT SUM (revenue) as revenue FROM statsdaily WHERE ofid = '38' AND date >= '2009-09-01' AND date <= '2999-01-01';
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=13153.47..13153.48 rows=1 width=8) (actual time=7.746..7.747 rows=1 loops=1)
   ->  Bitmap Heap Scan on statsdaily  (cost=3622.22..13140.92 rows=5017 width=8) (actual time=0.941..4.865 rows=3125 loops=1)
         Recheck Cond: ((ofid = 38) AND (date >= '2009-09-01'::date))
         Filter: (date <= '2999-01-01'::date)
         ->  Bitmap Index Scan on statsdaily_ofid_sept2009_idx  (cost=0.00..3620.97 rows=5046 width=0) (actual time=0.551..0.551 rows=3125 loops=1)
               Index Cond: (ofid = 38)
 Total runtime: 7.775 ms


default_statistics_target = 100 (tried with 500, no change). Vacuum analyzed before initial query, and after each change to default_statistics_target.


The same query, with a different "ofid", will occasionally get the more optimal plan -- I assume that the distribution of data is the differentiator there.

Is there any other data I can provide to shed some light on this?

Thanks!

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux