Re: Partial index on date column

From: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
To: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Partial index on date column
Date: 2003-03-07 02:08:49
Message-ID: 011c01c2e44e$7e245ed0$6500a8c0@fhp.internal
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

It's the same as the slight issue I had:

CREATE INDEX users_users_referrer_idx ON users_users(referrer) WHERE
(referrer IS NOT NULL);

usa=# explain analyze select * from users_users where referrer=1;
QUERY PLAN
----------------------------------------------------------------------------
---------------------------
Seq Scan on users_users (cost=0.00..3.89 rows=8 width=235) (actual
time=10.51..13.47 rows=8 loops=1)
Filter: (referrer = 1)

usa=# explain analyze select * from users_users where referrer=1 and
referrer is not null;
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------------
Index Scan using users_users_referrer_idx on users_users (cost=0.00..3.01
rows=1 width=235) (actual time=17.12..17.36 rows=8 loops=1)

Obviously to you and I, referrer=1 implies that referrer is not null, but
the planner doesn't know that. You often have to add a redundant clause to
the query to ensure that the partial index is used.

Chris

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Marc G. Fournier 2003-03-07 02:10:15 Bad crash, pg_clog files missing ... ?
Previous Message Christopher Kings-Lynne 2003-03-07 02:02:34 Re: bug in contrib/adddepend