Unanswered: variation of n_live_tup value on the table having 1:1 relationship
I need a clarification on 'n_live_tup' column available in the predefined table 'pg_stat_user_tables'.
I have two tables having one to one relationship, i.e., each row in TableA has its corresponding row in the TableB . Upon inserting or updating a row in Table A , trigger defined on Table A will make sure of inserting / updating a row in Table B.
I also confirm that i have same number of rows in both the tables.
But when ran the below SQL statement i could see a huge difference of 'n_live_tup' value.
select relname , n_live_tup,n_dead_tup from pg_stat_user_tables ;
n_live_tup - Table A(parent table) shows '159037' , TableB is showing '1428582'
the value of n_dead_tup is zero for both the tables.
Can some one help me out by explaining why this is happening. Am i missing any basic theory here ( am new to postgres).
The statistics recorded in the pg_stat_xxx tables are just that: statistics. They aren't 100% accurate, they are (usually) "good enough" in order to choose the best execution plan.
However if both tables really have the same number of rows than the difference is too big to be caused by different samples. Maybe your statistics weren't updated on that table.
Check if running analyze tablea changes anything. If not then you might need to increase the statistics target for that table.
If Postgres can't keep the statistics up to date, you might have transactions that are not ended correctly (through commit or rollback) which is the most common cause for this kind of thing. Check for sessions with the state "idle in transaction".
Also: what is the exact Postgres version you are using?