| From: | Peter Geoghegan <pg(at)bowt(dot)ie> |
|---|---|
| To: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
| Cc: | Scott Rankin <srankin(at)motus(dot)com>, "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: Huge shared hit for small table |
| Date: | 2019-11-04 20:07:27 |
| Message-ID: | CAH2-WzkP8bx5WyL9jk3EW-8+L1ySr963NJHQtduWFihOMnqrgg@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Mon, Nov 4, 2019 at 11:56 AM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
> I think it's because some heap pages are being visited many times, due to the
> index tuples being badly "fragmented". Note, I'm not talking about
> fragmentation of index *pages*, which is what pgstattuple reports (which
> wouldn't have nearly so detrimental effect). I could probably say that the
> index tuples are badly "correlated" with the heap.
But this is a unique index, and Scott indicates that the problem seems
to go away for a while following a REINDEX.
> In PG v12 you can use REINDEX CONCURRENTLY (but beware there's a crash
> affecting its progress reporting, fix to be included in v12.1).
PG v12 will store B-Tree duplicates in heap TID order, so if that's
the problem then upgrading to v12 (and REINDEXing if the upgrade was
performed using pg_upgrade) will fix it for good.
--
Peter Geoghegan
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andres Freund | 2019-11-04 20:17:28 | Re: Huge shared hit for small table |
| Previous Message | Scott Rankin | 2019-11-04 19:56:57 | Re: Huge shared hit for small table |