Обсуждение: questions about a table's row estimates
I'm looking at pg_stat_user_tables in 8.4.2, and I'm confused about n_live_tup. Shouldn't that be at least fairly close to(n_tup_ins - n_tup-del)? It doesn't seem to be, but I'm unclear why.
On Feb 5, 2010, at 12:14 PM, Ben Chobot wrote: > I'm looking at pg_stat_user_tables in 8.4.2, and I'm confused about n_live_tup. Shouldn't that be at least fairly closeto (n_tup_ins - n_tup-del)? It doesn't seem to be, but I'm unclear why. Is everybody else unclear as well?
Ben Chobot wrote: > I'm looking at pg_stat_user_tables in 8.4.2, and I'm confused about n_live_tup. Shouldn't that be at least fairly closeto (n_tup_ins - n_tup-del)? It doesn't seem to be, but I'm unclear why. > Insert 2000 tuples. Delete 1000 tuples. vacuum Insert 1000 tuples. These go into the free space the deleted tuples used to be in. analyze n_tup_ins=3000 n_tup_del=1000 n_live_tup=3000 If there's any delete/vacuum/reuse churn here, no reason the believe the insert/delete and live counts will be close at all. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com
Greg Smith <greg@2ndquadrant.com> writes: > Ben Chobot wrote: >> I'm looking at pg_stat_user_tables in 8.4.2, and I'm confused about n_live_tup. Shouldn't that be at least fairly closeto (n_tup_ins - n_tup-del)? It doesn't seem to be, but I'm unclear why. >> > Insert 2000 tuples. > Delete 1000 tuples. > vacuum > Insert 1000 tuples. These go into the free space the deleted tuples used > to be in. > analyze > n_tup_ins=3000 > n_tup_del=1000 > n_live_tup=3000 Huh? regression=# create table foo (f1 int); CREATE TABLE regression=# insert into foo select generate_series(1,2000); INSERT 0 2000 regression=# select n_live_tup,n_tup_ins,n_tup_del from pg_stat_user_tables where relname = 'foo'; n_live_tup | n_tup_ins | n_tup_del ------------+-----------+----------- 2000 | 2000 | 0 (1 row) regression=# delete from foo where f1 > 1000; DELETE 1000 regression=# select n_live_tup,n_tup_ins,n_tup_del from pg_stat_user_tables where relname = 'foo'; n_live_tup | n_tup_ins | n_tup_del ------------+-----------+----------- 1000 | 2000 | 1000 (1 row) regression=# insert into foo select generate_series(2001,3000); INSERT 0 1000 regression=# select n_live_tup,n_tup_ins,n_tup_del from pg_stat_user_tables where relname = 'foo'; n_live_tup | n_tup_ins | n_tup_del ------------+-----------+----------- 2000 | 3000 | 1000 (1 row) regression=# The only easy explanation I can think of for Ben's complaint is if he reset the stats counters sometime during the table's existence. regards, tom lane
On Feb 10, 2010, at 10:28 PM, Greg Smith wrote:
Ben Chobot wrote:I'm looking at pg_stat_user_tables in 8.4.2, and I'm confused about n_live_tup. Shouldn't that be at least fairly close to (n_tup_ins - n_tup-del)? It doesn't seem to be, but I'm unclear why.Insert 2000 tuples.
Delete 1000 tuples.
vacuum
Insert 1000 tuples. These go into the free space the deleted tuples used to be in.
analyze
n_tup_ins=3000
n_tup_del=1000
n_live_tup=3000
Say what? It's not documented terrible well (http://www.postgresql.org/docs/8.4/interactive/monitoring-stats.html is the best I can find) but I thought n_live_tup was basically the number of tuples visible to a new transaction. If my assumption is wrong, that might explain things.
And unfortunately, Tom, we're not resetting stats counters. :(
Ben Chobot <bench@silentmedia.com> writes: > And unfortunately, Tom, we're not resetting stats counters. :( Mph. Well, the other thing that comes to mind is that n_live_tup (and n_dead_tup) is typically updated by ANALYZE, but only to an estimate based on ANALYZE's partial sample of the table. If the sample isn't very representative then it might be far off. How does n_live_tup compare to an actual COUNT(*) count? regards, tom lane