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
* ** *** ** * ** *** ** * ** *** ** *
