8000 Patch from Andreas: when CREATE TABLE is followed by CREATE INDEX · postgrespro/postgres_cluster@7c8beef · GitHub
[go: up one dir, main page]
Skip to content

Commit 7c8beef

Browse files
tglsfdctglsfdc
committed
Patch from Andreas: when CREATE TABLE is followed by CREATE INDEX
before any tuples are loaded, preserve the default '1000 tuples' table size estimate.
1 parent 8eb18d8 commit 7c8beef

File tree

2 files changed

+47
-31
lines changed

2 files changed

+47
-31
lines changed

‎src/backend/catalog/heap.c

Lines changed: 5 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.85 1999/05/25 16:08:03 momjian Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.86 1999/05/26 22:57:39 tgl Exp $
1111
*
1212
*
1313
* INTERFACE ROUTINES
@@ -674,11 +674,10 @@ AddNewRelationTuple(Relation pg_class_desc,
674674
* enough to discourage the optimizer from using nested-loop plans.
675675
* With this hack, nested-loop plans will be preferred only after
676676
* the table has been proven to be small by VACUUM or CREATE INDEX.
677-
* (NOTE: if user does CREATE TABLE, then CREATE INDEX, then loads
678-
* the table, he still loses until he vacuums, because CREATE INDEX
679-
* will set reltuples to zero. Can't win 'em all. Maintaining the
680-
* stats on-the-fly would solve the problem, but the overhead of that
681-
* would likely cost more than it'd save.)
677+
* Maintaining the stats on-the-fly would solve the problem more cleanly,
678+
* but the overhead of that would likely cost more than it'd save.
679+
* (NOTE: CREATE INDEX inserts the same bogus estimates if it finds the
680+
* relation has 0 rows and pages. See index.c.)
682681
* ----------------
683682
*/
684683
new_rel_reltup->relpages = 10; /* bogus estimates */

‎src/backend/catalog/index.c

Lines changed: 42 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/catalog/index.c,v 1.75 1999/05/25 16:08:06 momjian Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/catalog/index.c,v 1.76 1999/05/26 22:57:39 tgl Exp $
1111
*
1212
*
1313
* INTERFACE ROUTINES
@@ -1291,7 +1291,6 @@ UpdateStats(Oid relid, long reltuples, bool hasindex)
12911291
if (!RelationIsValid(pg_class))
12921292
elog(ERROR, "UpdateStats: could not open RELATION relation");
12931293

1294-
12951294
if (!IsBootstrapProcessingMode())
12961295
{
12971296
tuple = SearchSysCacheTupleCopy(RELOID,
@@ -1320,34 +1319,48 @@ UpdateStats(Oid relid, long reltuples, bool hasindex)
13201319
}
13211320

13221321
/* ----------------
1323-
* update statistics
1322+
* Figure values to insert.
1323+
*
1324+
* If we found zero tuples in the scan, do NOT believe it; instead put
1325+
* a bogus estimate into the statistics fields. Otherwise, the common
1326+
* pattern "CREATE TABLE; CREATE INDEX; insert data" leaves the table
1327+
* with zero size statistics until a VACUUM is done. The optimizer will
1328+
* generate very bad plans if the stats claim the table is empty when
1329+
* it is actually sizable. See also CREATE TABLE in heap.c.
13241330
* ----------------
13251331
*/
13261332
relpages = RelationGetNumberOfBlocks(whichRel);
13271333

1334+
if (reltuples == 0)
1335+
{
1336+
if (relpages == 0)
1337+
{
1338+
/* Bogus defaults for a virgin table, same as heap.c */
1339+
reltuples = 1000;
1340+
relpages = 10;
1341+
}
1342+
else if (whichRel->rd_rel->relkind == RELKIND_INDEX && relpages <= 2)
1343+
{
1344+
/* Empty index, leave bogus defaults in place */
1345+
reltuples = 1000;
1346+
}
1347+
else
1348+
reltuples = relpages * NTUPLES_PER_PAGE(whichRel->rd_rel->relnatts);
1349+
}
1350+
13281351
/*
13291352
* We shouldn't have to do this, but we do... Modify the reldesc in
13301353
* place with the new values so that the cache contains the latest
13311354
* copy.
13321355
*/
1333-
13341356
whichRel->rd_rel->relhasindex = hasindex;
13351357
whichRel->rd_rel->relpages = relpages;
13361358
whichRel->rd_rel->reltuples = reltuples;
13371359

1338-
for (i = 0; i < Natts_pg_class; i++)
1339-
{
1340-
nulls[i] = heap_attisnull(tuple, i + 1) ? 'n' : ' ';
1341-
replace[i] = ' ';
1342-
values[i] = (Datum) NULL;
1343-
}
1344-
1345-
/*
1346-
* If reltuples wasn't supplied take an educated guess.
1360+
/* ----------------
1361+
* Update statistics in pg_class.
1362+
* ----------------
13471363
*/
1348-
if (reltuples == 0)
1349-
reltuples = relpages * NTUPLES_PER_PAGE(whichRel->rd_rel->relnatts);
1350-
13511364
if (IsBootstrapProcessingMode())
13521365
{
13531366

@@ -1363,7 +1376,15 @@ UpdateStats(Oid relid, long reltuples, bool hasindex)
13631376
}
13641377
else
13651378
{
1366-
/* during normal processing, work harder */
1379+
/* During normal processing, must work harder. */
1380+
1381+
for (i = 0; i < Natts_pg_class; i++)
1382+
{
1383+
nulls[i] = heap_attisnull(tuple, i + 1) ? 'n' : ' ';
1384+
replace[i] = ' ';
1385+
values[i] = (Datum) NULL;
1386+
}
1387+
13671388
replace[Anum_pg_class_relpages - 1] = 'r';
13681389
values[Anum_pg_class_relpages - 1] = (Datum) relpages;
13691390
replace[Anum_pg_class_reltuples - 1] = 'r';
@@ -1438,12 +1459,10 @@ DefaultBuild(Relation heapRelation,
14381459
char *nullv;
14391460
long reltuples,
14401461
indtuples;
1441-
14421462
#ifndef OMIT_PARTIAL_INDEX
14431463
ExprContext *econtext;
14441464
TupleTable tupleTable;
14451465
TupleTableSlot *slot;
1446-
14471466
#endif
14481467
Node *predicate;
14491468
Node *oldPred;
@@ -1524,21 +1543,20 @@ DefaultBuild(Relation heapRelation,
15241543
{
15251544
reltuples++;
15261545

1546+
#ifndef OMIT_PARTIAL_INDEX
15271547
/*
15281548
* If oldPred != NULL, this is an EXTEND INDEX command, so skip
15291549
* this tuple if it was already in the existing partial index
15301550
*/
15311551
if (oldPred != NULL)
15321552
{
1533-
#ifndef OMIT_PARTIAL_INDEX
15341553
/* SetSlotContents(slot, heapTuple); */
15351554
slot->val = heapTuple;
15361555
if (ExecQual((List *) oldPred, econtext) == true)
15371556
{
15381557
indtuples++;
15391558
continue;
15401559
}
1541-
#endif /* OMIT_PARTIAL_INDEX */
15421560
}
15431561

15441562
/*
@@ -1547,13 +1565,12 @@ DefaultBuild(Relation heapRelation,
15471565
*/
15481566
if (predicate != NULL)
15491567
{
1550-
#ifndef OMIT_PARTIAL_INDEX
15511568
/* SetSlotContents(slot, heapTuple); */
15521569
slot->val = heapTuple;
15531570
if (ExecQual((List *) predicate, econtext) == false)
15541571
continue;
1555-
#endif /* OMIT_PARTIAL_INDEX */
15561572
}
1573+
#endif /* OMIT_PARTIAL_INDEX */
15571574

15581575
indtuples++;
15591576

@@ -1586,12 +1603,12 @@ DefaultBuild(Relation heapRelation,
15861603

15871604
heap_endscan(scan);
15881605

1606+
#ifndef OMIT_PARTIAL_INDEX
15891607
if (predicate != NULL || oldPred != NULL)
15901608
{
1591-
#ifndef OMIT_PARTIAL_INDEX
15921609
ExecDestroyTupleTable(tupleTable, false);
1593-
#endif /* OMIT_PARTIAL_INDEX */
15941610
}
1611+
#endif /* OMIT_PARTIAL_INDEX */
15951612

15961613
pfree(nullv);
15971614
pfree(datum);

0 commit comments

Comments
 (0)
0