Re: Please help! Query jumps from 1s -> 4m

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, 2020-05-04 at 20:12 +0100, James Thompson wrote:
> The change is abrupt, on the 10th execution (but I hadn't spotted it was always after the
> same number of executions until your suggestion - thanks for pointing me in that direction).
> 
> I don't see any custom configuration on our end that changes the threshold for this from 5->10.
> Debugging the query call I also see that PgConnection has the prepareThreshold set to 5.
> 
> Additionally, the execution plans for the 10th + following queries look fine, they have the
> same structure as if I run the query manually. It's not that the query plan switches,
> it seems as though the same query plan is just > 200X slower than usual.
> 
> As for the heap fetches -> as far as I can tell, on both occasions the fetches are relatively
> low and shouldn't account for minutes of execution (even if one is lower than the other).
> Looking through one days logs I do find cases with lower heap fetches too, for example as
> below which has 1977 fetches instead of the previous 6940 but took approx the same time:
> ->  Index Only Scan using table1_typea_include_uniqueid_col16_idx on table1 table1alias1  (cost=0.56..17.25 rows=1 width=60) (actual time=56.858..120893.874 rows=67000 loops=1)
>         Index Cond: (col20 = $2005)
>         Filter: (((col3 = $2004) OR (col3 IS NULL)) AND ((col8)::text = ANY ((ARRAY[$1004, ..., $2003])::text[])))
>         Rows Removed by Filter: 2662793
>         Heap Fetches: 1977
>         Buffers: shared hit=84574 read=3522
> 
> Would you agree the statement threshold / heap fetches seems unlikely to be causing this? Any other thoughts?

It does sound suspiciously like custom plans vs. generic plan.

If you are using JDBC, then the cut-off of 10 would make sense:
the JDBC driver uses (server) prepared statements only after the
fifth execution, and the prepared statement will use a generic plan
only after the fifth execution.

It would be good to see the execution plan from the third, seventh
and thirteenth execution.  You could use "auto_explain" for that.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com






[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux