Partial index on date column

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.

Responses

Browse pgsql-hackers by date

  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