Re: "where x between y and z" for timestamp data types

The 'date' field is a timestamp without timezone. The upper and lower
bounds of the BETWEEN are identical just for testing purposes. Any range
would do.

I'm having trouble reproducing the problem on a similar data set. I
created a new db, same table schema, same row count with randomly generated
data for testing but the function works fine. So perhaps my problem is
related to the tables rather than the function. If I can successfully
reproduce the problem with another data set I'll send example code to
share. I'll also look into the links you gave me.

Thanks,
Kaib

On Sun, Aug 12, 2012 at 2:28 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > I briefly tried your example and I didn't see a problem. Can you provide
> > some sample data that illustrates your problem? Also, is the "date"
> > field a date or a timestamp? And why are the upper and lower bounds of
> > the BETWEEN identical?
>
> I'll bet a nickel the planner is choosing a different plan when it
> doesn't know that the timestamp range condition is extremely selective.
>
> regards, tom lane
>