Message-ID: <3a3e11197f5c557cf45e0f338dcfc84eb4020856.camel@cybertec.at>
Subject: Re: Postgres chooses slow query plan from time to time
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Kristjan Mustkivi <sonicmonkey@gmail.com>, Tomas Vondra
	 <tomas.vondra@enterprisedb.com>
Cc: pgsql-performance@lists.postgresql.org
Date: Tue, 14 Sep 2021 14:11:32 +0200
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>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.40.4 (3.40.4-1.fc34) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/3a3e11197f5c557cf45e0f338dcfc84eb4020856.camel%40cybertec.at>
Precedence: bulk

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