row-attribute in EXPLAIN-output doesn't match count(*)

From: Andreas Joseph Krogh <andreak(at)officenet(dot)no>
To: pgsql-sql(at)postgresql(dot)org
Subject: row-attribute in EXPLAIN-output doesn't match count(*)
Date: 2005-04-14 16:38:14
Message-ID: 200504141838.14705.andreak@officenet.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

I'm using PG-8.0.0

Here is the schema:

nbeweb=> \d onp_web_index;
Table "public.onp_web_index"
Column | Type | Modifiers
-------------+-------------------+---------------------------------------------------------------
id | integer | not null default
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)
Foreign-key constraints:
"$2" FOREIGN KEY (lang_id) REFERENCES code(id)
"$1" FOREIGN KEY (starturl_id) REFERENCES onp_web_index_starturl(id) ON
DELETE CASCADE

I've just run a "VACUUM FULL VERBOSE" on the whole database, which gave the
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
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 destinations.
CPU 4.89s/0.88u sec elapsed 286.44 sec.
INFO: index "onp_web_index_pkey" now contains 160057 row versions in 4562
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
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
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 14892
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
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
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
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 shows
the output below:

nbeweb=> EXPLAIN ANALYZE SELECT count(*) from onp_web_index;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=16892.72..16892.72 rows=1 width=0) (actual
time=362.055..362.058 rows=1 loops=1)
-> Seq Scan on onp_web_index (cost=0.00..16492.57 rows=160057 width=0)
(actual time=0.043..360.926 rows=298 loops=1)
Total runtime: 380.155 ms
(3 rows)

nbeweb=> REINDEX table onp_web_index;
REINDEX
nbeweb=> EXPLAIN ANALYZE SELECT count(*) from onp_web_index;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=16892.72..16892.72 rows=1 width=0) (actual
time=229.076..229.079 rows=1 loops=1)
-> Seq Scan on onp_web_index (cost=0.00..16492.57 rows=160057 width=0)
(actual time=0.034..227.985 rows=298 loops=1)
Total runtime: 229.157 ms
(3 rows)

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

Is this normal, shall I just overlook the "rows=160057" output from EXPLAIN,
or is something wrong?
What does the line "DETAIL: 159759 dead row versions cannot be removed yet."
mean?

Thanks for any info.

--
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
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øyen | 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 | |
------------------------+---------------------------------------------+

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message PFC 2005-04-14 17:08:53 Re: Very low performance on table with only 298 rows
Previous Message Dinesh Pandey 2005-04-14 15:03:45 Re: Prepared query ?