Cost of indexscan

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 WITHOUT TIME 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 WITHOUT TIME 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) (actual time=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 GROUP BY 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(at)bdb(dot)fi - (050) 380 3808
__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""