On Wed, Dec 1, 2010 at 3:53 AM, Kristian Nielsen <kniel...@knielsen-hq.org> wrote: > Greg Stark <gsst...@mit.edu> writes: > >> Just so everyone is on the same page.... Even once we have index-only >> scans they won't be anywhere near as useful with Postgres as they are >> with Oracle and other databases. At least not unless we find a >> solution for a different problem -- our inability to scan btree >> indexes sequentially. > > True, however I would not be too pessimistic about this. > > For OLTP like typical web applications, index-only scans are a killer feature > for being able to read N rows with 1 I/O (for some small N), when the data no > longer fits in the buffer pool, or after cold start. > > Eg. read all account settings for one user account, or subjects of all > messages, etc. A composite index with user_id in the first column allows to > fetch all N rows from one (or a few) disk pages with an index-only scan, as > opposed to N disk pages. > > So for this, index-only scans can make a _big_ difference, even without > support for Oracle-type index fast-full-scans.
I am not trying start a MySQL vs PostgreSQL thread. I lurk on these lists to learn more about PostgreSQL. I know that PostgreSQL is good at OLTP and complex query processing and that index fast-full scans can make a big difference for large joins, but the workload that I care about is OLTP-only. PostgreSQL will be more efficient on that workload with support for index-only scans. The majority of the load is simple queries -- joins that touch a few rows, short index range scans and index point lookups. With covering indexes and InnoDB the queries do a few disk reads in the worst case. Without covering indexes the queries do extra disk IO in the worst case (buffer pool read miss) and this is much worse for the range scans. I assume that the behavior with covering indexes but without index-only scans is similar to not having index-only scans. I collect 95th percentile response times for my popular queries and they are much improved with the use of covering indexes. -- Mark Callaghan mdcal...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
