On Mon, May 16, 2011 at 10:19 AM, Robert Klemme <shortcutter@xxxxxxxxxxxxxx> wrote:
Well, after reading your article i have been reading some materail about it on the internet, stating that separating indexes from data for performance benefits is a myth.On Fri, May 13, 2011 at 9:04 PM, Robert Haas <robertmhaas@xxxxxxxxx> wrote: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.
I found your comment "So then a single query will only ever access one of both at a time." very smart (no sarcasm there).
I also found a thread on AskTom that said mainly "the goal is to achieve even io." (that makes absolute sense)
In my situation, where i need extra space on a SAN, it seems logical to separate the tables from the indexes, to achieve just that: roughly even IO.. (put tables on san, leave indexes on raid10 cluster)
Or am i being silly?
Cheers,
WBL
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
