Thread: n_live_tup number double after migration do PG 10.4
Hi all, After the migration to pg 10 by pg_upgrade and vacuumdb, I’ve seen the number of live tuple double and performance decreaseson few queries. Where do these live tuples coming from ? Thank you
On Sun, Aug 5, 2018 at 5:29 AM, Mai Peng <maily.peng@webedia-group.com> wrote:
Hi all,
After the migration to pg 10 by pg_upgrade and vacuumdb, I’ve seen the number of live tuple double and performance decreases on few queries.
Where do these live tuples coming from ?
Thank you
The value stored in n_liv_tup is the value seen when the last analyze was run on the table. It is not kept up to date on every row change and only changes when an analyze is run again. So perhaps the tables you're referencing hadn't been analyzed for a while before you upgraded?
Keith
Keith <keith@keithf4.com> writes: > On Sun, Aug 5, 2018 at 5:29 AM, Mai Peng <maily.peng@webedia-group.com> > wrote: >> After the migration to pg 10 by pg_upgrade and vacuumdb, I’ve seen the >> number of live tuple double and performance decreases on few queries. > The value stored in n_liv_tup is the value seen when the last analyze was > run on the table. No, you're confusing this with pg_class.reltuples. I think the OP is talking about pg_stat counters such as pg_stat_all_tables.n_live_tup. Those should update after every transaction, although there are various reasons why they might be inaccurate. The OP hasn't given us nearly enough information to speculate usefully on what he's seeing. I'd suggest reading https://wiki.postgresql.org/wiki/Guide_to_reporting_problems regards, tom lane
Hi, The query I’m talking about is SELECT schemaname, relname, sum(n_live_tup) AS n_live_tup, sum(n_dead_tup) AS n_dead_tup FROM pg_stat_user_tables GROUP BY schemaname, relname ORDER BY n_live_tup DESC; > Le 6 août 2018 à 06:24, Tom Lane <tgl@sss.pgh.pa.us> a écrit : > > Keith <keith@keithf4.com> writes: >> On Sun, Aug 5, 2018 at 5:29 AM, Mai Peng <maily.peng@webedia-group.com> >> wrote: >>> After the migration to pg 10 by pg_upgrade and vacuumdb, I’ve seen the >>> number of live tuple double and performance decreases on few queries. > >> The value stored in n_liv_tup is the value seen when the last analyze was >> run on the table. > > No, you're confusing this with pg_class.reltuples. I think the OP is > talking about pg_stat counters such as pg_stat_all_tables.n_live_tup. > Those should update after every transaction, although there are various > reasons why they might be inaccurate. > > The OP hasn't given us nearly enough information to speculate usefully > on what he's seeing. I'd suggest reading > > https://wiki.postgresql.org/wiki/Guide_to_reporting_problems > > regards, tom lane