Re: why postgresql over other RDBMS

I believe you have made quite a good description of what happens.

Index-building isn't very CPU-intensive for integers (geometrics and
tsearch is another matter, of course), so building all indexes of a large
table in one pass is a possibility that works now, provided you issue all
create index commands in concurrent connections at roughly the same time.

I don't think pgrestore does this, though.

>> So it works right now, except it doesn't have (yet) the infrastructure
>> to
>> keep the scans synchronized
>
> Perhaps you only got one read of the table because the process is
> essentially self-synchronizing. Whenever one process "gets ahead", it
> requires a disk read for the next page, which causes it to block for a
> relatively long time, during which time the other two processes either
> proceed reading rows from cache, or come to the end of the cache and
> block
> waiting for the same page to be read from disk. Obviously not a
> guarantee,
> as indexing a relatively more expensive type COULD cause one process to
> get
> multiple pages behind, and memory usage by other processes COULD cause
> intervening pages to be flushed from cache. But I have a suspicion that
> the
> experiment was not just a happy fluke, that there will be a strong
> tendency
> for multiple simultaneous index operations to stay sufficiently closely
> synch'd that the table will only be read from disk once. (Especially when
> such operations are done while the database is otherwise quiescent, as
> would
> be the typical case during a restore.)
>