Thread: Using an SMP machine to make multiple indices on the same table
Folks, We have some big tables (1.2 billion records) and indexing is quite time consuming. Since we have this running on dual Athlon box, it would be great to make indices in parallel. On Postgresql 7.1.3, it seems that the table is locked after the first "create index" is started up. Is this right? Is there any way to do this in parallel? --Martin
Martin Weinberg <weinberg@osprey.astro.umass.edu> writes:
> On Postgresql 7.1.3, it seems that the table is locked after the
> first "create index" is started up. Is this right?
AFAIK it's a share lock, which only prohibits modifications to the
table, not reads (nor concurrent index builds). Not sure how you
expect the system to do better than that.
regards, tom lane
Re: Using an SMP machine to make multiple indices on the same table
From
Martijn van Oosterhout
Date:
On Mon, Oct 22, 2001 at 05:38:23PM -0400, Martin Weinberg wrote: > Folks, > > We have some big tables (1.2 billion records) and indexing is quite > time consuming. Since we have this running on dual Athlon box, it > would be great to make indices in parallel. > > On Postgresql 7.1.3, it seems that the table is locked after the > first "create index" is started up. Is this right? Is there any > way to do this in parallel? My question in, would it help. The creation of the index should only be limited by the bandwidth of the drives. I would think that creating two indexes at the same time would simply trash the disk a lot and end up being slower. The answer to your questions however, are yes and no respectivly. HTH, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Magnetism, electricity and motion are like a three-for-two special offer: > if you have two of them, the third one comes free.
Tom,
Yes, I understand locking the table, but empirically, two index
creations will not run simultaneously on the same table. So if
I start (and background) two
psql -c "create index one on mytable . . ." database
psql -c "create index two on mytable . . ." database
commands. The first one starts and the second one waits until the
first is finished (as tracked by "ps avx" or "top").
--Martin
Tom Lane wrote on Mon, 22 Oct 2001 23:09:26 EDT
>Martin Weinberg <weinberg@osprey.astro.umass.edu> writes:
>> On Postgresql 7.1.3, it seems that the table is locked after the
>> first "create index" is started up. Is this right?
>
>AFAIK it's a share lock, which only prohibits modifications to the
>table, not reads (nor concurrent index builds). Not sure how you
>expect the system to do better than that.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>
Tom, I should have forwarded you the ps output; here are the relevant lines: ******************************************************************************* 294 ttyp0 S 0:00 203 108 1991 836 0.0 psql -e -c create index v3_pscat_k_m_idx on v3_pscat(k_m) wsdb 295 ? R 0:27 2170 1425 17122 13252 1.4 postgres: postgres wsdb [local] CREATE 296 ttyp0 S 0:00 203 108 1991 836 0.0 psql -e -c create index v3_pscat_h_m_idx on v3_pscat(h_m) wsdb 297 ? S 0:00 190 1425 11858 2436 0.2 postgres: postgres wsdb [local] CREATE waiting 300 ttyp0 R 0:00 273 55 3016 1384 0.1 ps avx ******************************************************************************* Note the "CREATE waiting" process . . . --Martin Tom Lane wrote on Mon, 22 Oct 2001 23:09:26 EDT >Martin Weinberg <weinberg@osprey.astro.umass.edu> writes: >> On Postgresql 7.1.3, it seems that the table is locked after the >> first "create index" is started up. Is this right? > >AFAIK it's a share lock, which only prohibits modifications to the >table, not reads (nor concurrent index builds). Not sure how you >expect the system to do better than that. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html >