pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedn_live_tup number double after migration do PG 10.4
4+ messages / 3 participants
[nested] [flat]
* n_live_tup number double after migration do PG 10.4
@ 2018-08-05 09:29 Mai Peng <maily.peng@webedia-group.com>
0 siblings, 1 reply; 4+ messages in thread
From: Mai Peng @ 2018-08-05 09:29 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: n_live_tup number double after migration do PG 10.4
@ 2018-08-05 23:51 Keith <keith@keithf4.com>
parent: Mai Peng <maily.peng@webedia-group.com>
0 siblings, 1 reply; 4+ messages in thread
From: Keith @ 2018-08-05 23:51 UTC (permalink / raw)
To: Mai Peng <maily.peng@webedia-group.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: n_live_tup number double after migration do PG 10.4
@ 2018-08-06 04:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Keith <keith@keithf4.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2018-08-06 04:24 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: Mai Peng <maily.peng@webedia-group.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: n_live_tup number double after migration do PG 10.4
@ 2018-08-07 07:38 Mai Peng <maily.peng@webedia-group.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Mai Peng @ 2018-08-07 07:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-08-07 07:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-08-05 09:29 n_live_tup number double after migration do PG 10.4 Mai Peng <maily.peng@webedia-group.com>
2018-08-05 23:51 ` Keith <keith@keithf4.com>
2018-08-06 04:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-08-07 07:38 ` Mai Peng <maily.peng@webedia-group.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox