I wish I could be at PGWest
I wish I could go to PGWest this year. A lot of great work has been done on PostgreSQL in the last year and a half. There’s a new release with built-in replication, and there are in-place upgrades. That solves two of what I think are its three biggest shortcomings in many large-scale database deployments. (Lack of index-only queries is the third shortcoming.)
PGWest is the first major conference about Postgres since the 9.0 release, so all the cool stuff is happening there. I encourage MySQL users to go to it as well — there is a lot to learn from PostgreSQL. Just remember to be polite and don’t start, encourage, or tolerate any sniping between MySQL and Postgres fans.
Can you explain more about the issue of lack of index-only queries? If I had a covering index, wouldn’t I get index-only queries?
Andy
19 Oct 10 at 12:03 pm
No.
Xaprb
19 Oct 10 at 12:32 pm
@Andy: The indexes do not contain visibility information. In order for postgres to confirm that the information found in the index is actually supposed to be visible to the client, it must look up the visibility information in the table first. This can be quite expensive.
Ryan
19 Oct 10 at 12:35 pm
@Ryan. Ah thanks for the explanation. I did not know that.
Andy
19 Oct 10 at 12:39 pm
@Baron:
I talked with Bruce Momjian and Josh Berkus at OpenSQL Camp about index only queries. There is evidently a push to have indexes where all tuples are visible be able to perform index only queries. I am sure that has a use case, I just have not run across it.
Rob Wultsch
19 Oct 10 at 9:04 pm
Basically, the idea AIUI is to optimize the case where all tuples in the page are known visible, that is, unchanged under any current running transaction. This will allow the lookup back to the heap to check visibility to be skipped, and the data returned directly out of the index. The higher the ratio of reads to writes on a particular page is, the more this optimization pays. Effectively, this will give you covering indexes at the expense of maintaining the visibility bit (which is more complicated than it sounds).
Merlin Moncure
10 Nov 10 at 12:02 pm