Partial index on date column
От | Dave Page |
---|---|
Тема | Partial index on date column |
Дата | |
Msg-id | 03AF4E498C591348A42FC93DEA9661B885D0@mail.vale-housing.co.uk обсуждение исходный текст |
Ответы |
Re: Partial index on date column
Re: Partial index on date column |
Список | 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.
В списке pgsql-hackers по дате отправления: