Re: Postgres chooses slow query plan from time to time

On Tue, Sep 14, 2021 at 3:55 AM Kristjan Mustkivi <sonicmonkey(at)gmail(dot)com>
wrote:

> Hello Tomas,
>
> The auto explain analyze caught this:
>
> 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.

Cheers,

Jeff
Cheers,

Jeff