On 6-Mar-08, at 5:10 PM, Stephen Denne wrote:
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.
The strange thing of course is that the data is exactly the same for
both runs, the tables have not been changed between runs, and I did
them right after another. Even more strange is that the seq scan is
faster than the index scan.
Dave
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
