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))
So it expected to get 3885 rows, but got just 1. So this is the statistics issue, right?
That would be true if there were no LIMIT. But with the LIMIT, all this means is that it stopped actually scanning after it found one row, but it estimates that if it didn't stop it would have found 3885. So it is not very informative. But the above plan appears incomplete, there should be a line for "Rows Removed by Filter", and I think that that is what we really want to see in this case.
