pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gerald Gutierrez <gutz@kalador.com>
Cc: pgsql-sql@postgresql.org
Cc: gutz@colo.kalador.com
Subject: Re: VACUUM kills Index Scans ?!
Date: Thu, 15 Mar 2001 15:21:23 -0500
Message-ID: <15040.984687683@sss.pgh.pa.us> (raw)
In-Reply-To: <5.0.2.1.0.20010315113535.0282b750@kalador.com>
References: <5.0.2.1.0.20010315113535.0282b750@kalador.com>

Gerald Gutierrez <gutz@kalador.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



view thread (6+ messages)  latest in thread

Message-ID: <15040.984687683@sss.pgh.pa.us>
Permalink:  ../15040.984687683@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/15040.984687683@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, gutz@kalador.com, gutz@colo.kalador.com
  Subject: Re: VACUUM kills Index Scans ?!
  In-Reply-To: <15040.984687683@sss.pgh.pa.us>
  References: <5.0.2.1.0.20010315113535.0282b750@kalador.com> <15040.984687683@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox