Hi,
Postgres seems to estimate the cost of indexscan to be a bit too high.
The table has something like 50 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 - [EMAIL PROTECTED] - (050) 380 3808
__ooO( )Ooo___ _ ___ _ _ _ __ _ _
""
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])