Date: Thu, 20 Sep 2018 21:29:53 -0500
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?
Message-ID: <20180921022953.GE2471@telsasoft.com>
References: 
 <CAMqTPq=aFwfiqv=P9EDs+UxeOgfe03kyQ0uvtUHvSxvfSvYR+w@mail.gmail.com>
 <20180921004232.GC2471@telsasoft.com>
 <CAMqTPqkGhZvB=EnSOHTVA0XV9uy3iyzfSKDESpbiqjzOpLpvfA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAMqTPqkGhZvB=EnSOHTVA0XV9uy3iyzfSKDESpbiqjzOpLpvfA@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

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