Content-Type: multipart/mixed; boundary="------------ut0y2gfWdSLk8Pc0drvwlXMa"
Message-ID: <e61a37f3-eeeb-21cb-647d-0fc3771f94c9@enterprisedb.com>
Date: Thu, 2 Nov 2023 23:00:00 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.13.0
Subject: Re: BUG #18177: certain queries under certain contexts take multiple
 orders of magnitude longer compared to v10
Content-Language: en-US
To: David Rowley <dgrowleyml@gmail.com>, rleach@princeton.edu,
 pgsql-bugs@lists.postgresql.org
References: <18177-a282c2eaaf791f21@postgresql.org>
 <CAApHDvrRgDpV19MDNZ-0Tou-F2+cTU6ZNPq3rixUGp_kuw6-9Q@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: 
 <CAApHDvrRgDpV19MDNZ-0Tou-F2+cTU6ZNPq3rixUGp_kuw6-9Q@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/e61a37f3-eeeb-21cb-647d-0fc3771f94c9%40enterprisedb.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------ut0y2gfWdSLk8Pc0drvwlXMa
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

On 11/2/23 11:45, David Rowley wrote:
> On Thu, 2 Nov 2023 at 21:56, PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> I cannot claim to understand the bug that is causing this issue, so the best
>> I can do is simply provide the explain output and try to keep from providing
>> confusing details, because this is outside the realm of my expertise:
> 
> 
>> ('                    ->  Index Scan using
>> "DataRepo_peakdata_peak_group_id_4dd87f4a" on "DataRepo_peakdata"
>> (cost=0.25..8.26 rows=1 width=8) (actual time=0.017..7.149 rows=7896
>> loops=1)',)
> 
> Nothing looks particularly bug like so far.  It seems the
> pg_class.reltuples estimate for this relation is way out.
> 
> Has autovacuum gotten to this table recently?
> 
> select * from pg_stat_user_tables where relid = '"DataRepo_peakdata"'::regclass;
> 
> The plan would likely come good if you analyzed that table.  You
> should see if you can figure out why autovacuum hasn't analyzed it.
> 
> For the future, it might be best to post EXPLAIN output as an
> attachment.  The extra formatting makes it difficult to read.
> 

I did look at the django forum, and the plans are there. For convenience
I'm attaching them here too. But there's nothing particularly revealing,
except that it indeed seems to be related to the index scan.

One thing that puzzles me is why it'd behave differently between PG10
and PG13. I'd have expected both releases to do the same thing if there
are no stats, but maybe PG10 does analyze it time for some reason?

Would be interesting to see the pg_class data (reltuples, relpages and
timestamp of last analyze/autoanalyze) for the slow/fast cases.

However, the forum thread also claims this:

    And here is the Django 4.2 and postgres 13 output when running just
    the one test (which is always fast):

Which seems weird. But maybe the table is modified/updated by the tests,
so a single test might seem much less data, or something? Not sure.

However, there's one more interesting bit - the 'weird errors' after
placing VACUUM FULL ANALYZE into test setup/teardown are this:

    psycopg2.errors.DataCorrupted: missing chunk number 0 for toast
    value 2884552 in pg_toast_2619

That seems unrelated to the planning issue, but it definitely seems like
a bug. Interestingly enough we got a report [1] about the same error on
pgsql-hackers a couple days ago. That error affected PG11-16, which
seems to be consistent with this (PG13).


[1]
https://www.postgresql.org/message-id/CALdSSPhmqoN02ciT4UxS6ax0N84NpRwPWm87nKJ_%2B0G-Na8qOQ%40mail.gmail.com


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--------------ut0y2gfWdSLk8Pc0drvwlXMa
Content-Type: text/plain; charset=UTF-8; name="explains.txt"
Content-Disposition: attachment; filename="explains.txt"
Content-Transfer-Encoding: base64

QWdncmVnYXRlICAoY29zdD00NS43Ny4uNDUuNzggcm93cz0xIHdpZHRoPTgpIChhY3R1YWwg
dGltZT0yMDIzMjYuMjEwLi4yMDIzMjYuMjE0IHJvd3M9MSBsb29wcz0xKQogIC0+ICBVbmlx
dWUgIChjb3N0PTAuODguLjQ1Ljc2IHJvd3M9MSB3aWR0aD0xMDUyKSAoYWN0dWFsIHRpbWU9
MTA0OTIuOTYzLi4yMDIzMjYuMjA1IHJvd3M9MSBsb29wcz0xKQogICAgICAgIC0+ICBOZXN0
ZWQgTG9vcCAgKGNvc3Q9MC44OC4uNDUuNzUgcm93cz0xIHdpZHRoPTEwNTIpIChhY3R1YWwg
dGltZT0xMDQ5Mi45NTkuLjIwMjMyNi4xODggcm93cz04IGxvb3BzPTEpCiAgICAgICAgICAg
ICAgSm9pbiBGaWx0ZXI6ICgiRGF0YVJlcG9fcGVha2RhdGEiLmlkID0gIkRhdGFSZXBvX3Bl
YWtkYXRhbGFiZWwiLnBlYWtfZGF0YV9pZCkKICAgICAgICAgICAgICBSb3dzIFJlbW92ZWQg
YnkgSm9pbiBGaWx0ZXI6IDMwNzYKICAgICAgICAgICAgICAtPiAgTmVzdGVkIExvb3AgIChj
b3N0PTAuNjIuLjM3LjQ3IHJvd3M9MSB3aWR0aD04KSAoYWN0dWFsIHRpbWU9MTA0OTIuNDU1
Li4yMDIzMjMuOTM1IHJvd3M9OCBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgIEpvaW4g
RmlsdGVyOiAoIkRhdGFSZXBvX3BlYWtncm91cCIuaWQgPSAiRGF0YVJlcG9fcGVha2RhdGEi
LnBlYWtfZ3JvdXBfaWQpCiAgICAgICAgICAgICAgICAgICAgUm93cyBSZW1vdmVkIGJ5IEpv
aW4gRmlsdGVyOiA3ODg4CiAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNp
bmcgIkRhdGFSZXBvX3BlYWtkYXRhX3BlYWtfZ3JvdXBfaWRfNGRkODdmNGEiIG9uICJEYXRh
UmVwb19wZWFrZGF0YSIgIChjb3N0PTAuMjUuLjguMjYgcm93cz0xIHdpZHRoPTgpIChhY3R1
YWwgdGltZT0wLjAxNy4uNy4xNDkgcm93cz03ODk2IGxvb3BzPTEpCiAgICAgICAgICAgICAg
ICAgICAgLT4gIE5lc3RlZCBMb29wICAoY29zdD0wLjM4Li4yOS4yMCByb3dzPTEgd2lkdGg9
NCkgKGFjdHVhbCB0aW1lPTIuOTQyLi4yNS42MjEgcm93cz0xIGxvb3BzPTc4OTYpCiAgICAg
ICAgICAgICAgICAgICAgICAgICAgSm9pbiBGaWx0ZXI6ICgiRGF0YVJlcG9fcGVha2dyb3Vw
Ii5wZWFrX2Fubm90YXRpb25fZmlsZV9pZCA9ICJEYXRhUmVwb19hcmNoaXZlZmlsZSIuaWQp
CiAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIE5lc3RlZCBMb29wICAoY29zdD0wLjM4
Li4xOC40MyByb3dzPTEgd2lkdGg9OCkgKGFjdHVhbCB0aW1lPTIuOTM1Li4yNS42MTQgcm93
cz0xIGxvb3BzPTc4OTYpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgSm9pbiBG
aWx0ZXI6ICgiRGF0YVJlcG9fbXNydW4iLnNhbXBsZV9pZCA9ICJEYXRhUmVwb19zYW1wbGUi
LmlkKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIFJvd3MgUmVtb3ZlZCBieSBK
b2luIEZpbHRlcjogODMKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgTmVz
dGVkIExvb3AgIChjb3N0PTAuMTIuLjEwLjE1IHJvd3M9MSB3aWR0aD0xMikgKGFjdHVhbCB0
aW1lPTAuMDI4Li4yLjUxMyByb3dzPTg0IGxvb3BzPTc4OTYpCiAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgSm9pbiBGaWx0ZXI6ICgiRGF0YVJlcG9fcGVha2dyb3Vw
Ii5tc3J1bl9pZCA9ICJEYXRhUmVwb19tc3J1biIuaWQpCiAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgUm93cyBSZW1vdmVkIGJ5IEpvaW4gRmlsdGVyOiAzNDg2CiAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNp
bmcgIkRhdGFSZXBvX3BlYWtncm91cF9wZWFrX2Fubm90YXRpb25fZmlsZV9pZF82ZGMyZmMy
NSIgb24gIkRhdGFSZXBvX3BlYWtncm91cCIgIChjb3N0PTAuMTIuLjguMTQgcm93cz0xIHdp
ZHRoPTEyKSAoYWN0dWFsIHRpbWU9MC4wMDYuLjAuNDY0IHJvd3M9ODQgbG9vcHM9Nzg5NikK
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBGaWx0ZXI6ICgo
bmFtZSk6OnRleHQgPSAnc2VyaW5lJzo6dGV4dCkiLCkKICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICBSb3dzIFJlbW92ZWQgYnkgRmlsdGVyOiAxMDA4CiAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIFNlcSBTY2FuIG9uICJE
YXRhUmVwb19tc3J1biIgIChjb3N0PTAuMDAuLjIuMDAgcm93cz0xIHdpZHRoPTgpIChhY3R1
YWwgdGltZT0wLjAxMy4uMC4wMTcgcm93cz00MiBsb29wcz02NjMyNjQpCiAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgIkRhdGFSZXBvX3Nh
bXBsZV9wa2V5IiBvbiAiRGF0YVJlcG9fc2FtcGxlIiAgKGNvc3Q9MC4yNS4uOC4yNyByb3dz
PTEgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTAuMTk0Li4wLjI3NSByb3dzPTEgbG9vcHM9NjYz
MjY0KQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEZpbHRlcjogKChu
YW1lKTo6dGV4dCA9ICd4emw1X3BhbmMnOjp0ZXh0KSIsKQogICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgIFJvd3MgUmVtb3ZlZCBieSBGaWx0ZXI6IDI4MQogICAgICAg
ICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiAiRGF0YVJlcG9fYXJjaGl2ZWZp
bGUiICAoY29zdD0wLjAwLi4xMC43NSByb3dzPTEgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTAu
MDAyLi4wLjAwMiByb3dzPTEgbG9vcHM9Nzg5NikKICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICBGaWx0ZXI6ICgoZmlsZW5hbWUpOjp0ZXh0ID0gJ2FsYWZhc3RlZF9jb3IueGxz
eCc6OnRleHQpIiwpCiAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgIkRhdGFS
ZXBvX3BlYWtkYXRhbGFiZWxfcGVha19kYXRhX2lkX2Y2MTUxZDRhIiBvbiAiRGF0YVJlcG9f
cGVha2RhdGFsYWJlbCIgIChjb3N0PTAuMjUuLjguMjcgcm93cz0xIHdpZHRoPTEyKSAoYWN0
dWFsIHRpbWU9MC4wMDcuLjAuMjE3IHJvd3M9Mzg2IGxvb3BzPTgpCiAgICAgICAgICAgICAg
ICAgICAgRmlsdGVyOiAoKGVsZW1lbnQpOjp0ZXh0ID0gJ0MnOjp0ZXh0KSIsKQogICAgICAg
ICAgICAgICAgICAgIFJvd3MgUmVtb3ZlZCBieSBGaWx0ZXI6IDE4MApQbGFubmluZyBUaW1l
OiAwLjk2MyBtcwpFeGVjdXRpb24gVGltZTogMjAyMzI3LjMzNCBtcwoKCkFnZ3JlZ2F0ZSAg
KGNvc3Q9OTkuMDEuLjk5LjAyIHJvd3M9MSB3aWR0aD04KSAoYWN0dWFsIHRpbWU9MS4zOTku
LjEuNDAzIHJvd3M9MSBsb29wcz0xKQogIC0+ICBVbmlxdWUgIChjb3N0PTk4Ljk5Li45OS4w
MCByb3dzPTEgd2lkdGg9MTA1MikgKGFjdHVhbCB0aW1lPTEuMzkwLi4xLjM5OCByb3dzPTEg
bG9vcHM9MSkKICAgICAgICAtPiAgU29ydCAgKGNvc3Q9OTguOTkuLjk5LjAwIHJvd3M9MSB3
aWR0aD0xMDUyKSAoYWN0dWFsIHRpbWU9MS4zODkuLjEuMzkzIHJvd3M9OCBsb29wcz0xKQog
ICAgICAgICAgICAgIFNvcnQgS2V5OiAiRGF0YVJlcG9fcGVha2dyb3VwIi5pZAogICAgICAg
ICAgICAgIFNvcnQgTWV0aG9kOiBxdWlja3NvcnQgIE1lbW9yeTogMjVrQgogICAgICAgICAg
ICAgIC0+ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9NS4zNi4uOTguOTggcm93cz0xIHdpZHRoPTEw
NTIpIChhY3R1YWwgdGltZT0wLjI2MS4uMS4zNzcgcm93cz04IGxvb3BzPTEpCiAgICAgICAg
ICAgICAgICAgICAgLT4gIE5lc3RlZCBMb29wICAoY29zdD01LjA4Li44MC4xOSByb3dzPTUx
IHdpZHRoPTgpIChhY3R1YWwgdGltZT0wLjE1Mi4uMS4yMzQgcm93cz04IGxvb3BzPTEpCiAg
ICAgICAgICAgICAgICAgICAgICAgICAgLT4gIE5lc3RlZCBMb29wICAoY29zdD0wLjU1Li4y
OC4zNiByb3dzPTEgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTAuMTM4Li4xLjIxNCByb3dzPTEg
bG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgTmVzdGVkIExv
b3AgIChjb3N0PTAuMjkuLjI3LjIwIHJvd3M9MSB3aWR0aD04KSAoYWN0dWFsIHRpbWU9MC4w
NjEuLjAuODczIHJvd3M9ODQgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAtPiAgTmVzdGVkIExvb3AgIChjb3N0PTAuMTQuLjE4LjkyIHJvd3M9MSB3
aWR0aD04KSAoYWN0dWFsIHRpbWU9MC4wNDguLjAuNjY3IHJvd3M9ODQgbG9vcHM9MSkKICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4g
b24gIkRhdGFSZXBvX2FyY2hpdmVmaWxlIiAgKGNvc3Q9MC4wMC4uMTAuNzUgcm93cz0xIHdp
ZHRoPTQpIChhY3R1YWwgdGltZT0wLjAxMS4uMC4wMTIgcm93cz0xIGxvb3BzPTEpCiAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgRmlsdGVyOiAo
KGZpbGVuYW1lKTo6dGV4dCA9ICdhbGFmYXN0ZWRfY29yLnhsc3gnOjp0ZXh0KSIsKQogICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2Fu
IHVzaW5nICJEYXRhUmVwb19wZWFrZ3JvdXBfcGVha19hbm5vdGF0aW9uX2ZpbGVfaWRfNmRj
MmZjMjUiIG9uICJEYXRhUmVwb19wZWFrZ3JvdXAiICAoY29zdD0wLjE0Li44LjE2IHJvd3M9
MSB3aWR0aD0xMikgKGFjdHVhbCB0aW1lPTAuMDM1Li4wLjYzNCByb3dzPTg0IGxvb3BzPTEp
CiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgSW5k
ZXggQ29uZDogKHBlYWtfYW5ub3RhdGlvbl9maWxlX2lkID0gIkRhdGFSZXBvX2FyY2hpdmVm
aWxlIi5pZCkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICBGaWx0ZXI6ICgobmFtZSk6OnRleHQgPSAnc2VyaW5lJzo6dGV4dCkiLCkKICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBSb3dzIFJlbW92
ZWQgYnkgRmlsdGVyOiAxMDA4CiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgIkRhdGFSZXBvX21zcnVuX3BrZXkiIG9uICJEYXRh
UmVwb19tc3J1biIgIChjb3N0PTAuMTQuLjguMTYgcm93cz0xIHdpZHRoPTgpIChhY3R1YWwg
dGltZT0wLjAwMi4uMC4wMDIgcm93cz0xIGxvb3BzPTg0KQogICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6IChpZCA9ICJEYXRhUmVwb19w
ZWFrZ3JvdXAiLm1zcnVuX2lkKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+
ICBJbmRleCBTY2FuIHVzaW5nICJEYXRhUmVwb19zYW1wbGVfcGtleSIgb24gIkRhdGFSZXBv
X3NhbXBsZSIgIChjb3N0PTAuMjcuLjAuNzIgcm93cz0xIHdpZHRoPTQpIChhY3R1YWwgdGlt
ZT0wLjAwNC4uMC4wMDQgcm93cz0wIGxvb3BzPTg0KQogICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6IChpZCA9ICJEYXRhUmVwb19tc3J1biIuc2Ft
cGxlX2lkKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEZpbHRlcjog
KChuYW1lKTo6dGV4dCA9ICd4emw1X3BhbmMnOjp0ZXh0KSIsKQogICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgIFJvd3MgUmVtb3ZlZCBieSBGaWx0ZXI6IDEKICAgICAg
ICAgICAgICAgICAgICAgICAgICAtPiAgQml0bWFwIEhlYXAgU2NhbiBvbiAiRGF0YVJlcG9f
cGVha2RhdGEiICAoY29zdD00LjUyLi41MS41MiByb3dzPTMxIHdpZHRoPTgpIChhY3R1YWwg
dGltZT0wLjAxMC4uMC4wMTIgcm93cz04IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgUmVjaGVjayBDb25kOiAocGVha19ncm91cF9pZCA9ICJEYXRhUmVwb19w
ZWFrZ3JvdXAiLmlkKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEhlYXAgQmxv
Y2tzOiBleGFjdD0xCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEJpdG1h
cCBJbmRleCBTY2FuIG9uICJEYXRhUmVwb19wZWFrZGF0YV9wZWFrX2dyb3VwX2lkXzRkZDg3
ZjRhIiAgKGNvc3Q9MC4wMC4uNC41MSByb3dzPTMxIHdpZHRoPTApIChhY3R1YWwgdGltZT0w
LjAwNS4uMC4wMDUgcm93cz04IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgSW5kZXggQ29uZDogKHBlYWtfZ3JvdXBfaWQgPSAiRGF0YVJlcG9fcGVh
a2dyb3VwIi5pZCkKICAgICAgICAgICAgICAgICAgICAtPiAgSW5kZXggT25seSBTY2FuIHVz
aW5nIHVuaXF1ZV9wZWFrZGF0YSBvbiAiRGF0YVJlcG9fcGVha2RhdGFsYWJlbCIgIChjb3N0
PTAuMjkuLjAuMzcgcm93cz0xIHdpZHRoPTEyKSAoYWN0dWFsIHRpbWU9MC4wMTcuLjAuMDE3
IHJvd3M9MSBsb29wcz04KQogICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6
ICgocGVha19kYXRhX2lkID0gIkRhdGFSZXBvX3BlYWtkYXRhIi5pZCkgQU5EIChlbGVtZW50
ID0gXCdDXCc6OnRleHQpKQogICAgICAgICAgICAgICAgICAgICAgICAgIEhlYXAgRmV0Y2hl
czogOApQbGFubmluZyBUaW1lOiAxLjE5NSBtcwpFeGVjdXRpb24gVGltZTogMS41NjYgbXMK


--------------ut0y2gfWdSLk8Pc0drvwlXMa--