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  ("scott.marlowe" <scott.marlowe@ihs.com>)
Re: Partial index on date column  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Row level stats
Следующее
От: Rod Taylor
Дата:
Сообщение: Re: Row level stats