Thread: BUG #5887: EXPLAIN returns more than 1000 rows when table is empty

The following bug has been logged online:

Bug reference:      5887
Logged by:          Nick Baryshnikov
Email address:      Warstone@list.ru
PostgreSQL version: 8.4.7
Operating system:   Ubuntu 10.10
Description:        EXPLAIN returns more than 1000 rows when table is empty
Details:

CREATE TABLE test (
    id bigserial NOT NULL PRIMARY KEY
);
VACUUM FULL ANALYZE test;
EXPLAIN ANALYZE SELECT * FROM test;
INSERT INTO test VALUES (DEFAULT);
VACUUM FULL ANALYZE test;
EXPLAIN ANALYZE SELECT * FROM test;

1-st EXPLAIN give me:
"Seq Scan on test  (cost=0.00..31.40 rows=2140 width=8) (actual
time=0.003..0.003 rows=0 loops=1)"
"Total runtime: 0.040 ms"

2-nd:
"Seq Scan on test  (cost=0.00..1.01 rows=1 width=8) (actual
time=0.004..0.005 rows=1 loops=1)"
"Total runtime: 0.025 ms"

VACUUM FULL ANALYZE didn't works when there is 0 records in the table?

"Nick Baryshnikov" <Warstone@list.ru> writes:
> Description:        EXPLAIN returns more than 1000 rows when table is empty

This is not a bug; it's intentional behavior.  As long as the recorded
size of the table is zero, the planner assumes that information is
probably obsolete.

            regards, tom lane

Understood. Can you show me where it is written in the documentation?
(I just want to know is, whether it is in the documentation and if not, probably should add this case)

Tue, 15 Feb 2011 14:52:17 -0500 письмо от Tom Lane <tgl@sss.pgh.pa.us>:

> "Nick Baryshnikov" <Warstone@list.ru> writes:
> > Description:        EXPLAIN returns more than 1000 rows when table is empty
>
> This is not a bug; it's intentional behavior.  As long as the recorded
> size of the table is zero, the planner assumes that information is
> probably obsolete.
>
> regards, tom lane

On Tue, Feb 15, 2011 at 3:59 PM, Warstone@list.ru <warstone@list.ru> wrote:
> Understood. Can you show me where it is written in the documentation?
> (I just want to know is, whether it is in the documentation and if not, probably should add this case)

This is a pretty frequently asked question, so it might be worth
documenting.  But I'm not sure exactly where in the documentation it
should go.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company