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

On 3/15/06, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)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