Thread: BETWEEN optimizer problems with single-value range

Attached is a simplified example of a performance problem we have seen,
with a workaround and a suggestion for enhancement (hence both the
performance and hackers lists).

Our software is allowing users to specify the start and end dates for a
query.  When they enter the same date for both, the optimizer makes a
very bad choice.  We can work around it in application code by using an
equality test if both dates match.  I think the planner should be able
to make a better choice here.  (One obvious way to fix it would be to
rewrite "BETWEEN a AND b" as "= a" when a is equal to b, but it seems
like there is some underlying problem which should be fixed instead (or
in addition to) this.

The first query uses BETWEEN with the same date for both min and max
values.  The second query uses an equality test for the same date.  The
third query uses BETWEEN with a two-day range.  In all queries, there
are less than 4,600 rows for the specified cotfcNo value out of over 18
million rows in the table.  We tried boosting the statistics samples for
the columns in the selection, which made the estimates of rows more
accurate, but didn't change the choice of plans.

-Kevin

On 3/15/06, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
> Attached is a simplified example of a performance problem we have seen,
> with a workaround and a suggestion for enhancement (hence both the
> performance and hackers lists).


Hi Kevin.  In postgres 8.2 you will be able to use the row-wise
comparison for your query which  should guarantee good worst case
performance without having to maintain two separate query forms.  it
is also a more elegant syntax as you will see.

SELECT "CA"."calDate", "CA"."startTime"
  FROM "Cal" "CA"
  WHERE ("CA"."ctofcNo", "CA"."calDate") BETWEEN
    (2192, '2006-03-15') and (2192, '2006-03-15')
  ORDER BY "ctofcNo", "calDate", "startTime";

Be warned this will not work properly in pg < 8.2.  IMO, row-wise is
the best way to write this type of a query. Please note the row
constructor and the addition of ctofcNo into the order by clause to
force use of the index.

Merlin

On 3/15/06, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
> Merlin Moncure <mmoncure@gmail.com> schrieb:
>
> > On 3/15/06, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
> > > Attached is a simplified example of a performance problem we have seen,
> > > with a workaround and a suggestion for enhancement (hence both the
> > > performance and hackers lists).
> >
> >
> > Hi Kevin.  In postgres 8.2 you will be able to use the row-wise
>
> 8.2? AFAIK, Feature freeze in juni/juli this year...
> Release august/september.

yes, but I was addressing kevin's point about enhancing the server...

> > comparison for your query which  should guarantee good worst case
> > performance without having to maintain two separate query forms.  it
>
> Perhaps, a bitmap index scan (since 8.1) are useful for such querys.
> Thats why i asked which version.

I think you will find that reading a range of records from a table
ordered by an index utilizing the 8.2 comparison feature is much
faster than a bitmap index scan.

Merlin