From: | "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Partial index on date column |
Date: | 2003-03-06 16:52:32 |
Message-ID: | 03AF4E498C591348A42FC93DEA9661B885D0@mail.vale-housing.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Rod Taylor | 2003-03-06 16:55:37 | Re: Row level stats |
Previous Message | Tom Lane | 2003-03-06 16:49:15 | Re: Row level stats |