Postgres chooses slow query plan from time to time
Dear community, I have a query that most of the time gets executed in a few milliseconds yet occasionally takes ~20+ seconds. The difference, as far as I am able to tell, comes whether it uses the table Primary Key (fast) or an additional index with smaller size. The table in question is INSERT ONLY - no updates or deletes done there. Pg 11.12, total OS mem 124G shared_buffers: 31GB work_mem: 27MB effective_cache_size: 93GB The query: 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; \d myschema.mytable Table "myschema.mytable" Column │Type │ Collation │ Nullable │ Default ┼─┼───┼──┼─ cage_code │ integer │ │ not null │ cage_player_id │ bigint │ │ not null │ product_code │ character varying(30) │ │ not null │ balance_type │ character varying(30) │ │ not null │ version│ bigint │ │ not null │ modified_time │ timestamp(3) with time zone │ │ not null │ amount │ numeric(38,8) │ │ not null │ change │ numeric(38,8) │ │ not null │ transaction_id │ bigint │ │ not null │ Indexes: "mytable_pk" PRIMARY KEY, btree (cage_code, cage_player_id, product_code, balance_type, version) "mytable_idx1" btree (modified_time) "mytable_idx2" btree (cage_code, cage_player_id, modified_time) SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE relname='mytable'; ─[ RECORD 1 ]──┬─── relname│ mytable relpages │ 18630554 reltuples │ 1.45045e+09 relallvisible │ 18629741 relkind│ r relnatts │ 9 relhassubclass │ f reloptions │ ¤ pg_table_size │ 152695029760 (142 GB) I have caught this with AUTOEXPLAIN: 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) -> Index Scan Backward using mytable_idx2 on mytable pbh (cost=0.70..21552.55 rows=3869 width=66) Index Cond: ((cage_code = $3) AND (cage_player_id = $2) AND (modified_time < $5)) Filter: (((product_code)::text = ($1)::text) AND ((balance_type)::text = ($4)::text)) And when I run EXPLAIN ANALYZE on the same query with the same parameters manually: Limit (cost=177.75..177.75 rows=1 width=66) (actual time=8.635..8.635 rows=1 loops=1) -> Sort (cost=177.75..178.21 rows=186 width=66) (actual time=8.634..8.634 rows=1 loops=1) Sort Key: modified_time DESC Sort Method: top-N heapsort Memory: 25kB -> Index Scan using mytable_pk on mytable pbh (cost=0.70..176.82 rows=186 width=66) (actual time=1.001..8.610 rows=25 loops=1) Index Cond: ((cage_code = 123) AND (cage_player_id = '12345'::bigint) AND ((product_code)::text = 'PRODUCT'::text) AND ((balance_type)::text = 'TOTAL'::text)) Filter: (modified_time < '2021-09-13 04:00:00+00'::timestamp with time zone) Planning Time: 2.117 ms Execution Time: 8.658 ms I have played around with SET STATISTICS, work_mem and even tried CREATE STATISTICS although there is no functional dependency on the table columns in questions, but nothing seems to work. Any ideas, hints are very much appreciated! With best regards, -- Kristjan Mustkivi Email: [email protected]
Re: Postgres chooses slow query plan from time to time
On 9/13/21 3:24 PM, Kristjan Mustkivi wrote: > Dear community, > > I have a query that most of the time gets executed in a few > milliseconds yet occasionally takes ~20+ seconds. The difference, as > far as I am able to tell, comes whether it uses the table Primary Key > (fast) or an additional index with smaller size. The table in question > is INSERT ONLY - no updates or deletes done there. > It'd be really useful to have explain analyze for the slow execution. My guess is there's a poor estimate, affecting some of the parameter values, and it probably resolves itself after autoanalyze run. I see you mentioned SET STATISTICS, so you tried increasing the statistics target for some of the columns? Have you tried lowering autovacuum_analyze_scale_factor to make autoanalyze more frequent? It's also possible most values are independent, but some values have a rather strong dependency, skewing the estimates. The MCV would help with that, but those are in PG12 :-( regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Postgres chooses slow query plan from time to time
Autovacuum will only run for freezing, right? Insert only tables don't get autovacuumed/analyzed until PG13 if I remember right.
Re: Postgres chooses slow query plan from time to time
On Mon, Sep 13, 2021 at 08:19:40AM -0600, Michael Lewis wrote: > Autovacuum will only run for freezing, right? Insert only tables don't get > autovacuumed/analyzed until PG13 if I remember right. Tomas is talking about autovacuum running *analyze*, not vacuum. It runs for analyze, except on partitioned tables and (empty) inheritence parents. -- Justin
RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
> -Original Message- > From: Andrew Dunstan > Sent: Thursday, September 2, 2021 13:00 > To: Julien Rouhaud > Cc: [email protected]; Tom Lane ; Ranier > Vilela ; Justin Pryzby ; > [email protected] > Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 > and 13.4 > > > On 9/2/21 11:34 AM, Julien Rouhaud wrote: > > On Thu, Sep 2, 2021 at 11:22 PM Andrew Dunstan > wrote: > >> Here are a couple of pictures of profiles made with a tool called > >> sleepy. The bad profile is from release 13.4 built with the latest > >> gettext, built with vcpkg. The good profile is the same build but > >> using the intl-8.dll copied from the release 11.13 installer. The > >> good run takes about a minute. The bad run takes about 30 minutes. > >> > >> > >> I'm not exactly sure what the profiles tell us. > > Isn't GetLocaleInfoA suspicious? Especially since the doc [1] says > > that it shouldn't be called anymore unless you want to have > > compatibility with OS from more than a decade ago? > > > > [1] > > https://docs.microsoft.com/en-us/windows/win32/api/winnls/nf- > winnls-ge > > tlocaleinfoa > > Possibly, but the profile doesn't show it as having a great impact. > > Maybe surrounding code is affected. > > cheers > > andrew > > > -- > Andrew Dunstan > EDB: https://www.enterprisedb.com Hello all, Any further update or guidance on this issue at this time? Thank you, Laurent.
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
On 9/13/21 10:32 AM, [email protected] wrote: > > Hello all, > > Any further update or guidance on this issue at this time? > Wait for a new installer. Our team is working on it. As I have previously advised you, please be patient. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
> -Original Message- > From: Andrew Dunstan > Sent: Monday, September 13, 2021 11:36 > To: [email protected]; Julien Rouhaud > Cc: Tom Lane ; Ranier Vilela ; > Justin Pryzby ; pgsql- > [email protected] > Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 > and 13.4 > > > On 9/13/21 10:32 AM, [email protected] wrote: > > > > Hello all, > > > > Any further update or guidance on this issue at this time? > > > > Wait for a new installer. Our team is working on it. As I have previously > advised you, please be patient. > > > cheers > > > andrew > > -- > Andrew Dunstan > EDB: https://www.enterprisedb.com Hello Andrew, I'll be as patient as is needed and appreciate absolutely all the work you are all doing. I also know V14 is just around the corner too so the team is super busy 😊 Just looking for some super-rough ETA for some rough planning on our end. Is this something potentially for 13.5 later this year? Or something that may happen before the end of Sept? Or still unknown? And I understand all is always tentative. Thank you! Laurent.
Re: Postgres chooses slow query plan from time to time
On Mon, Sep 13, 2021 at 9:25 AM Kristjan Mustkivi wrote: > 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; > > "mytable_idx2" btree (cage_code, cage_player_id, modified_time) > Why does this index exist? It seems rather specialized, but what is it specialized for? If you are into specialized indexes, the ideal index for this query would be: btree (cage_code, cage_player_id, product_code, balance_type, modified_time) But the first 4 columns can appear in any order if that helps you combine indexes. If this index existed, then it wouldn't have to choose between two other suboptimal indexes, and so would be unlikely to choose incorrectly between them. Cheers, Jeff
Re: Postgres chooses slow query plan from time to time
On Mon, Sep 13, 2021 at 9:25 AM Kristjan Mustkivi wrote: > > I have caught this with AUTOEXPLAIN: > > Index Cond: ((cage_code = $3) AND (cage_player_id = $2) AND > (modified_time < $5)) > Filter: (((product_code)::text = ($1)::text) AND > ((balance_type)::text = ($4)::text)) > > Is it always the case that autoexplain shows plans with $1 etc, rather than real values, for the slow queries? If so, then it could be that the switch from custom to generic plans is causing the problem. Cheers, Jeff
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
On 9/13/21 11:53 AM, [email protected] wrote: > >> -Original Message- >> From: Andrew Dunstan >> Sent: Monday, September 13, 2021 11:36 >> To: [email protected]; Julien Rouhaud >> Cc: Tom Lane ; Ranier Vilela ; >> Justin Pryzby ; pgsql- >> [email protected] >> Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 >> and 13.4 >> >> >> On 9/13/21 10:32 AM, [email protected] wrote: >> > >> > Hello all, >> > >> > Any further update or guidance on this issue at this time? >> > >> >> Wait for a new installer. Our team is working on it. As I have > previously >> advised you, please be patient. >> >> >> cheers >> >> >> andrew >> >> -- >> Andrew Dunstan >> EDB: https://www.enterprisedb.com > > > Hello Andrew, > > I'll be as patient as is needed and appreciate absolutely all the work you > are all doing. I also know V14 is just around the corner too so the team is > super busy 😊 > > Just looking for some super-rough ETA for some rough planning on our end. Is > this something potentially for 13.5 later this year? Or something that may > happen before the end of Sept? Or still unknown? And I understand all is > always tentative. > This is not governed at all by the Postgres release cycle. The issue is not with Postgres but with the version of libintl used in the build. I can't speak for the team, they will publish an updated installer when they get it done. But rest assured it's being worked on. I got email about it just this morning. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
EnterpriseDB
The company I work for will test EnterpriseDB. I am fairly well acquainted with Postgres but have no experience whatsoever with EnterpriseDB. How compatible to Postgres it is? Do pgAdmin4 and pgBadger work with EnterpriseDB? Are psql commands the same? Can anyone here share some impressions? Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
