Cost of indexscan - Mailing list pgsql-performance

Hi,

Postgres seems to estimate the cost of indexscan to be a bit too high.
The table has something like 500000 rows and I have run reindex and vacuum
analyze recently. Is there something to tune?

Index is a multicolumn index:
    "admin_event_stamp_event_type_name_status" btree (stamp, event_type_name, status)

Singlecolumn index for stamp doesn't make a significant difference in cost
estimation.

               -- -- -- -- -- -- -- -- -- --

galleria=> set enable_seqscan = true;
SET
galleria=> explain analyze SELECT * FROM admin_event WHERE stamp > (current_timestamp - '1 days'::INTERVAL)::TIMESTAMP
WITHOUTTIME ZONE; 
                                                       QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
 Seq Scan on admin_event  (cost=0.00..19844.37 rows=154361 width=109) (actual time=479.173..2760.186 rows=4705 loops=1)
   Filter: (stamp > ((('now'::text)::timestamp(6) with time zone - '1 day'::interval))::timestamp without time zone)
 Total runtime: 2765.428 ms
(3 rows)

galleria=> set enable_seqscan = false;
SET
galleria=> explain analyze SELECT * FROM admin_event WHERE stamp > (current_timestamp - '1 days'::INTERVAL)::TIMESTAMP
WITHOUTTIME ZONE; 

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using admin_event_stamp_event_type_name_status on admin_event  (cost=0.00..540690.18 rows=154361 width=109)
(actualtime=7.771..124.886 rows=4706 loops=1) 
   Index Cond: (stamp > ((('now'::text)::timestamp(6) with time zone - '1 day'::interval))::timestamp without time
zone)
 Total runtime: 82.530 ms
(3 rows)

               -- -- -- -- -- -- -- -- -- --

Distribution of stamp looks like the following:

galleria=> SELECT date_trunc('month', stamp)::DATE, count(*), repeat('*', (count(*) / 3000)::INTEGER) FROM admin_event
GROUPBY date_trunc('month', stamp)::DATE ORDER BY 1; 
 date_trunc | count  |                  repeat
------------+--------+-------------------------------------------
 2002-01-01 |   2013 |
 2002-02-01 |   2225 |
 2002-03-01 |   2165 |
 2002-04-01 |   2692 |
 2002-05-01 |   3031 | *
 2002-06-01 |   2376 |
 2002-07-01 |   2694 |
 2002-08-01 |   4241 | *
 2002-09-01 |   4140 | *
 2002-10-01 |   4964 | *
 2002-11-01 |   8769 | **
 2002-12-01 |  13249 | ****
 2003-01-01 |   5776 | *
 2003-02-01 |   6301 | **
 2003-03-01 |   6404 | **
 2003-04-01 |   6905 | **
 2003-05-01 |   7119 | **
 2003-06-01 |   8978 | **
 2003-07-01 |   7723 | **
 2003-08-01 |  36566 | ************
 2003-09-01 |  15759 | *****
 2003-10-01 |  10610 | ***
 2003-11-01 |  83113 | ***************************
 2003-12-01 |  90927 | ******************************
 2004-01-01 | 124479 | *****************************************


    |\__/|
    ( oo )    Kari Lavikka - tuner@bdb.fi - (050) 380 3808
__ooO(  )Ooo_______ _____ ___ _ _  _   _    _      _                  _
      ""