> 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.
It is not strange at all, since both queries read ALL the rows in your table, checking each and every row to see
whetherit matched your predicates.
The sequential scan read them in the order they are on the disk, meaning your disk didn't have to seek as much
(assuminglow file fragmentation).
The index scan again reads all the rows in your table, but reads them in the order they were in the index, which is
probablyquite different from the order that they are on the disk, so the disk had to seek a lot. In addition, it had to
readthe index.
Taking some wild guesses about the distribution of your data, I'd hazard a guess that this specific query could be sped
upa great deal by creating an index on lower(firstname).
Regards,
Stephen.
Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any
attachmentsis 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.
__________________________________________________________________