On Mon, May 16, 2011 at 4:19 AM, Robert Klemme <shortcutter@xxxxxxxxxxxxxx> wrote: >> - If the planner chooses a Bitmap Index Scan, it effectively scans the >> index to figure out which table blocks to read, and then reads those >> table blocks in block number order, so that the I/O is sequential, >> with skips. > > Are these two separate phases (i.e. first scan index completely, then > access table)? Yes. > Separating index and tables might not be a totally good idea > generally. Richard Foote has an excellent article about Oracle but I > assume at least a few things do apply to PostgreSQL as well - it's at > least worth as something to check PostgreSQL's access patterns > against: > > http://richardfoote.wordpress.com/2008/04/16/separate-indexes-from-tables-some-thoughts-part-i-everything-in-its-right-place/ > > I would probably rather try to separate data by the nature and > frequency of accesses. One reasonable separation would be to leave > all frequently accessed tables *and* their indexes on local RAID and > moving less frequently accessed data to the SAN. This separation > could be easily identified if you have separate tables for current and > historic data. Yeah, I think the idea of putting tables and indexes in separate tablespaces is mostly to bring more I/O bandwidth to bear on the same data. But there are other reasonable things you might do also - e.g. put the indexes on an SSD, and the tables on a spinning disk, figuring that the SSD is less reliable but you can always rebuild the index if you need to... Also, a lot of people have reported big speedups from putting pg_xlog on a dedicated RAID 1 pair, or moving the PostgreSQL logs off the data partition. So those sorts of divisions should be considered also. Your idea of dividing things by access frequency is another good thought. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
