Re: Building multiple indexes on one table.

>Von: pgsql-performance-owner(at)postgresql(dot)org [pgsql-performance-owner(at)postgresql(dot)org]" im Auftrag von "Claudio Freire [klaussfreire(at)gmail(dot)com]
>Gesendet: Freitag, 18. Juli 2014 01:21
>An: Chris Ruprecht
>Cc: pgsql-performance(at)postgresql(dot)org
>Betreff: Re: [PERFORM] Building multiple indexes on one table.
>
>On Thu, Jul 17, 2014 at 7:47 PM, Chris Ruprecht <chris(at)cdrbill(dot)com> wrote:
>> Is there any way that I can build multiple indexes on one table without having to scan the table multiple times? For small tables, that's probably not an issue, but if I have a 500 GB table that I need to create 6 indexes on, I don't want to read that table 6 times.
>> Nothing I could find in the manual other than reindex, but that's not helping, since it only rebuilds indexes that are already there and I don't know if that reads the table once or multiple times. If I could create indexes inactive and then run reindex, which then reads the table once, I would have a solution. But that doesn't seem to exist either.
>
>Just build them with separate but concurrent connections, and the
>scans will be synchronized so it will be only one.
>
>Btw, reindex rebuilds one index at a time, so what I do is issue
>separate reindex for each index in parallel, to avoid the repeated
>scans as well.
>
>Just make sure you've got the I/O and CPU capacity for it (you'll be
>writing many indexes at once, so there is a lot of I/O).

Index creation on large tables are mostly CPU bound as long as no swap occurs.
I/O may be an issue when all your indexes are similar; e.g. all on single int4 columns.
in other cases the writes will not all take place concurrently.
To reduce I/O due to swap, you can consider increasing maintenance_work_mem on the connextions/sessionns
that build the indexes.

regards,

Marc Mamin