Re: VACUUM kills Index Scans ?!

Gerald Gutierrez <gutz(at)kalador(dot)com> writes:
> 1) When I create a empty table, and then immediate create an index on a
> column, I can get /index scans/ when searching on that column. But when I
> then run VACUUM, the same search becomes a /sequential scan/.

VACUUM updates the planner's statistics so that it knows the table is
empty (note the change in cost estimates). The default numbers for a
never-yet-vacuumed table (10 disk blocks and 1000 rows, IIRC) just
happen to be large enough to cause an indexscan. Put in a reasonable
amount of data and then repeat the VACUUM, and it'll go back to index
scan.

> 2) If I already have some data in a table and I create an index on a
> column, why doesn't subsequent searches then change from sequential scans
> to index scans?

Again, you haven't got enough data to justify an indexscan. You need at
least several disk blocks worth of data before an indexscan can possibly
save more table I/O than it costs to read the index.

There is an undocumented little factoid here: CREATE INDEX will update
(some of) the planner stats, but only if it finds some data in the
table. CREATE INDEX on an empty table leaves the initial default
numbers alone. This may be contributing to your confusion, but it was
deemed necessary so that the common sequence

CREATE TABLE
CREATE INDEX
load data

wouldn't leave the planner believing the table to be completely empty
(and hence generating abysmally bad plans if you had actually loaded
quite a bit of data). On the other hand, the preferred bulk-load
method is

CREATE TABLE
load data
CREATE INDEX

and this leaves the planner's stats set correctly.

regards, tom lane