On Sun, 11 Sep 2005, Mark Dilger wrote: > All, > > In the psql output below, I would expect the second query to run faster, > because the b-tree index on two columns knows the values of 'b' for any > given value of 'a', and hence does not need to fetch a row from the > actual table. I am not seeing a speed-up, however, so I expect my > understanding of the index mechanism is wrong. Could anyone enlighten > me?
A common but incorrect assumption. We must consult the underlying table when we do an index scan so that we can check visibility information. The reason it is stored there in the table is so that we have only one place to check for tuple visibility and therefore avoid race conditions. A brief explanation of this system is described here: http://www.postgresql.org/docs/8.0/static/mvcc.html. and this page shows what information we store in the to do visibility checks: http://www.postgresql.org/docs/8.0/static/storage-page-layout.html Thanks, Gavin ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
