Skip to content

Commit 208ec47

Browse files
tglsfdctglsfdc
committed
Tweak planner to use a minimum size estimate of 10 pages for a
never-yet-vacuumed relation. This restores the pre-8.0 behavior of avoiding seqscans during initial data loading, while still allowing reasonable optimization after a table has been vacuumed. Several regression test cases revert to 7.4-like behavior, which is probably a good sign. Per gripes from Keith Browne and others.
1 parent 7604267 commit 208ec47

File tree

4 files changed

+71
-46
lines changed

4 files changed

+71
-46
lines changed

‎src/backend/optimizer/util/plancat.c

Lines changed: 27 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.100 2004/12/31 22:00:23 pgsql Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.101 2005/03/24 19:14:49 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -234,7 +234,32 @@ estimate_rel_size(Relation rel, int32 *attr_widths,
234234
case RELKIND_INDEX:
235235
case RELKIND_TOASTVALUE:
236236
/* it has storage, ok to call the smgr */
237-
*pages = curpages = RelationGetNumberOfBlocks(rel);
237+
curpages = RelationGetNumberOfBlocks(rel);
238+
239+
/*
240+
* HACK: if the relation has never yet been vacuumed, use a
241+
* minimum estimate of 10 pages. This emulates a desirable
242+
* aspect of pre-8.0 behavior, which is that we wouldn't assume
243+
* a newly created relation is really small, which saves us from
244+
* making really bad plans during initial data loading. (The
245+
* plans are not wrong when they are made, but if they are cached
246+
* and used again after the table has grown a lot, they are bad.)
247+
* It would be better to force replanning if the table size has
248+
* changed a lot since the plan was made ... but we don't
249+
* currently have any infrastructure for redoing cached plans at
250+
* all, so we have to kluge things here instead.
251+
*
252+
* We approximate "never vacuumed" by "has relpages = 0", which
253+
* means this will also fire on genuinely empty relations. Not
254+
* great, but fortunately that's a seldom-seen case in the real
255+
* world, and it shouldn't degrade the quality of the plan too
256+
* much anyway to err in this direction.
257+
*/
258+
if (curpages < 10 && rel->rd_rel->relpages == 0)
259+
curpages = 10;
260+
261+
/* report estimated # pages */
262+
*pages = curpages;
238263
/* quick exit if rel is clearly empty */
239264
if (curpages == 0)
240265
{

‎src/test/regress/expected/join.out

Lines changed: 20 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -214,13 +214,13 @@ SELECT '' AS "xxx", t1.a, t2.e
214214
WHERE t1.a = t2.d;
215215
xxx | a | e
216216
-----+---+----
217+
| 0 |
217218
| 1 | -1
218219
| 2 | 2
219-
| 3 | -3
220220
| 2 | 4
221+
| 3 | -3
221222
| 5 | -5
222223
| 5 | -5
223-
| 0 |
224224
(7 rows)
225225

226226
--
@@ -1567,27 +1567,27 @@ SELECT '' AS "xxx", *
15671567
FROM J1_TBL INNER JOIN J2_TBL USING (i);
15681568
xxx | i | j | t | k
15691569
-----+---+---+-------+----
1570+
| 0 | | zero |
15701571
| 1 | 4 | one | -1
1571-
| 2 | 3 | two | 4
15721572
| 2 | 3 | two | 2
1573+
| 2 | 3 | two | 4
15731574
| 3 | 2 | three | -3
15741575
| 5 | 0 | five | -5
15751576
| 5 | 0 | five | -5
1576-
| 0 | | zero |
15771577
(7 rows)
15781578

15791579
-- Same as above, slightly different syntax
15801580
SELECT '' AS "xxx", *
15811581
FROM J1_TBL JOIN J2_TBL USING (i);
15821582
xxx | i | j | t | k
15831583
-----+---+---+-------+----
1584+
| 0 | | zero |
15841585
| 1 | 4 | one | -1
1585-
| 2 | 3 | two | 4
15861586
| 2 | 3 | two | 2
1587+
| 2 | 3 | two | 4
15871588
| 3 | 2 | three | -3
15881589
| 5 | 0 | five | -5
15891590
| 5 | 0 | five | -5
1590-
| 0 | | zero |
15911591
(7 rows)
15921592

15931593
SELECT '' AS "xxx", *
@@ -1623,35 +1623,35 @@ SELECT '' AS "xxx", *
16231623
FROM J1_TBL NATURAL JOIN J2_TBL;
16241624
xxx | i | j | t | k
16251625
-----+---+---+-------+----
1626+
| 0 | | zero |
16261627
| 1 | 4 | one | -1
1627-
| 2 | 3 | two | 4
16281628
| 2 | 3 | two | 2
1629+
| 2 | 3 | two | 4
16291630
| 3 | 2 | three | -3
16301631
| 5 | 0 | five | -5
16311632
| 5 | 0 | five | -5
1632-
| 0 | | zero |
16331633
(7 rows)
16341634

16351635
SELECT '' AS "xxx", *
16361636
FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d);
16371637
xxx | a | b | c | d
16381638
-----+---+---+-------+----
1639+
| 0 | | zero |
16391640
| 1 | 4 | one | -1
1640-
| 2 | 3 | two | 4
16411641
| 2 | 3 | two | 2
1642+
| 2 | 3 | two | 4
16421643
| 3 | 2 | three | -3
16431644
| 5 | 0 | five | -5
16441645
| 5 | 0 | five | -5
1645-
| 0 | | zero |
16461646
(7 rows)
16471647

16481648
SELECT '' AS "xxx", *
16491649
FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
16501650
xxx | a | b | c | d
16511651
-----+---+---+------+---
1652+
| 0 | | zero |
16521653
| 2 | 3 | two | 2
16531654
| 4 | 1 | four | 2
1654-
| 0 | | zero |
16551655
(3 rows)
16561656

16571657
-- mismatch number of columns
@@ -1660,13 +1660,13 @@ SELECT '' AS "xxx", *
16601660
FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a);
16611661
xxx | a | b | t | k
16621662
-----+---+---+-------+----
1663+
| 0 | | zero |
16631664
| 1 | 4 | one | -1
1664-
| 2 | 3 | two | 4
16651665
| 2 | 3 | two | 2
1666+
| 2 | 3 | two | 4
16661667
| 3 | 2 | three | -3
16671668
| 5 | 0 | five | -5
16681669
| 5 | 0 | five | -5
1669-
| 0 | | zero |
16701670
(7 rows)
16711671

