Is stats update during COPY IN really a good idea?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Is stats update during COPY IN really a good idea?
Date: 2001-05-21 16:45:22
Message-ID: 29033.990463522@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

We have a TODO item
* Update reltuples in COPY

I was just about to go do this when I realized that it may not be such
a hot idea after all. The problem is that updating pg_class.reltuples
means that concurrent COPY operations will block each other, because
they want to update the same row in pg_class. You can already see this
happen in CREATE INDEX:

create table foo(f1 int);
begin;
create index fooey on foo(f1);

-- in another psql do

create index fooey2 on foo(f1);

-- second backend blocks until first xact is committed or rolled back.

While this doesn't bother me for CREATE INDEX, it does bother me for
COPY, since people often use COPY to avoid per-tuple INSERT overhead.
It seems pretty likely that this will cause blocking problems for real
applications. I think that may be a bigger problem than the benefit of
not needing a VACUUM (or, now, ANALYZE) to get the stats updated.

regards, tom lane

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Zak McGregor 2001-05-21 16:50:10 Re: Queries across multiple databases (was: SELECT from a table in another database).
Previous Message Richard Huxton 2001-05-21 16:40:54 Re: [GENERAL] Queries across multiple databases  (was: SELECT from a table in another database).