From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: pgsql-sql@postgresql.org
Subject: row-attribute in EXPLAIN-output doesn't match count(*)
Date: Thu, 14 Apr 2005 18:38:14 +0200
User-Agent: KMail/1.8
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200504141838.14705.andreak@officenet.no>

Hi everybody, I have table which inly has 298 rows in it, but EXPLAIN ANALY=
ZE=20
shows that PG thinks it has as much as 160057 rows AFAICT from the=20
EXPLAIN-output.

I'm using PG-8.0.0

Here is the schema:

nbeweb=3D> \d onp_web_index;
                                  Table "public.onp_web_index"
   Column    |       Type        |                           Modifiers
=2D------------+-------------------+---------------------------------------=
=2D-----------------------
 id          | integer           | not null default=20
nextval('public.onp_web_index_id_seq'::text)
 starturl_id | integer           | not null
 lang_id     | integer           | not null
 url_host    | character varying | not null
 url_path    | character varying | not null
 title       | character varying | not null
 content     | tsvector          | not null
 plain_text  | character varying | not null
Indexes:
    "onp_web_index_pkey" primary key, btree (id)
    "onp_web_index_url_host_key" unique, btree (url_host, url_path)
    "onp_web_index_idx" gist (content)
=46oreign-key constraints:
    "$2" FOREIGN KEY (lang_id) REFERENCES code(id)
    "$1" FOREIGN KEY (starturl_id) REFERENCES onp_web_index_starturl(id) ON=
=20
DELETE CASCADE

 I've just run a "VACUUM FULL VERBOSE" on the whole database, which gave th=
e=20
following output on the relevant table(onp_web_index):

INFO:  vacuuming "public.onp_web_index"
INFO:  "onp_web_index": found 0 removable, 160057 nonremovable row versions=
 in=20
206940 pages
DETAIL:  159759 dead row versions cannot be removed yet.
Nonremovable row versions range from 188 to 2036 bytes long.
There were 2205045 unused item pointers.
Total free space (including removable row versions) is 1562667708 bytes.
191561 pages are or will become empty, including 0 at the end of the table.
201809 pages containing 1562243144 free bytes are potential move destinatio=
ns.
CPU 4.89s/0.88u sec elapsed 286.44 sec.
INFO:  index "onp_web_index_pkey" now contains 160057 row versions in 4562=
=20
pages
DETAIL:  0 index row versions were removed.
4072 index pages have been deleted, 3429 are currently reusable.
CPU 0.09s/0.03u sec elapsed 4.77 sec.
INFO:  index "onp_web_index_url_host_key" now contains 160057 row versions =
in=20
23508 pages
DETAIL:  0 index row versions were removed.
10589 index pages have been deleted, 9885 are currently reusable.
CPU 1.19s/0.30u sec elapsed 198.82 sec.
INFO:  index "onp_web_index_idx" now contains 160057 row versions in 52050=
=20
pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.43s/0.53u sec elapsed 270.59 sec.
INFO:  "onp_web_index": moved 160057 row versions, truncated 206940 to 1489=
2=20
pages
DETAIL:  CPU 28.25s/615.27u sec elapsed 2458.49 sec.
INFO:  index "onp_web_index_pkey" now contains 160057 row versions in 4858=
=20
pages
DETAIL:  160057 index row versions were removed.
3880 index pages have been deleted, 3237 are currently reusable.
CPU 0.15s/0.41u sec elapsed 7.93 sec.
INFO:  index "onp_web_index_url_host_key" now contains 160057 row versions =
in=20
23508 pages
DETAIL:  160057 index row versions were removed.
10492 index pages have been deleted, 9788 are currently reusable.
CPU 1.07s/1.61u sec elapsed 90.54 sec.
INFO:  index "onp_web_index_idx" now contains 160057 row versions in 55361=
=20
pages
DETAIL:  160057 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.40s/0.92u sec elapsed 286.41 sec.

Here it says that it removed 160057 rows, doesn't it? But still EXPLAIN sho=
ws=20
the output below:

nbeweb=3D> EXPLAIN ANALYZE SELECT count(*) from onp_web_index;
                                                        QUERY PLAN
=2D------------------------------------------------------------------------=
=2D------------------------------------------------
 Aggregate  (cost=3D16892.72..16892.72 rows=3D1 width=3D0) (actual=20
time=3D362.055..362.058 rows=3D1 loops=3D1)
   ->  Seq Scan on onp_web_index  (cost=3D0.00..16492.57 rows=3D160057 widt=
h=3D0)=20
(actual time=3D0.043..360.926 rows=3D298 loops=3D1)
 Total runtime: 380.155 ms
(3 rows)

nbeweb=3D> REINDEX table onp_web_index;
REINDEX
nbeweb=3D> EXPLAIN ANALYZE SELECT count(*) from onp_web_index;
                                                        QUERY PLAN
=2D------------------------------------------------------------------------=
=2D------------------------------------------------
 Aggregate  (cost=3D16892.72..16892.72 rows=3D1 width=3D0) (actual=20
time=3D229.076..229.079 rows=3D1 loops=3D1)
   ->  Seq Scan on onp_web_index  (cost=3D0.00..16492.57 rows=3D160057 widt=
h=3D0)=20
(actual time=3D0.034..227.985 rows=3D298 loops=3D1)
 Total runtime: 229.157 ms
(3 rows)

nbeweb=3D> select count(*) from onp_web_index;
 count
=2D------
   298
(1 row)


Is this normal, shall I just overlook the "rows=3D160057" output from EXPLA=
IN,=20
or is something wrong?
What does the line "DETAIL:  159759 dead row versions cannot be removed yet=
=2E"=20
mean?

Thanks for any info.

=2D-=20
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
=2D-----------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Hoffsveien 17           | know how to do a thing and to watch         |
PO. Box 425 Sk=F8yen      | somebody else doing it wrong, without       |
0213 Oslo               | comment.                                    |
NORWAY                  |                                             |
Phone : +47 22 13 01 00 |                                             |
Direct: +47 22 13 10 03 |                                             |
Mobile: +47 909  56 963 |                                             |
=2D-----------------------+---------------------------------------------+