Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uEStz-008VrI-7P for pgsql-performance@arkaria.postgresql.org; Mon, 12 May 2025 13:09:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uEStx-00G18Q-Gk for pgsql-performance@arkaria.postgresql.org; Mon, 12 May 2025 13:09:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uEStw-00G185-U6 for pgsql-performance@lists.postgresql.org; Mon, 12 May 2025 13:09:01 +0000 Received: from mail-io1-xd2b.google.com ([2607:f8b0:4864:20::d2b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uEStu-001Qj7-0h for pgsql-performance@lists.postgresql.org; Mon, 12 May 2025 13:09:00 +0000 Received: by mail-io1-xd2b.google.com with SMTP id ca18e2360f4ac-85e73562577so478563539f.0 for ; Mon, 12 May 2025 06:08:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1747055337; x=1747660137; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=T/oOkZvCjJrQbOESGTMiklJ0v9bpoQsUP5tZ/mwxhDk=; b=fBq17N2qNeLp8iYzYlaqd8y6T6F63kW13KGFqK9ktlDw2JWF467CILA47FR+5yjO4d pUTFvKyG37rVn1qba8lUSmlPaqCl6UAs69m2P3qF7xZS87xWIzKP79msC1EIJ73v+vLB VuVVkWm33/+vZF1+ATHD9wnpKgvou9MD/pmFBNbyCAaHkOu8LCoUmYOxlipaD6Kd5N1h KgZ7UvLetU8CO9rNm1kAtqj0fY5EmFT/0Cp9xlMuELxVGzmWxQevw/D1nN5y16NgAVnt s62QxJ7cXI/EPvXBy2y1P5GplZ3p3Lowk0avG/JTavYuazAQ+rr1fr+iIUUAo4iR9gPH zPWA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1747055337; x=1747660137; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=T/oOkZvCjJrQbOESGTMiklJ0v9bpoQsUP5tZ/mwxhDk=; b=Np+WAUU312ndFto5iFdb72wls229pFi2Nw76Disj4QCG0asM+b1WCnxWqWqyZvOMY5 ViMf1sfzXGWqRc9A7XOyRjaytXVDRh2XduMgYYT92PzDgPKZCa3KTafYz41mmxpn7Wa5 h2UrHw3MO1kO6ogh6ldtZpFQ6v7WH3CnZ7Ck1nid6sZDGSG5PS64uchvbEftVkWv2MOb 8msAhNrQPpeKs6Z4JvAGVrUzi8GluKIj/Ud+4U4FIjmAFw/3s3bhxbY8tGe0debFZhNM kQxXswomHp1AwVGGbWBUft4HKscKyHx/ZTobFyYQ3PTisnAGGdCrpuYZ5GsVifF6uHkv n9nw== X-Gm-Message-State: AOJu0YzEdiQW5xIYikIjduxLA37h22j719aIF9grElKWS5043bv9zTdK OwV09sEkni0Wma73WctzzcOpOkiOzJSO9RHfCpxlZFqd1b1aasHQwthVHIFqg3fEYDaDKWmZmUI 7WAW/3XKIxM6Xwv5q6J5edDTV3PU6k2mVoIMYvg== X-Gm-Gg: ASbGncuDvnjjCIn9TQFMxpNazq0fLMacBpenY9tPkQ78lzOW7kp8z9u+Fg8xrYRkSPA t8QczRQ+tApJppu+NfRTm6u6grIiNEiuUHKXgMMRWjw6YxiK810qcwLekFDpGuYmhCJ8JurkdHm PRQUSQDNCUPkGMDTJqJof4PBhVxzCocDo= X-Google-Smtp-Source: AGHT+IFgJ2Zd+1meLqvEZvF05l8B6n+KqOGV7OeZ6scjhlDhFwXQZAvfdcHwg0oYEG1q6nBIgjDHD4i4a1/pAvcWt3M= X-Received: by 2002:a05:6808:1587:b0:3f3:f90b:f19d with SMTP id 5614622812f47-4037fec54f1mr7492015b6e.33.1747055325725; Mon, 12 May 2025 06:08:45 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Maxim Boguk Date: Mon, 12 May 2025 16:08:09 +0300 X-Gm-Features: AX0GCFuYBca068LkE2S79qDb7qQSMaUQGUud86IhCzEq-wMwr7nZrbkmpR-OL0g Message-ID: Subject: Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) To: Andrei Lepikhov Cc: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000017c8700634f004a7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000017c8700634f004a7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, May 12, 2025 at 3:08=E2=80=AFPM Andrei Lepikhov = wrote: > On 5/12/25 13:49, Maxim Boguk wrote: > > I suspect this situation should be quite common with queries over > > partitioned tables (where planning time is usually quite a high). > > > > Any suggestions what could be done there outside of using > > force_generic_plan for a particular db user (which will kill performanc= e > > in other queries for sure)? > Thanks for this puzzle! > I suppose, in case generic planning is much faster than custom one, > there are two candidates exist: > 1. Touching the index during planning causes too much overhead - see > get_actual_variable_range > 2. You have a massive default_statistics_target for a table involved. > > So, to clarify the problem, may you provide EXPLAIN (without analyze) > with BUFFERS ON ? > Also, could you provide extra information on the statistics involved? > For each column (I think created_at is the most important one), show the > size of MCV and histogram arrays. > > -- > regards, Andrei Lepikhov > clickcast=3D# explain (buffers) execute qqq('2025-04-11 09:22:00.193'::timestamp without time zone, '2025-05-12 09:22:00.203'::timestamp without time zone); QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------- Limit (cost=3D1.14..1.29 rows=3D1 width=3D385) -> Append (cost=3D1.14..9.10 rows=3D50 width=3D385) -> Index Scan Backward using job_stats_new_2025_05_job_board_id_job_reference_created_at_idx on job_stats_new_2025_05 job_stats_master_2 (cost=3D0.56..3.28 rows=3D18 width=3D371) Index Cond: ((job_board_id =3D 27068) AND ((job_reference)::text =3D '*****'::text) AND (created_at >=3D '2025-04-11 09:22:00.193'::timestamp without time zone) AND (created_at <=3D '2025-05-1= 2 09:22:00.203'::timestamp without time zone)) -> Index Scan Backward using job_stats_new_2025_04_job_board_id_job_reference_created_at_idx on job_stats_new_2025_04 job_stats_master_1 (cost=3D0.57..5.32 rows=3D32 width=3D394) Index Cond: ((job_board_id =3D 27068) AND ((job_reference)::text =3D '*******'::text) AND (created_at >=3D '2025-04-1= 1 09:22:00.193'::timestamp without time zone) AND (created_at <=3D '2025-05-1= 2 09:22:00.203'::timestamp without time zone)) Planning: Buffers: shared hit=3D16 16 buffers - most times, sometimes 12k Buffers: shared hit=3D12511 (like = 5% cases) - I have no idea why. show default_statistics_target ; default_statistics_target --------------------------- 100 No custom statistic targets on this table or partitions. select tablename,attname,inherited,null_frac,n_distinct,array_length(most_common_v= als,1) mcv, array_length(histogram_bounds,1) hist from pg_stats where tablename IN ('job_stats_master', 'job_stats_new_2025_04', 'job_stats_new_2025_05') and attname in ('created_at', 'job_board_id', 'job_reference') order by tablename, attname; tablename | attname | inherited | null_frac | n_distinct | mcv | hist -----------------------+---------------+-----------+------------+----------= ----+-----+------ job_stats_master | created_at | t | 0 | 1.066586e+06 | 15 | 101 job_stats_master | job_board_id | t | 0.52743334 | 1716 | 100 | 101 job_stats_master | job_reference | t | 0 | -0.1 | 39 | 101 job_stats_new_2025_04 | created_at | f | 0 | 832508 | 39 | 101 job_stats_new_2025_04 | job_board_id | f | 0.47096667 | 1096 | 100 | 101 job_stats_new_2025_04 | job_reference | f | 0 | -0.1 | 93 | 101 job_stats_new_2025_05 | created_at | f | 0 | 709166 | 42 | 101 job_stats_new_2025_05 | job_board_id | f | 0.4703 | 1142 | 100 | 101 job_stats_new_2025_05 | job_reference | f | 0 | -0.1 | 100 | 101 PS: problem not with difference between custom and generic planning time but with prepared statements generic plan plans only once, but custom plan plan every call (and plan time cost 95% on total query runtime). --=20 Maxim Boguk Senior Postgresql DBA Phone UA: +380 99 143 0000 Phone AU: +61 45 218 5678 --00000000000017c8700634f004a7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Mon, May 12, 2025 at 3:08=E2=80=AFPM Andrei Lepikhov <lepihov@gmail.com> wrote:
On 5/12/25 13:49, Maxim Boguk wro= te:
> I suspect this situation should be quite common with queries over
> partitioned tables (where planning time is usually quite a high).
>
> Any suggestions what could be done there outside of using
> force_generic_plan for a particular db user (which will kill performan= ce
> in other queries for sure)?
Thanks for this puzzle!
I suppose, in case generic planning is much faster than custom one,
there are two candidates exist:
1. Touching the index during planning causes too much overhead - see
get_actual_variable_range
2. You have a massive default_statistics_target for a table involved.

So, to clarify the problem, may you provide EXPLAIN (without analyze)
with BUFFERS ON ?
Also, could you provide extra information on the statistics involved?
For each column (I think created_at is the most important one), show the size of MCV and histogram arrays.

--
regards, Andrei Lepikhov


clickcast=3D# explain (buffers) execute qqq('2025-04-11 09:22:00.1= 93'::timestamp without time zone, '2025-05-12 09:22:00.203'::ti= mestamp without time zone);
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0
--------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------
=C2=A0Li= mit =C2=A0(cost=3D1.14..1.29 rows=3D1 width=3D385)
=C2=A0 =C2=A0-> = =C2=A0Append =C2=A0(cost=3D1.14..9.10 rows=3D50 width=3D385)
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan Backward using job_stats_new= _2025_05_job_board_id_job_reference_created_at_idx on job_stats_new_2025_05= job_stats_master_2 =C2=A0(cost=3D0.56..3.28 rows=3D18 width=3D371)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((job_board= _id =3D 27068) AND ((job_reference)::text =3D '*****'::text) AND (c= reated_at >=3D '2025-04-11 09:22:00.193'::timestamp without time= zone) AND (created_at <=3D '2025-05-12 09:22:00.203'::timestamp= without time zone))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index= Scan Backward using job_stats_new_2025_04_job_board_id_job_reference_creat= ed_at_idx on job_stats_new_2025_04 job_stats_master_1 =C2=A0(cost=3D0.57..5= .32 rows=3D32 width=3D394)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Index Cond: ((job_board_id =3D 27068) AND ((job_reference)::text = =3D '*******'::text) AND (created_at >=3D '2025-04-11 09:22:= 00.193'::timestamp without time zone) AND (created_at <=3D '2025= -05-12 09:22:00.203'::timestamp without time zone))
=C2=A0Planning:<= br>=C2=A0 =C2=A0Buffers: shared hit=3D16
16 buffers - most times, sometimes 12k =C2=A0 Buffers: shared hit=3D12= 511 (like 5% cases) - I have no idea why.

s= how default_statistics_target ;
=C2=A0default_statistics_target
----= -----------------------
=C2=A0100
No = custom statistic targets on this table or partitions.

<= /div>
select tablename,attname,inherited,null_frac,n_distin= ct,array_length(most_common_vals,1) mcv, array_length(histogram_bounds,1) h= ist from pg_stats where tablename IN ('job_stats_master', 'job_= stats_new_2025_04', 'job_stats_new_2025_05') and attname in (&#= 39;created_at', 'job_board_id', 'job_reference') order = by tablename, attname;
=C2=A0 =C2=A0 =C2=A0 =C2=A0tablename =C2=A0 =C2= =A0 =C2=A0 | =C2=A0 =C2=A0attname =C2=A0 =C2=A0| inherited | null_frac =C2= =A0| =C2=A0n_distinct =C2=A0| mcv | hist
-----------------------+------= ---------+-----------+------------+--------------+-----+------
=C2=A0job= _stats_master =C2=A0 =C2=A0 =C2=A0| created_at =C2=A0 =C2=A0| t =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00 | 1.066586e+06 | = =C2=A015 | =C2=A0101
=C2=A0job_stats_master =C2=A0 =C2=A0 =C2=A0| job_bo= ard_id =C2=A0| t =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 0.52743334 | =C2=A0 =C2=A0 = =C2=A0 =C2=A0 1716 | 100 | =C2=A0101
=C2=A0job_stats_master =C2=A0 =C2= =A0 =C2=A0| job_reference | t =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A00 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 -0.1 | =C2=A039 | =C2= =A0101
=C2=A0job_stats_new_2025_04 | created_at =C2=A0 =C2=A0| f =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00 | =C2=A0 =C2=A0 = =C2=A0 832508 | =C2=A039 | =C2=A0101
=C2=A0job_stats_new_2025_04 | job_b= oard_id =C2=A0| f =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 0.47096667 | =C2=A0 =C2=A0 = =C2=A0 =C2=A0 1096 | 100 | =C2=A0101
=C2=A0job_stats_new_2025_04 | job_r= eference | f =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A00 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 -0.1 | =C2=A093 | =C2=A0101
=C2=A0job= _stats_new_2025_05 | created_at =C2=A0 =C2=A0| f =C2=A0 =C2=A0 =C2=A0 =C2= =A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00 | =C2=A0 =C2=A0 =C2=A0 709166 | = =C2=A042 | =C2=A0101
=C2=A0job_stats_new_2025_05 | job_board_id =C2=A0| = f =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 0.4703 | =C2=A0 =C2=A0 =C2=A0= =C2=A0 1142 | 100 | =C2=A0101
=C2=A0job_stats_new_2025_05 | job_referen= ce | f =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00 | = =C2=A0 =C2=A0 =C2=A0 =C2=A0 -0.1 | 100 | =C2=A0101


PS: problem not with difference betwee= n custom and generic planning time but with prepared statements
generic plan plans only once, but custom plan plan every call (and pl= an time cost 95% on total query runtime).

--
Maxim Boguk
Senior Postgresql DBA

Phone UA: +380 99 143 0000
= Phone AU: +61=C2=A0 45 218 5678

--00000000000017c8700634f004a7--