From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Vladimir Ryabtsev <greatvovan(at)gmail(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Why could different data in a table be processed with different performance? |
Date: | 2018-09-21 02:29:53 |
Message-ID: | 20180921022953.GE2471@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2018-09-21 03:17:26 | Re: Why could different data in a table be processed with different performance? |
Previous Message | Vladimir Ryabtsev | 2018-09-21 01:54:00 | Re: Why could different data in a table be processed with different performance? |