PostgreSQL Administration - Performance question: Restore + INDEX

Author

Performance question: Restore + INDEX

Aldor

2005-09-25, 8:23 pm

Hi,

I'm curious how other people do it:

What is faster?

1. CREATE TABLE
2. restore data
3. CREATE INDEX

or

1. CREATE TABLE
2. CREATE INDEX
3. restore data

Thanks,

Aldor

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Michael Fuhr

2005-09-26, 3:23 am

On Mon, Sep 26, 2005 at 01:30:53AM +0100, Aldor wrote:
> What is faster?
>
> 1. CREATE TABLE
> 2. restore data
> 3. CREATE INDEX
>
> or
>
> 1. CREATE TABLE
> 2. CREATE INDEX
> 3. restore data

See "Populating a Database" in the "Performance Tips" chapter of
the documentation:

http://www.postgresql.org/docs/8.0/...e/populate.html

But instead of taking somebody's word for it, you could simply try
it both ways and see which is faster for yourself.

--
Michael Fuhr

---------------------------(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

Chris Browne

2005-09-26, 3:23 am

an@mediaroot.de (Aldor) writes:
> I'm curious how other people do it:
>
> What is faster?
>
> 1. CREATE TABLE
> 2. restore data
> 3. CREATE INDEX
>
> or
>
> 1. CREATE TABLE
> 2. CREATE INDEX
> 3. restore data

Creating the index AFTER loading the data is definitely faster. But
by all means do your own tests; it's pretty easy to do so and satisfy
yourself...
--
(format nil "~S@~S" "cbbrowne" "ntlug.org")
http://cbbrowne.com/info/languages.html
"It worked about as well as sticking a blender in the middle of a lime
plantation and hoping they'll make margaritas out of themselves."
-- Frederick J. Polsky v1.0

Sponsored Links