Optimizer regression 8.2.1 -> 8.2.3 on TSEARCH2 queries with ORDER BY and LIMIT

Hi folks,

An apparent optimizer regression between 8.2.1 & 8.2.3 ? :

select pk,... from tbl where tsv @@ to_tsquery(...) order by pk limit 10

disadvantageously uses PK index scan against a 2.5 million row (vacuum analysed) table whenever limit<=16 , leading to an increase in query time from sub 100ms to 4 seconds typically.

With identical freshly vaccuum analyzed table, 8.2.1 does the same only when limit <= 3

Although it's not a difference in principle, the later behaviour is more problematic as it is much more likely to be encountered in practice as part of a results paging scheme (with OFFSET N)

Changing the ORDER BY clause to pk ||'' seems to get around the problem without any substantial execution overhead.

Anyone aware of any alternate workaround or info on likely behaviour in 8.3 ?

Brendan

* ** *** ** * ** *** ** * ** *** ** *
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed.
Any views or opinions presented are solely those of the author, and do not necessarily
represent those of ESB.
If you have received this email in error please notify the sender.

Although ESB scans e-mail and attachments for viruses, it does not guarantee
that either are virus-free and accepts no liability for any damage sustained
as a result of viruses.

Company Registration Information: http://www.esb.ie/companies
* ** *** ** * ** *** ** * ** *** ** *