Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2FKLVx57099 for ; Thu, 15 Mar 2001 15:21:31 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f2FKLNt15043; Thu, 15 Mar 2001 15:21:23 -0500 (EST) To: Gerald Gutierrez cc: pgsql-sql@postgresql.org, gutz@colo.kalador.com Subject: Re: VACUUM kills Index Scans ?! In-reply-to: <5.0.2.1.0.20010315113535.0282b750@kalador.com> References: <5.0.2.1.0.20010315113535.0282b750@kalador.com> Comments: In-reply-to Gerald Gutierrez message dated "Thu, 15 Mar 2001 11:42:21 -0800" Date: Thu, 15 Mar 2001 15:21:23 -0500 Message-ID: <15040.984687683@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200103/318 X-Sequence-Number: 1982 Gerald Gutierrez 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