Received: from candle.pha.pa.us (maillist@s5-03.ppp.op.net [209.152.195.67]) by hub.org (8.9.3/8.9.3) with ESMTP id AAA22599 for ; Tue, 26 Oct 1999 00:24:34 -0400 (EDT) (envelope-from maillist@candle.pha.pa.us) Received: (from maillist@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id AAA18449; Tue, 26 Oct 1999 00:20:41 -0400 (EDT) From: Bruce Momjian Message-Id: <199910260420.AAA18449@candle.pha.pa.us> Subject: Re: [SQL] Stats on new tables In-Reply-To: from Michael Richards at "Oct 24, 1999 03:55:05 pm" To: Michael Richards Date: Tue, 26 Oct 1999 00:20:41 -0400 (EDT) CC: pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL65 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > I think I've found something of interest. > When I create a new table, the plan for it believes that the table > contains 2 rows, not 0. Also, since the engine knows when we run an > insert, and also knows how many rows a delete nukes, wouldn't it be better > to update the stats after every select and delete? We believe the overhead of that would be high vs. the benefits. > > -Michael > > > equipment=> CREATE TABLE xuserid( > equipment-> oldid varchar(12), > equipment-> newid int4, > equipment-> PRIMARY KEY (oldid,newid) > equipment-> ); > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index > 'xuserid_pkey' for table 'xuserid' > CREATE > equipment=> \d xuserid_pkey > Table = xuserid_pkey > +----------------------------------+----------------------------------+-------+ > | Field | Type | > Length| > +----------------------------------+----------------------------------+-------+ > | oldid | varchar() | > 12 | > | newid | int4 | > 4 | > +----------------------------------+----------------------------------+-------+ > equipment=> explain select * from xuserid where newid=859; > NOTICE: QUERY PLAN: > > Seq Scan on xuserid (cost=43.00 rows=2 width=16) > > EXPLAIN > equipment=> vacuum analyze; > VACUUM > equipment=> explain select * from xuserid where newid=859; > NOTICE: QUERY PLAN: > > Seq Scan on xuserid (cost=0.00 rows=0 width=16) > > EXPLAIN > > > ************ > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026