Re: Statistics mismatch between n_live_tup and actual row count

On 12/06/2012 06:13 PM, tim_wilson wrote:
> This drift gets more confusing.
>
> My small table A (60K rows) is not being inserted to (except one or two
> rows) it is getting thousands of updates a minute. Analyze and vacuum on the
> table are running regularly. But sometimes ,every time the vacuum runs the
> reltuples value jumps up. Sometime bloating by 4 times the actualy size of
> the table (have seen 10 times)
>
> The impact of this on the query plans is evident straight away. Joins from
> all small A to large table B (55 Million rows) on the large B's primary key
> start seq scanning.
>
> Some sort of threshold is being hit where the planner believes that the size
> of A is big enough that given we are requesting all of A we might as well
> scan all of B.
>
> Also even though the table has been clustered and even vacuum full'ed the
> relpages value continues to grow.
>
> Any answers?

1) FYI an update is a delete/insert, so you are actually getting
thousands of inserts a minute.
2) As to why the size is growing- Are there open transactions holding
old row versions around?

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com