Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

Next:From: Rod TaylorDate: 2003-03-06 16:55:37
Subject: Re: Row level stats
Previous:From: Tom LaneDate: 2003-03-06 16:49:15
Subject: Re: Row level stats

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group