From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Kristjan Mustkivi <sonicmonkey@gmail.com>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgres chooses slow query plan from time to time
Date: Tue, 14 Sep 2021 14:11:32 +0200
Message-ID: <3a3e11197f5c557cf45e0f338dcfc84eb4020856.camel@cybertec.at> (raw)
In-Reply-To: <CAOQPKasCkSAW6FTt_iCY1Ts8tUgT+mf+jro46QXE50eVAQ6Pvg@mail.gmail.com>
References: <CAOQPKavhdTrgQo8Uss0M+K0Du+_WYmdoGyMpC0hJQkKM+-X25Q@mail.gmail.com>
<31c01752-c4d6-7751-9d25-b1fac8f3a7aa@enterprisedb.com>
<CAOQPKasCkSAW6FTt_iCY1Ts8tUgT+mf+jro46QXE50eVAQ6Pvg@mail.gmail.com>
On Tue, 2021-09-14 at 10:55 +0300, Kristjan Mustkivi wrote:
> 2021-09-14 06:55:33 UTC, pid=12345 db=mydb, usr=myuser, client=ip,
> app=PostgreSQL JDBC Driver, line=55 LOG: duration: 5934.165 ms plan:
> Query Text: SELECT * FROM myschema.mytable pbh WHERE
> pbh.product_code = $1 AND pbh.cage_player_id = $2 AND
> pbh.cage_code = $3 AND balance_type = $4 AND pbh.modified_time <
> $5 ORDER BY pbh.modified_time DESC FETCH FIRST 1 ROWS ONLY
> Limit (cost=0.70..6.27 rows=1 width=66) (actual time=5934.154..5934.155 rows=1 loops=1)
> Buffers: shared hit=7623 read=18217
> -> Index Scan Backward using mytable_idx2 on mytable pbh (cost=0.70..21639.94 rows=3885 width=66) (actual time=5934.153..5934.153 rows=1 loops=1)
> Index Cond: ((cage_code = $3) AND (cage_player_id = $2) AND (modified_time < $5))
If it scanned the index for 6 seconds before finding the first result,
I'd suspect one of the following:
- the index is terribly bloated
- there were lots of deleted rows, and the index entries were marked as "dead"
- something locked the table for a long time
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
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: laurenz.albe@cybertec.at, sonicmonkey@gmail.com, tomas.vondra@enterprisedb.com, pgsql-performance@lists.postgresql.org
Subject: Re: Postgres chooses slow query plan from time to time
In-Reply-To: <3a3e11197f5c557cf45e0f338dcfc84eb4020856.camel@cybertec.at>
* 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