[BUGS] BUG #7853: Incorrect statistics in table with many dead rows.
The following bug has been logged on the website: Bug reference: 7853 Logged by: James Skaggs Email address: jim...@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 3 of 2123642 pages, containing 184517 live rows and 2115512 dead rows; 3 rows in sample, 158702435 estimated total rows Here are the table statistics. Sequential Scans81853 Sequential Tuples Read 578848425234 Index Scans 1976513672 Index Tuples Fetched2183339860 Tuples Inserted 65122575 Tuples Updated 308883671 Tuples Deleted 51238760 Tuples HOT Updated 2242897 Live Tuples 163981972 Dead Tuples 7056493 Heap Blocks Read43483331819 Heap Blocks Hit 43121456487 Index Blocks Read 134539277 Index Blocks Hit13606451182 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 Analyze2013-02-04 14:22:27.848547-07 Last Autoanalyze2013-02-01 17:37:29.83-07 Table Size 17 GB Toast Table Sizenone Indexes Size34 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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7853: Incorrect statistics in table with many dead rows.
So, I have some observations. Is this what you are seeing as well? So when we CLUSTER a table heavily-updated table: CLUSTER does appear to reset *n_dead_tup*, *n_tup_ins*, *n_tup_del*, *n_tup_hot_upd*, but NOT *n_live_tup* pg_stat_reset() truly clears out all the statistics counters. I tried this because *n_live_tup* is not correct. A subsequent ANALYZE will update *n_dead_tup* and *n_live_tup* to some values that could not possibly be based on the newly CLUSTERed table So, how to get correct statistics for a heavily updated table? In my experience, we only need to get the exponent correct, but we're not even getting that. BTW, I've upgraded to 8.4.15. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-7853-Incorrect-statistics-in-table-with-many-dead-rows-tp5743845p5746602.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7853: Incorrect statistics in table with many dead rows.
BTW "jimbob" and "James.R.Skaggs" are the same person. I just didn't want to use my "work" email for this -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-7853-Incorrect-statistics-in-table-with-many-dead-rows-tp5743845p5747000.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
