Re: SELECT count(*) differs from result in pgadmin - Mailing list pgsql-general

Tobias Larsen <tobiasl@reto.dk> writes:

> Oops, got it. Count(*) includes the rowcount of tables that inherit
> from the table queried? I wasn't counting on that.

To prevent inheriting tables from being scanned as well, run your
query as;

select  count(*) from  ONLY footable;



> On Wed, May 8, 2013 at 4:44 PM, Tobias Larsen <tobiasl@reto.dk> wrote:
>
>     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(*)?
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800