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

Re: Partial index on date column

From: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
To: <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <dpage(at)vale-housing(dot)co(dot)uk>, <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Partial index on date column
Date: 2003-03-06 20:08:46
Message-ID: 50057.80.177.99.193.1046981326.squirrel@ssl.vale-housing.co.uk (view raw or flat)
Thread:
Lists: pgsql-hackers
It's rumoured that Tom Lane once said:
> "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.

Oh, OK. Thanks Tom. I can obviously work around this in my PHP code, it
just struck me as odd. I assume then that the optimizer doesn't execute
the function, and that that's done later on? Would the same be true of
simple expressions such as 1 + 2?
Regards, Dave.



In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2003-03-06 20:18:56
Subject: Re: Index File growing big.
Previous:From: mlwDate: 2003-03-06 19:59:00
Subject: Re: Aggregate "rollup"

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