Re: index not always used when selecting on a date field

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Miquel van Smoorenburg" <miquels(at)cistron(dot)nl>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: index not always used when selecting on a date field
Date: 2004-11-08 23:00:11
Message-ID: 11542.1099954811@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

list-pgsql-general(at)news(dot)cistron(dot)nl ("Miquel van Smoorenburg" ) writes:
> techdb2=> explain select * from lines where (removed > CURRENT_DATE AND removed < '9999-01-01');

> With 7.3, this query used the index, while with 7.4 it doesn't.

Perhaps you hadn't ANALYZEd in 7.3? AFAICS 7.3 and 7.4 behave
essentially alike on this point, given comparable statistics.

One thing I did notice in looking at this is that the preferential
treatment for range constraints only applies when *both* sides of the
range are un-estimatable. So you need to write something like

WHERE (removed > CURRENT_DATE AND removed < CURRENT_DATE + 10000)

to get it to work nicely. I'll see if I can improve on that for 8.0;
seems like the way you tried ought to work, too.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Devrim GUNDUZ 2004-11-08 23:12:06 Re: trouble with rpmbuild on WBEL3.0/x86_64
Previous Message Marc G. Fournier 2004-11-08 22:31:55 Re: RFD: comp.databases.postgresql.general