Thread: create index updates nrows statistics

a create index updates the statistics in pg_class,
this leads to substantial performance degradation compared to
6.4.2.

If you want to see what I mean simply run the performance test in
our test subdirectory.

I think the create index statement should not update this statistic.
(at least not in the newly created empty table case) 
This behavior would then be in sync with the create table behavior.

Andreas

> a create index updates the statistics in pg_class,
> this leads to substantial performance degradation compared to
> 6.4.2.
> 
> If you want to see what I mean simply run the performance test in
> our test subdirectory.
> 
> I think the create index statement should not update this statistic.
> (at least not in the newly created empty table case) 
> This behavior would then be in sync with the create table behavior.
> 
To fix this I urgently suggest the following patch:<<index.patch>> 
regression passes and is a little faster :-)
performance test: without patch 7min now 22 sec

Andreas

ZEUGSWETTER Andreas IZ5 <Andreas.Zeugswetter@telecom.at> writes:
> a create index updates the statistics in pg_class,
> this leads to substantial performance degradation compared to
> 6.4.2.

Create index did that in 6.4.2 as well --- how could it be making
performance worse?

> I think the create index statement should not update this statistic.
> (at least not in the newly created empty table case) 
> This behavior would then be in sync with the create table behavior.

Hmm, skip the update if size is found to be 0 you mean?  Might be
reasonable ... it would eliminate the problem thatCREATE TABLECREATE INDEXCOPY ...
results in horrible plans compared to doing it in the "right" order.
        regards, tom lane

I had this patch, but was unsure if it was save for 6.5.

Looks like Tom has decided.  Good.


> > a create index updates the statistics in pg_class,
> > this leads to substantial performance degradation compared to
> > 6.4.2.
> > 
> > If you want to see what I mean simply run the performance test in
> > our test subdirectory.
> > 
> > I think the create index statement should not update this statistic.
> > (at least not in the newly created empty table case) 
> > This behavior would then be in sync with the create table behavior.
> > 
> To fix this I urgently suggest the following patch:
>  <<index.patch>> 
> regression passes and is a little faster :-)
> performance test: without patch 7min now 22 sec
> 
> Andreas

[Attachment, skipping...]


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026