From: Anupam b <abordia99@hotmail.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mikhail Balayan <mv.balayan@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Planning time is time-consuming
Date: Mon, 11 Sep 2023 01:23:46 +0000
Message-ID: <DM4PR20MB57256A764C408DE596ECD405BDF2A@DM4PR20MB5725.namprd20.prod.outlook.com> (raw)
In-Reply-To: <5ef3acf5733d5a8584b09eb5dd107e59aa87a075.camel@cybertec.at>
References: <CAC2oM1bUqsBFQKVrZy9Z95SuDbmj-uSkXdbYfTnRwyTACSmd4w@mail.gmail.com>
<5ef3acf5733d5a8584b09eb5dd107e59aa87a075.camel@cybertec.at>
Also, if you write sql with bind params, planning time should be once for the sql. Subsequent sql will use cached stmt.
Get Outlook for Android<https://aka.ms/AAb9ysg;
________________________________
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Sunday, September 10, 2023 6:15:43 PM
To: Mikhail Balayan <mv.balayan@gmail.com>; pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Planning time is time-consuming
On Fri, 2023-09-08 at 18:51 +0800, Mikhail Balayan wrote:
> I have three tables:
> - test_db_bench_1
> - test_db_bench_tenants
> - test_db_bench_tenant_closure
>
> And the query to join them:
> SELECT "test_db_bench_1"."id" id, "test_db_bench_1"."tenant_id"
> FROM "test_db_bench_1"
> JOIN "test_db_bench_tenants" AS "tenants_child" ON (("tenants_child"."uuid" = "test_db_bench_1"."tenant_id")
> AND ("tenants_child"."is_deleted" != true))
> JOIN "test_db_bench_tenant_closure" AS "tenants_closure" ON (("tenants_closure"."child_id" = "tenants_child"."id")
> AND ("tenants_closure"."barrier" <= 0))
> JOIN "test_db_bench_tenants" AS "tenants_parent" ON (("tenants_parent"."id" = "tenants_closure"."parent_id")
> AND ("tenants_parent"."uuid" IN ('4c79c1c5-21ae-45a0-8734-75d67abd0330'))
> AND ("tenants_parent"."is_deleted" != true))
> LIMIT 1
>
>
> With following execution plan:
>
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> ---------------
> Limit (cost=1.56..1.92 rows=1 width=44) (actual time=0.010..0.011 rows=0 loops=1)
> -> Nested Loop (cost=1.56..162.42 rows=438 width=44) (actual time=0.009..0.009 rows=0 loops=1)
> -> Nested Loop (cost=1.13..50.27 rows=7 width=36) (actual time=0.008..0.009 rows=0 loops=1)
> -> Nested Loop (cost=0.84..48.09 rows=7 width=8) (actual time=0.008..0.009 rows=0 loops=1)
> -> Index Scan using test_db_bench_tenants_uuid on test_db_bench_tenants tenants_parent (cost=0.41..2.63 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)
> Index Cond: ((uuid)::text = '4c79c1c5-21ae-45a0-8734-75d67abd0330'::text)
> Filter: (NOT is_deleted)
> -> Index Scan using test_db_bench_tenant_closure_pkey on test_db_bench_tenant_closure tenants_closure (cost=0.42..45.06 rows=40 width=16) (never executed)
> Index Cond: (parent_id = tenants_parent.id)
> Filter: (barrier <= 0)
> -> Index Scan using test_db_bench_tenants_pkey on test_db_bench_tenants tenants_child (cost=0.29..0.31 rows=1 width=44) (never executed)
> Index Cond: (id = tenants_closure.child_id)
> Filter: (NOT is_deleted)
> -> Index Scan using test_db_bench_1_idx_tenant_id_3 on acronis_db_bench_heavy (cost=0.43..14.66 rows=136 width=44) (never executed)
> Index Cond: ((tenant_id)::text = (tenants_child.uuid)::text)
> Planning Time: 0.732 ms
> Execution Time: 0.039 ms
>
>
> Where the planning time gets in the way as it takes an order of magnitude more time than the actual execution.
>
> Is there a possibility to reduce this time? And, in general, to understand why planning takes so much time.
You could try to VACUUM the involved tables; indexes with many entries pointing 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 database, 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: abordia99@hotmail.com, laurenz.albe@cybertec.at, mv.balayan@gmail.com
Subject: Re: Planning time is time-consuming
In-Reply-To: <DM4PR20MB57256A764C408DE596ECD405BDF2A@DM4PR20MB5725.namprd20.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox