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