I have a table that is likely to grow over the next few years at a rate
of 1K-2K rows/day. As the vast majority of the activity on the table
(other than the inserts) will be selects of data for the current day, I
have a cron job that drops and recreates a partial index just after
midnight. It also vacuum analyzes the table.
-- Index: public.pbx_log_today_idx
CREATE INDEX pbx_log_today_idx ON pbx_log USING btree (pbx_time,
pbx_call_type, pbx_digits_source, pbx_digits_destination) WHERE
(pbx_date = '2003-03-06'::date);
I'm surprised by the following behaviour:
EXPLAIN SELECT * FROM pbx_log WHERE pbx_date = CURRENT_DATE;
Seq Scan on pbx_log (cost=0.00..286.20 rows=1274 width=384) Filter: (pbx_date = ('now'::text)::date)
EXPLAIN SELECT * FROM pbx_log WHERE pbx_date = '2003-03-06';
Index Scan using pbx_log_today_idx on pbx_log (cost=0.00..5.00 rows=0
width=384) Filter: (pbx_date = '2003-03-06'::date)
Is this just an oddity because I don't have masses of data yet (4500
rows right now), or is this something the optimizer cannot handle?
Regards, Dave.
