The following bug has been logged on the website:
Bug reference: 7853
Logged by: James Skaggs
Email address: jimbob@seagate.com
PostgreSQL version: 8.4.14
Operating system: RHEL6
Description: =
After "analyze verbose", the table shows 158 million rows. A select count(1)
yields 13.8 million rows.
INFO: analyzing "public.stream_file"
INFO: "stream_file": scanned 30000 of 2123642 pages, containing 184517 live
rows and 2115512 dead rows; 30000 rows in sample, 158702435 estimated total
rows
Here are the table statistics.
Sequential Scans 81853 =
Sequential Tuples Read 578848425234 =
Index Scans 1976513672 =
Index Tuples Fetched 2183339860 =
Tuples Inserted 65122575 =
Tuples Updated 308883671 =
Tuples Deleted 51238760 =
Tuples HOT Updated 2242897 =
Live Tuples 163981972 =
Dead Tuples 7056493 =
Heap Blocks Read 43483331819 =
Heap Blocks Hit 43121456487 =
Index Blocks Read 134539277 =
Index Blocks Hit 13606451182 =
Toast Blocks Read =
Toast Blocks Hit =
Toast Index Blocks Read =
Toast Index Blocks Hit =
Last Vacuum 2013-02-04 10:06:44.058743-07 =
Last Autovacuum 2013-02-04 16:11:34.289823-07 =
Last Analyze 2013-02-04 14:22:27.848547-07 =
Last Autoanalyze 2013-02-01 17:37:29.855553-07 =
Table Size 17 GB =
Toast Table Size none =
Indexes Size 34 GB
Query returned successfully with no result in 4094 ms.
Bad statistics led to a bad plan. We will cluster the table today to see if
that fixes it, but I think statistics should be correct, regardless of the
state of a table. BTW, Coverity product requries 8.x, and we'll upgrade to
8.4.15 today. Didn't see anything about better statistics in the 8.4.15
changelog.