Message-ID: <5ef3acf5733d5a8584b09eb5dd107e59aa87a075.camel@cybertec.at>
Subject: Re: Planning time is time-consuming
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mikhail Balayan <mv.balayan@gmail.com>, pgsql-performance@postgresql.org
Date: Mon, 11 Sep 2023 03:15:43 +0200
In-Reply-To: 
 <CAC2oM1bUqsBFQKVrZy9Z95SuDbmj-uSkXdbYfTnRwyTACSmd4w@mail.gmail.com>
References: 
	<CAC2oM1bUqsBFQKVrZy9Z95SuDbmj-uSkXdbYfTnRwyTACSmd4w@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/5ef3acf5733d5a8584b09eb5dd107e59aa87a075.camel%40cybertec.at>
Precedence: bulk

On Fri, 2023-09-08 at 18:51 +0800, Mikhail Balayan wrote:
> I have three tables:
> =C2=A0 =C2=A0 - test_db_bench_1
> =C2=A0 =C2=A0 - test_db_bench_tenants
> =C2=A0 =C2=A0 - test_db_bench_tenant_closure
>=20
> And the query to join them:
> SELECT "test_db_bench_1"."id" id, "test_db_bench_1"."tenant_id"
> =C2=A0 FROM "test_db_bench_1"
> =C2=A0 JOIN "test_db_bench_tenants" AS "tenants_child" ON (("tenants_chil=
d"."uuid" =3D "test_db_bench_1"."tenant_id")=20
> =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=A0AND ("tenants_child"."is_deleted" !=3D true))
> =C2=A0 JOIN "test_db_bench_tenant_closure" AS "tenants_closure" ON (("ten=
ants_closure"."child_id" =3D "tenants_child"."id")
> =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 AND ("tenants_closu=
re"."barrier" <=3D 0))
> =C2=A0 JOIN "test_db_bench_tenants" AS "tenants_parent" ON (("tenants_par=
ent"."id" =3D "tenants_closure"."parent_id")
> =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 AND ("tenants_parent"."uuid" IN ('4c79c1c5-21ae=
-45a0-8734-75d67abd0330'))
> =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 AND ("tenants_parent"."is_deleted" !=3D true))
> =C2=A0LIMIT 1
>=20
>=20
> With following execution plan:
>=20
> =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=A0Limit =C2=A0(cost=3D1.56..1.92 rows=3D1 width=3D44) (actual time=3D=
0.010..0.011 rows=3D0 loops=3D1)
> =C2=A0 =C2=A0-> =C2=A0Nested Loop =C2=A0(cost=3D1.56..162.42 rows=3D438 w=
idth=3D44) (actual time=3D0.009..0.009 rows=3D0 loops=3D1)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Nested Loop =C2=A0(cost=3D1.13=
..50.27 rows=3D7 width=3D36) (actual time=3D0.008..0.009 rows=3D0 loops=3D1=
)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Nested Lo=
op =C2=A0(cost=3D0.84..48.09 rows=3D7 width=3D8) (actual time=3D0.008..0.00=
9 rows=3D0 loops=3D1)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0-> =C2=A0Index Scan using test_db_bench_tenants_uuid on test_db_bench_te=
nants tenants_parent =C2=A0(cost=3D0.41..2.63 rows=3D1 width=3D8) (actual t=
ime=3D0.008..0.008 rows=3D0 loops=3D1)
> =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=A0Index Cond: ((uuid)::text =3D '4c79c1c5-21ae-45a0-8=
734-75d67abd0330'::text)
> =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=A0Filter: (NOT is_deleted)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0-> =C2=A0Index Scan using test_db_bench_tenant_closure_pkey on test_db_b=
ench_tenant_closure tenants_closure =C2=A0(cost=3D0.42..45.06 rows=3D40 wid=
th=3D16) (never executed)
> =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=A0Index Cond: (parent_id =3D tenants_parent.id)
> =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=A0Filter: (barrier <=3D 0)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Sca=
n using test_db_bench_tenants_pkey on test_db_bench_tenants tenants_child =
=C2=A0(cost=3D0.29..0.31 rows=3D1 width=3D44) (never executed)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0Index Cond: (id =3D tenants_closure.child_id)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0Filter: (NOT is_deleted)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using test_db_bench=
_1_idx_tenant_id_3 on acronis_db_bench_heavy =C2=A0(cost=3D0.43..14.66 rows=
=3D136 width=3D44) (never executed)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((tena=
nt_id)::text =3D (tenants_child.uuid)::text)
> =C2=A0Planning Time: 0.732 ms
> =C2=A0Execution Time: 0.039 ms
>=20
>=20
> Where the planning time gets in the way as it takes an order of magnitude=
 more time than the actual execution.
>=20
> Is there a possibility to reduce this time? And, in general, to understan=
d why planning takes so much time.

You could try to VACUUM the involved tables; indexes with many entries poin=
ting to dead tuples
can cause a long planing time.

Also, there are quite a lot of indexes on "test_db_bench_1".  On a test dat=
abase, drop some
indexes and see if that makes a difference.

Finally, check if "default_statistics_target" is set to a high value, or if=
 the "Stats target"
for some column in the "\d+ tablename" output is set higher than 100.

Yours,
Laurenz Albe