pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Stats on new tables 3+ messages / 3 participants [nested] [flat]
* Stats on new tables @ 1999-10-24 18:55 Michael Richards <miker@scifair.acadiau.ca> 0 siblings, 2 replies; 3+ messages in thread From: Michael Richards @ 1999-10-24 18:55 UTC (permalink / raw) To: pgsql-sql 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? -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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Stats on new tables @ 1999-10-24 20:09 Tom Lane <tgl@sss.pgh.pa.us> parent: Michael Richards <miker@scifair.acadiau.ca> 1 sibling, 0 replies; 3+ messages in thread From: Tom Lane @ 1999-10-24 20:09 UTC (permalink / raw) To: Michael Richards <miker@scifair.acadiau.ca>; +Cc: pgsql-sql Michael Richards <miker@scifair.acadiau.ca> writes: > 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. No, actually the initial default assumption under 6.5 & up is that the table contains 1000 rows. (It *used* to be that relntuples started off zero, but that led the optimizer to pick plans that were only suitable for very small tables, which led to horrible performance if you loaded up a table without doing a vacuum.) The particular example you're looking at involves a default assumption about the selectivity of an '=' WHERE condition as well as a default assumption about the total table size. The 'rows' field of a plan node shows the estimated number of *output* tuples, not the number of tuples that need to be scanned. > 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? A lot easier said than done (consider concurrent transactions some of which may abort). We've discussed making it happen, but personally I don't believe that the bookkeeping costs that would be incurred could possibly be justified. Certainly the optimizer's results wouldn't be materially better with exact tuple counts than with approximate ones. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Stats on new tables @ 1999-10-26 04:20 Bruce Momjian <maillist@candle.pha.pa.us> parent: Michael Richards <miker@scifair.acadiau.ca> 1 sibling, 0 replies; 3+ messages in thread From: Bruce Momjian @ 1999-10-26 04:20 UTC (permalink / raw) To: Michael Richards <miker@scifair.acadiau.ca>; +Cc: pgsql-sql > 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-10-26 04:20 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 1999-10-24 18:55 Stats on new tables Michael Richards <miker@scifair.acadiau.ca> 1999-10-24 20:09 ` Tom Lane <tgl@sss.pgh.pa.us> 1999-10-26 04:20 ` Bruce Momjian <maillist@candle.pha.pa.us>
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