> > Attached is a patch that adds support for specifying a
> > location  for indexes via the "create database" command.
> > 
> > I believe this patch is complete, but it is my first .
> 
> This patch allows index locations to be specified as
> different from data locations. Is this a feature direction
> we want to go in?  Comments?

The more general and "standard" way to go are TABLESPACEs.
But probably proposed feature will be compatible with
tablespaces, when we'll got them: we could use new "create
database" syntax to specify default tablespace for indices.
Unfortunately I removed message with patch, can you send it
to me, Bruce?

Vadim

Wed, 12 Sep 2001 09:58:20 -0700
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
Re: Index location patch for review
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> The more general and "standard" way to go are TABLESPACEs.
> But probably proposed feature will be compatible with
> tablespaces, when we'll got them:

Will it be?  I'm afraid of creating a backwards-compatibility
problem for ourselves when it comes time to implement tablespaces.

At the very least I'd like to see some information demonstrating
how much benefit there is to this proposed patch, before we
consider whether to adopt it.  If there's a significant performance
benefit to splitting a PG database along the table-vs-index divide,
then it's interesting as a short-term improvement ... but Jim didn't
even make that assertion, let alone provide evidence to back it up.

			regards, tom lane

Wed, 12 Sep 2001 13:54:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Index location patch for review 
> "Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> > The more general and "standard" way to go are TABLESPACEs.
> > But probably proposed feature will be compatible with
> > tablespaces, when we'll got them:
> 
> Will it be?  I'm afraid of creating a backwards-compatibility
> problem for ourselves when it comes time to implement tablespaces.
> 
> At the very least I'd like to see some information demonstrating
> how much benefit there is to this proposed patch, before we
> consider whether to adopt it.  If there's a significant performance
> benefit to splitting a PG database along the table-vs-index divide,
> then it's interesting as a short-term improvement ... but Jim didn't
> even make that assertion, let alone provide evidence to back it up.

If that is your only concern, I can tell you for sure that if the
locations are on different drives, there will be a performance benefit. 
It is standard database practice to put indexes on different drives than
data.  In fact, sometimes you want to put two tables that are frequently
joined on separate drives.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 12 Sep 2001 14:22:05 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Index location patch for review
...
> At the very least I'd like to see some information demonstrating
> how much benefit there is to this proposed patch, before we
> consider whether to adopt it.  If there's a significant performance
> benefit to splitting a PG database along the table-vs-index divide,
> then it's interesting as a short-term improvement ... but Jim didn't
> even make that assertion, let alone provide evidence to back it up.

Clearly there can be a *storage management* benefit to having control
over what gets put where, so this does not need to be justified strictly
on a performance basis.

For features like this, we will feel free to evolve them or
revolutionize them with further development, so I'm not worried about
the backward compatibility issue for cases like this.

Comments?

                    - Thomas

Wed, 12 Sep 2001 18:24:34 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Index location patch for review
> ...
> > At the very least I'd like to see some information demonstrating
> > how much benefit there is to this proposed patch, before we
> > consider whether to adopt it.  If there's a significant performance
> > benefit to splitting a PG database along the table-vs-index divide,
> > then it's interesting as a short-term improvement ... but Jim didn't
> > even make that assertion, let alone provide evidence to back it up.
> 
> Clearly there can be a *storage management* benefit to having control
> over what gets put where, so this does not need to be justified strictly
> on a performance basis.
> 
> For features like this, we will feel free to evolve them or
> revolutionize them with further development, so I'm not worried about
> the backward compatibility issue for cases like this.
> 
> Comments?

Agreed.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 12 Sep 2001 14:26:26 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Index location patch for review