Since I wrote my previous blog entry on index-only scans, quite a bit of additional work has been done. Tom Lane cleaned up the code and improved the costing model, but possibly the most interesting thing he did was to allow index-only scans to be used for queries that don't involve an indexable condition at all. The classic example is SELECT COUNT(*) FROM table. In previous versions of PostgreSQL, there's just one way to implement this: sequential scan the table and count 'em up. In PostgreSQL 9.2, that method will still, of course, be available, but now there will be another choice: pick any index you like and do a full index scan, checking whether each tuple is all-visible either using the visibility map or via a heap fetch. So, how well does it work?
Showing posts with label index-only. Show all posts
Showing posts with label index-only. Show all posts
Monday, October 31, 2011
Friday, October 07, 2011
Index-Only Scans: We've Got 'Em
Tom Lane committed a patch for index-only scans by myself and Ibrar Ahmed, which also incorporated some previous work by Heikki Linnakangas, after hacking on it some more himself. Woohoo!
There is, of course, more work to be done here - performance fine-tuning, cost estimation, extensions to the core functionality - but the core of the feature is now in. If you get a chance, please test it out and let us know how it works for you.
For those that may not have been following along at home, what we're essentially doing here is allowing any index to act as a "covering index". If all of the columns the query needs are available from the index tuple, we'll skip fetching the corresponding heap (table) page if every tuple on that page is visible to all running transactions.
Although I know we're not even really done with this feature yet, I can't help wondering what's next. Index-only scans have so often be cited as "the big performance feature that PostgreSQL is missing" that it's become something of a cliché. Now that we have them, what will take their place as the next big thing?
There is, of course, more work to be done here - performance fine-tuning, cost estimation, extensions to the core functionality - but the core of the feature is now in. If you get a chance, please test it out and let us know how it works for you.
For those that may not have been following along at home, what we're essentially doing here is allowing any index to act as a "covering index". If all of the columns the query needs are available from the index tuple, we'll skip fetching the corresponding heap (table) page if every tuple on that page is visible to all running transactions.
Although I know we're not even really done with this feature yet, I can't help wondering what's next. Index-only scans have so often be cited as "the big performance feature that PostgreSQL is missing" that it's become something of a cliché. Now that we have them, what will take their place as the next big thing?
Thursday, August 18, 2011
Index-Only Scans: Now There's a Patch
In November of 2010, I blogged about a much-requested PostgreSQL feature: index-only scans. We've made some progress! In June of this year, I committed a patch (and then, after Heikki found some bugs, another patch) to make the visibility map crash-safe. In previous releases, it was possible for the visibility map to become incorrect after a system crash, which means that it could not be relied on for anything very critical. That should be fixed now. Last week, I posted a patch for the main feature: index-only scans.
Monday, November 22, 2010
Index-Only Scans
There seems to be a lot of interest in the as-yet-unimplemented performance feature called index-only scans, so I thought it would be useful to explain a little bit more about what this feature is, how it will help PostgreSQL, and where the bodies are buried.
First, the name. What do we mean by an index-only scan? In PostgreSQL today, an index scan always accesses both the index itself and the underlying table. You might think this unnecessary. For example, if you have the query SELECT name FROM table WHERE id = 10, and there is an index on (id, name), you might assume that we could use the index to check for tuples with id = 10, and the if one is found, return the name directly from the index tuple, without consulting the underlying table. Unfortunately, this does not work, because that tuple might not actually be one that the SELECT statement can see. If the tuple was inserted by a transaction which began after the SELECT statement took its MVCC snapshot, or deleted by a transaction which committed before the SELECT statement took its MVCC snapshot, then the SELECT statement must not return it. If it did, we would quickly get very surprising wrong answers out of the database. So PostgreSQL first looks at the index tuple, and then the heap (table) tuple, decides what the right thing to do is, and does it. By an index ONLY scan, we mean one which will look at just the index, and not at the corresponding table; the trick is to figure out how to make that happen without returning wrong answers.
First, the name. What do we mean by an index-only scan? In PostgreSQL today, an index scan always accesses both the index itself and the underlying table. You might think this unnecessary. For example, if you have the query SELECT name FROM table WHERE id = 10, and there is an index on (id, name), you might assume that we could use the index to check for tuples with id = 10, and the if one is found, return the name directly from the index tuple, without consulting the underlying table. Unfortunately, this does not work, because that tuple might not actually be one that the SELECT statement can see. If the tuple was inserted by a transaction which began after the SELECT statement took its MVCC snapshot, or deleted by a transaction which committed before the SELECT statement took its MVCC snapshot, then the SELECT statement must not return it. If it did, we would quickly get very surprising wrong answers out of the database. So PostgreSQL first looks at the index tuple, and then the heap (table) tuple, decides what the right thing to do is, and does it. By an index ONLY scan, we mean one which will look at just the index, and not at the corresponding table; the trick is to figure out how to make that happen without returning wrong answers.
Subscribe to:
Posts (Atom)