Re: how to improve the performance of creating index

On 10/19/07, Yinan Li <yinanli(at)cse(dot)ust(dot)hk> wrote:
>
> Hi, all
> I am trying to improve the performance of creating index.
> I've set shared_buffers = 1024MB
> Effective_cache_size = 1024MB
> Work_mem = 1GB
> Maintenance_work_mem=512MB
> (I'm sure that the server process has received the SIGHUP signal)
>
> However, when create index, I found that the memory used by Postgres is only
> 50MB. And it is very slow. How to make it faster?

What, exactly is the create index statement? I assume that if there's
only two columns then at worst it's a two part index (i.e. column1,
column2) which can get rather large.

From what you said in your reply to Heikki, these settings are WAY too
high. You shouldn't try to allocate more memory than your machine has
to the database. with shared buffers at 1G, work mem at 1G and maint
workmem at 0.5 gig you could use all your memory plus 0.5G on a single
query.

Set them at something more sane. shared_buffers at 128M to 512M,
work_mem at 64M, and maintenance_work_mem at 128M to 512M (max)

What do top, vmstat, and iostat have to say about your machine while
the create query is going on?

If you want it to go faster, it depends on what's holding you back.
If you're CPUs are maxed out, then you might need more CPU. If your
I/O is maxed, then you might need more I/O bandwidth, and if neither
seems maxed out, but you've got a lot of time spend waiting / idle,
then you might need faster / more memory.

Until we / you know what's slow, we don't know what to change to make
your create index run faster.

On my reporting server at work, where we have 2 Gigs ram, and
70Million or so rows using about 45Gigs (tables and indexes) I can
create a new index in about 10-15 minutes. So your machine sounds
kinda slow. The primary difference between my machine and yours is
that I have a 4 disk RAID-10 software array. Even if that made my
machine twice as fast as yours, that doesn't explain the really slow
performance of yours.