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

Re: Partial index on date column

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Partial index on date column
Date: 2003-03-06 18:12:35
Message-ID: 17137.1046974355@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
"Dave Page" <dpage(at)vale-housing(dot)co(dot)uk> writes:
> 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;
> [ no indexscan ]

> 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?

The optimizer does not think that "pbx_date = CURRENT_DATE" satisfies the
partial index's WHERE condition.  I don't see any really good way around
this; to improve matters there'd need to be some concept of a plan that
is only good for a limited time.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Rod TaylorDate: 2003-03-06 18:13:28
Subject: Re: TODO: DROP COLUMN .. CASCADE
Previous:From: scott.marloweDate: 2003-03-06 18:03:17
Subject: Re: Partial index on date column

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