> 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 whether it 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 (assuming low 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 probably quite different from the order that they are on the disk, so the disk had to seek a lot. In addition, it had to read the index.
Taking some wild guesses about the distribution of your data, I'd hazard a guess that this specific query could be sped up a 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 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
