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.153rows=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