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

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: James Thompson <james(at)jthompson(dot)dev>, Justin Pryzby <pryzby(at)telsasoft(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Please help! Query jumps from 1s -> 4m
Date: 2020-05-05 08:08:20
Message-ID: 1949097f3ae6189d111d4e0dba63505db958ffef.camel@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Marc Rechté 2020-05-05 08:11:20 Re: NUMA settings
Previous Message Laurenz Albe 2020-05-05 08:00:02 Re: NUMA settings