Skip site navigation (1) Skip section navigation (2)

Stats on new tables

From: Michael Richards <miker(at)scifair(dot)acadiau(dot)ca>
To: pgsql-sql(at)postgresql(dot)org
Subject: Stats on new tables
Date: 1999-10-24 18:55:05
Message-ID: Pine.BSF.4.10.9910241553001.46045-100000@scifair.acadiau.ca (view raw or flat)
Thread:
Lists: 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


Responses

pgsql-sql by date

Next:From: Tom LaneDate: 1999-10-24 20:09:30
Subject: Re: [SQL] Stats on new tables
Previous:From: Tom LaneDate: 1999-10-24 17:31:06
Subject: Re: [SQL] Can VACUUM, but cannot do VACUUM ANALYZE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group