Received: from mail2.uniserve.com (mail2.uniserve.com [204.244.156.10]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2FL16x64765 for ; Thu, 15 Mar 2001 16:01:06 -0500 (EST) (envelope-from gutz@kalador.com) Received: from van4d21.dial.uniserve.ca ([204.244.163.84] helo=gutz.kalador.com) by mail2.uniserve.com with esmtp (Exim 3.13 #1) id 14derk-0000Ym-00; Thu, 15 Mar 2001 13:01:01 -0800 Message-Id: <5.0.2.1.0.20010315125737.0282b9c0@kalador.com> X-Sender: gutz@kalador.com X-Mailer: QUALCOMM Windows Eudora Version 5.0.2 Date: Thu, 15 Mar 2001 13:06:22 -0800 To: Tom Lane , pgsql-sql@postgresql.org From: Gerald Gutierrez 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> <5.0.2.1.0.20010315113535.0282b750@kalador.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200103/320 X-Sequence-Number: 1984 >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 ... I understand now; it makes sense. I'll be using a number of tables that are initially very small, perhaps 5 or 10 records. But I expect that the tables will grow very quickly to several tens (or hundreds) of thousands of records. It seems reasonable to me that the table should then be set up to use index scan right from the beginning so that as the table grows the index scan will become more useful. Thus, the correct sequence for me is probably: > CREATE TABLE > CREATE INDEX > load data I also understand that VACUUM and VACUUM ANALYZE takes a significant amount of time and locks the tables that it works on. Does it do locking and unlocking per table as it goes through them (i.e. only lock while it's reading the table) or does it do something else? If the locks are for large amounts of time I'll have to shut down my application to avoid connections from timing out and JDBC exceptions from being thrown. Thanks for your help :)