Dave Cramer wrote:
> I have two almost identical queries. Strangely enough the one
> that uses the index is slower ???
The index scan is being used so that it can retrieve the rows in the name order.
It expects that if it was to retrieve every row via the index, it would get
about 1010 rows that matched the filter, and it knows it can stop after 250, so
assuming the matching rows are evenly distributed it thinks it can stop after
having read only a quarter of the rows.
However only 129 rows matched. Consequently it had to read every row in the
table anyway, seeking a fair bit as the read order was specified by the index
rather than in sequential order, and it also had to read the index. These extra
costs were much larger than reading the lot sequentially, and sorting 129
resulting rows.
The first query picked a sequential scan as it thought it was only going to get
11 results, so was expecting that the limit wasn't going to come into play, and
that every row would have to be read anyway.
Regards,
Stephen Denne.
Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer
focus, and courage. This email with any attachments is confidential and may be
subject to legal privilege. If it is not intended for you please advise by
reply immediately, destroy it and do not copy, disclose or use it in any way.
__________________________________________________________________
This email has been scanned by the DMZGlobal Business Quality
Electronic Messaging Suite.
Please see http://www.dmzglobal.com/services/bqem.htm for details.
__________________________________________________________________
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance
