SELECT count(*) differs from result in pgadmin

From: Tobias Larsen <tobiasl(at)reto(dot)dk>
To: pgsql-general(at)postgresql(dot)org
Subject: SELECT count(*) differs from result in pgadmin
Date: 2013-05-08 14:44:22
Message-ID: CAC+idWxryJM0CTKbCHDxYuewcSYcqXrZptFifJMAe2j5AtEgGA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

This is a small, but weird problem. Completely regular table :
- requesting count in pgadmin shows 3124448 rows
- running SELECT count(*) via the query tool returns 5997620

Why is there a difference? There's nothing remotely remarkable about the
table.

I've run a full database VACUUM just to be certain
I've run VACUUM ANALYZE on that specific table
Database version is 9.1 (x64) on windows

verbose output from vacuum is
----------------------------------------------------------------------------------------------
INFO: vacuuming "public.<tablename>"
INFO: index "<tablename>_pkey" now contains 3124448 row versions in 12233
pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.09s/0.00u sec elapsed 0.09 sec.
INFO: index "<tablename>_character_id" now contains 3124448 row versions
in 14898 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.10s/0.00u sec elapsed 0.10 sec.
INFO: index "<tablename>_index" now contains 3124448 row versions in 14694
pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.03u sec elapsed 0.10 sec.
INFO: index "<tablename>_key_idx" now contains 3124448 row versions in
23154 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.12s/0.04u sec elapsed 0.16 sec.
INFO: "<tablename>": found 0 removable, 0 nonremovable row versions in 0
out of 104149 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.40s/0.07u sec elapsed 0.50 sec.
INFO: analyzing "public.<tablename>"
INFO: "<tablename>": scanned 30000 of 104149 pages, containing 900000 live
rows and 0 dead rows; 30000 rows in sample, 3124454 estimated total rows
INFO: analyzing "public.<tablename>" inheritance tree
INFO: "<tablename>": scanned 15628 of 104149 pages, containing 468840 live
rows and 0 dead rows; 15628 rows in sample, 3124451 estimated total rows
INFO: "archive_<tablename>": scanned 14372 of 95773 pages, containing
431160 live rows and 0 dead rows; 14372 rows in sample, 2873175 estimated
total rows
Query returned successfully with no result in 2611 ms.
----------------------------------------------------------------------------------------------

...So it would seem that PGAdmin is correct, but why am I getting the wrong
number from SELECT Count(*)?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2013-05-08 14:47:12 Re: Does it make sense to break a large query into separate functions?
Previous Message Julian 2013-05-08 14:40:59 Re: Does it make sense to break a large query into separate functions?