Hi!
I've got a query that has a where clause on a timestamp field:
select t.board_id
, t.thread_id
from public.board_thread t
where t.last_reply <= now()-'6 months'::interval
limit 1
I've got random_page_cost set to 1.4 which is fine for most queries; yet here the planner prefers a (slower) sequential scan:
QUERY PLAN
Limit (cost=0.00..0.14 rows=1 width=8) (actual time=2.598..2.600 rows=1 loops=1)
-> Seq Scan on board_thread t (cost=0.00..4613.87 rows=33674 width=8) (actual time=2.592..2.592 rows=1 loops=1)
Filter: (last_reply <= (now() - '6 mons'::interval))
Total runtime: 2.711 ms
When I set random_page_cost extremely low, the planner makes a better decision; the "breaking point" ist at set random_page_cost = 0.16938, which seems much to unrealistic to use as a general setting:
QUERY PLAN
Limit (cost=0.00..0.14 rows=1 width=8) (actual time=0.142..0.143 rows=1 loops=1)
-> Index Scan using idx_bt_last_reply on board_thread t (cost=0.00..4613.72 rows=33674 width=8) (actual time=0.137..0.137 rows=1 loops=1)
Index Cond: (last_reply <= (now() - '6 mons'::interval))
Total runtime: 0.281 ms
When I change my query slightly so as to retrieve only rows with an "equals" match, the planner prefers the index-scan alright, even with the 1.4-setting:
select t.board_id
, t.thread_id
from public.board_thread t
where t.last_reply <= now()-'6 months'::interval
limit 1
QUERY PLAN
Limit (cost=0.00..1.83 rows=1 width=8) (actual time=0.321..0.321 rows=0 loops=1)
-> Index Scan using idx_bt_last_reply on board_thread t (cost=0.00..3.66 rows=2 width=8) (actual time=0.315..0.315 rows=0 loops=1)
Index Cond: (last_reply = (now() - '6 mons'::interval))
Total runtime: 0.457 ms
Now here's my question: Is there some other screw to turn in order to tune general planner behaviour so it better matches this specific kind of query, too, or is it preferrable to leave general settings as they are and just use a set enable_seqscan = off; before this type of query?
Kind regards
Markus
