From: Justin Pryzby <pryzby@telsasoft.com>
To: Vladimir Ryabtsev <greatvovan@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Why could different data in a table be processed with different performance?
Date: Thu, 20 Sep 2018 21:29:53 -0500
Message-ID: <20180921022953.GE2471@telsasoft.com> (raw)
In-Reply-To: <CAMqTPqkGhZvB=EnSOHTVA0XV9uy3iyzfSKDESpbiqjzOpLpvfA@mail.gmail.com>
References: <CAMqTPq=aFwfiqv=P9EDs+UxeOgfe03kyQ0uvtUHvSxvfSvYR+w@mail.gmail.com>
<20180921004232.GC2471@telsasoft.com>
<CAMqTPqkGhZvB=EnSOHTVA0XV9uy3iyzfSKDESpbiqjzOpLpvfA@mail.gmail.com>
Sorry, dropped -performance.
>>>> Has the table been reindexed (or pg_repack'ed) since loading (or vacuumed
>>>> for that matter) ?
>>> Not sure what you mean... We created indexes on some fields (on
>> I mean REINDEX INDEX articles_pkey;
>> Or (from "contrib"): /usr/pgsql-10/bin/pg_repack -i articles_pkey
>I never did it... Do you recommend to try it? Which variant is preferable?
REINDEX is likely to block access to the table [0], and pg_repack is "online"
(except for briefly acquiring an exclusive lock).
[0] https://www.postgresql.org/docs/10/static/sql-reindex.html>>>> You can see the index scan itself takes an additional 11sec, the "heap"
>>>> portion takes the remaining, additional 14sec (33s-12s-7s).
>>> Sorry, I see 33 s total and 12 s for index, where do you see 7 s?
>> 6625 ms (for short query).
>> So the heap component of the long query is 14512 ms slower.
> Yes, I see, thanks.
> So reindex can help only with index component? What should I do for heap?
> May be reindex the corresponding toast table?
I think reindex will improve the heap access..and maybe the index access too.
I don't see why it would be bloated without UPDATE/DELETE, but you could check
to see if its size changes significantly after reindex.
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, greatvovan@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Why could different data in a table be processed with different performance?
In-Reply-To: <20180921022953.GE2471@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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