16721672
--
@@ -1676,22 +1676,22 @@ SELECT '' AS "xxx", *
16761676
FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i);
16771677
xxx | i | j | t | i | k
16781678
-----+---+---+-------+---+----
1679+
| 0 | | zero | 0 |
16791680
| 1 | 4 | one | 1 | -1
1680-
| 2 | 3 | two | 2 | 4
16811681
| 2 | 3 | two | 2 | 2
1682+
| 2 | 3 | two | 2 | 4
16821683
| 3 | 2 | three | 3 | -3
16831684
| 5 | 0 | five | 5 | -5
16841685
| 5 | 0 | five | 5 | -5
1685-
| 0 | | zero | 0 |
16861686
(7 rows)
16871687

16881688
SELECT '' AS "xxx", *
16891689
FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k);
16901690
xxx | i | j | t | i | k
16911691
-----+---+---+------+---+---
1692+
| 0 | | zero | | 0
16921693
| 2 | 3 | two | 2 | 2
16931694
| 4 | 1 | four | 2 | 4
1694-
| 0 | | zero | | 0
16951695
(3 rows)
16961696

16971697
--
@@ -1760,13 +1760,13 @@ SELECT '' AS "xxx", *
17601760
FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);
17611761
xxx | i | j | t | k
17621762
-----+---+---+-------+----
1763+
| 0 | | zero |
17631764
| 1 | 4 | one | -1
17641765
| 2 | 3 | two | 2
1765-
| 3 | 2 | three | -3
17661766
| 2 | 3 | two | 4
1767+
| 3 | 2 | three | -3
17671768
| 5 | 0 | five | -5
17681769
| 5 | 0 | five | -5
1769-
| 0 | | zero |
17701770
| | | |
17711771
| | | | 0
17721772
(9 rows)
@@ -1775,13 +1775,13 @@ SELECT '' AS "xxx", *
17751775
FROM J1_TBL RIGHT JOIN J2_TBL USING (i);
17761776
xxx | i | j | t | k
17771777
-----+---+---+-------+----
1778+
| 0 | | zero |
17781779
| 1 | 4 | one | -1
17791780
| 2 | 3 | two | 2
1780-
| 3 | 2 | three | -3
17811781
| 2 | 3 | two | 4
1782+
| 3 | 2 | three | -3
17821783
| 5 | 0 | five | -5
17831784
| 5 | 0 | five | -5
1784-
| 0 | | zero |
17851785
| | | |
17861786
| | | | 0
17871787
(9 rows)

0 commit comments

Comments
 (0)