Re: [PERFORM] BETWEEN optimizer problems with single-value range

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