Hello,
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.
What I have tried:
- disabled JIT, which resulted in a minor improvement, around 5 microseconds.
- disabled constraint_exclusion, which also didn't have a significant impact.
Sizes of tables and indexes:
-- test_db_bench_1
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-----------------+-------+--------+-------------+---------------+---------+-------------
public | test_db_bench_1 | table | dbuser | permanent | heap | 5351 MB |
Column | Type | Collation | Nullable | Default
---------------------------+------------------------+-----------+----------+----------------------------------------------
------
id | bigint | | not null | nextval('test_db_bench_1_id_seq'::regclass)
uuid | uuid | | not null |
checksum | character varying(64) | | not null |
tenant_id | character varying(36) | | not null |
cti_entity_uuid | character varying(36) | | |
euc_id | character varying(64) | | not null |
workflow_id | bigint | | |
state | integer | | not null |
type | character varying(64) | | not null |
queue | character varying(64) | | not null |
priority | integer | | not null |
issuer_id | character varying(64) | | not null |
issuer_cluster_id | character varying(64) | | |
heartbeat_ivl_str | character varying(64) | | |
heartbeat_ivl_ns | bigint | | |
queue_timeout_str | character varying(64) | | |
queue_timeout_ns | bigint | | |
ack_timeout_str | character varying(64) | | |
ack_timeout_ns | bigint | | |
exec_timeout_str | character varying(64) | | |
exec_timeout_ns | bigint | | |
life_time_str | character varying(64) | | |
life_time_ns | bigint | | |
max_assign_count | integer | | not null |
assign_count | integer | | not null |
max_fail_count | integer | | not null |
fail_count | integer | | not null |
cancellable | boolean | | not null |
cancel_requested | boolean | | not null |
blocker_count | integer | | not null |
started_by_user | character varying(256) | | |
policy_id | character varying(64) | | |
policy_type | character varying(64) | | |
policy_name | character varying(256) | | |
resource_id | character varying(64) | | |
resource_type | character varying(64) | | |
resource_name | character varying(256) | | |
tags | text | | |
affinity_agent_id | character varying(64) | | not null |
affinity_cluster_id | character varying(64) | | not null |
argument | bytea | | |
context | bytea | | |
progress | integer | | |
progress_total | integer | | |
assigned_agent_id | character varying(64) | | |
assigned_agent_cluster_id | character varying(64) | | |
enqueue_time_str | character varying(64) | | |
enqueue_time_ns | bigint | | not null |
assign_time_str | character varying(64) | | |
assign_time_ns | bigint | | |
start_time_str | character varying(64) | | |
start_time_ns | bigint | | |
update_time_str | character varying(64) | | not null |
update_time_ns | bigint | | not null |
completion_time_str | character varying(64) | | |
completion_time_ns | bigint | | |
result_code | integer | | |
result_error | bytea | | |
result_warnings | bytea | | |
result_payload | bytea | | |
const_val | integer | | |
Indexes:
"test_db_bench_1_pkey" PRIMARY KEY, btree (id)
"test_db_bench_1_idx_completion_time_ns_1" btree (completion_time_ns)
"test_db_bench_1_idx_cti_entity_uuid_2" btree (cti_entity_uuid)
"test_db_bench_1_idx_enqueue_time_ns_10" btree (enqueue_time_ns)
"test_db_bench_1_idx_euc_id_4" btree (euc_id)
"test_db_bench_1_idx_policy_id_12" btree (policy_id)
"test_db_bench_1_idx_queue_18" btree (queue, type, tenant_id)
"test_db_bench_1_idx_queue_19" btree (queue, type, euc_id)
"test_db_bench_1_idx_queue_5" btree (queue, state, affinity_agent_id, affinity_cluster_id, tenant_id, priority)
"test_db_bench_1_idx_queue_6" btree (queue, state, affinity_agent_id, affinity_cluster_id, euc_id, priority)
"test_db_bench_1_idx_resource_id_11" btree (resource_id)
"test_db_bench_1_idx_resource_id_14" btree (resource_id, enqueue_time_ns)
"test_db_bench_1_idx_result_code_13" btree (result_code)
"test_db_bench_1_idx_start_time_ns_9" btree (start_time_ns)
"test_db_bench_1_idx_state_8" btree (state, completion_time_ns)
"test_db_bench_1_idx_tenant_id_3" btree (tenant_id)
"test_db_bench_1_idx_type_15" btree (type)
"test_db_bench_1_idx_type_16" btree (type, tenant_id, enqueue_time_ns)
"test_db_bench_1_idx_type_17" btree (type, euc_id, enqueue_time_ns)
"test_db_bench_1_idx_update_time_ns_7" btree (update_time_ns)
"test_db_bench_1_idx_uuid_0" btree (uuid)
"test_db_bench_1_uuid_key" UNIQUE CONSTRAINT, btree (uuid)
-- test_db_bench_tenants
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-----------------------+-------+--------+-------------+---------------+---------+-------------
public | test_db_bench_tenants | table | dbuser | permanent | heap | 8432 kB |
Column | Type | Collation | Nullable | Default
-------------------+------------------------+-----------+----------+---------
id | bigint | | not null |
uuid | character varying(36) | | not null |
name | character varying(255) | | not null |
kind | character(1) | | not null |
is_deleted | boolean | | not null | false
parent_id | bigint | | not null |
parent_has_access | boolean | | not null | true
nesting_level | smallint | | not null |
Indexes:
"test_db_bench_tenants_pkey" PRIMARY KEY, btree (id)
"test_db_bench_tenants_uuid" UNIQUE CONSTRAINT, btree (uuid)
-- test_db_bench_tenant_closure
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------------------------+-------+--------+-------------+---------------+-------+-------------
public | test_db_bench_tenant_closure | table | dbuser | permanent | heap | 22 MB |
Column | Type | Collation | Nullable | Default
-------------+--------------+-----------+----------+---------
parent_id | bigint | | not null |
child_id | bigint | | not null |
parent_kind | character(1) | | not null |
barrier | smallint | | not null | 0
Indexes:
"test_db_bench_tenant_closure_pkey" PRIMARY KEY, btree (parent_id, child_id)
"cybercache_tenants_closure_child_id_idx" btree (child_id)
Postgresql version: 15.3 (Debian 15.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
And just in case it matters, this is an experimental setup, so Postgresql running in Docker.
Thank you.
--
Mikhail
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.
What I have tried:
- disabled JIT, which resulted in a minor improvement, around 5 microseconds.
- disabled constraint_exclusion, which also didn't have a significant impact.
Sizes of tables and indexes:
-- test_db_bench_1
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-----------------+-------+--------+-------------+---------------+---------+-------------
public | test_db_bench_1 | table | dbuser | permanent | heap | 5351 MB |
Column | Type | Collation | Nullable | Default
---------------------------+------------------------+-----------+----------+----------------------------------------------
------
id | bigint | | not null | nextval('test_db_bench_1_id_seq'::regclass)
uuid | uuid | | not null |
checksum | character varying(64) | | not null |
tenant_id | character varying(36) | | not null |
cti_entity_uuid | character varying(36) | | |
euc_id | character varying(64) | | not null |
workflow_id | bigint | | |
state | integer | | not null |
type | character varying(64) | | not null |
queue | character varying(64) | | not null |
priority | integer | | not null |
issuer_id | character varying(64) | | not null |
issuer_cluster_id | character varying(64) | | |
heartbeat_ivl_str | character varying(64) | | |
heartbeat_ivl_ns | bigint | | |
queue_timeout_str | character varying(64) | | |
queue_timeout_ns | bigint | | |
ack_timeout_str | character varying(64) | | |
ack_timeout_ns | bigint | | |
exec_timeout_str | character varying(64) | | |
exec_timeout_ns | bigint | | |
life_time_str | character varying(64) | | |
life_time_ns | bigint | | |
max_assign_count | integer | | not null |
assign_count | integer | | not null |
max_fail_count | integer | | not null |
fail_count | integer | | not null |
cancellable | boolean | | not null |
cancel_requested | boolean | | not null |
blocker_count | integer | | not null |
started_by_user | character varying(256) | | |
policy_id | character varying(64) | | |
policy_type | character varying(64) | | |
policy_name | character varying(256) | | |
resource_id | character varying(64) | | |
resource_type | character varying(64) | | |
resource_name | character varying(256) | | |
tags | text | | |
affinity_agent_id | character varying(64) | | not null |
affinity_cluster_id | character varying(64) | | not null |
argument | bytea | | |
context | bytea | | |
progress | integer | | |
progress_total | integer | | |
assigned_agent_id | character varying(64) | | |
assigned_agent_cluster_id | character varying(64) | | |
enqueue_time_str | character varying(64) | | |
enqueue_time_ns | bigint | | not null |
assign_time_str | character varying(64) | | |
assign_time_ns | bigint | | |
start_time_str | character varying(64) | | |
start_time_ns | bigint | | |
update_time_str | character varying(64) | | not null |
update_time_ns | bigint | | not null |
completion_time_str | character varying(64) | | |
completion_time_ns | bigint | | |
result_code | integer | | |
result_error | bytea | | |
result_warnings | bytea | | |
result_payload | bytea | | |
const_val | integer | | |
Indexes:
"test_db_bench_1_pkey" PRIMARY KEY, btree (id)
"test_db_bench_1_idx_completion_time_ns_1" btree (completion_time_ns)
"test_db_bench_1_idx_cti_entity_uuid_2" btree (cti_entity_uuid)
"test_db_bench_1_idx_enqueue_time_ns_10" btree (enqueue_time_ns)
"test_db_bench_1_idx_euc_id_4" btree (euc_id)
"test_db_bench_1_idx_policy_id_12" btree (policy_id)
"test_db_bench_1_idx_queue_18" btree (queue, type, tenant_id)
"test_db_bench_1_idx_queue_19" btree (queue, type, euc_id)
"test_db_bench_1_idx_queue_5" btree (queue, state, affinity_agent_id, affinity_cluster_id, tenant_id, priority)
"test_db_bench_1_idx_queue_6" btree (queue, state, affinity_agent_id, affinity_cluster_id, euc_id, priority)
"test_db_bench_1_idx_resource_id_11" btree (resource_id)
"test_db_bench_1_idx_resource_id_14" btree (resource_id, enqueue_time_ns)
"test_db_bench_1_idx_result_code_13" btree (result_code)
"test_db_bench_1_idx_start_time_ns_9" btree (start_time_ns)
"test_db_bench_1_idx_state_8" btree (state, completion_time_ns)
"test_db_bench_1_idx_tenant_id_3" btree (tenant_id)
"test_db_bench_1_idx_type_15" btree (type)
"test_db_bench_1_idx_type_16" btree (type, tenant_id, enqueue_time_ns)
"test_db_bench_1_idx_type_17" btree (type, euc_id, enqueue_time_ns)
"test_db_bench_1_idx_update_time_ns_7" btree (update_time_ns)
"test_db_bench_1_idx_uuid_0" btree (uuid)
"test_db_bench_1_uuid_key" UNIQUE CONSTRAINT, btree (uuid)
-- test_db_bench_tenants
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-----------------------+-------+--------+-------------+---------------+---------+-------------
public | test_db_bench_tenants | table | dbuser | permanent | heap | 8432 kB |
Column | Type | Collation | Nullable | Default
-------------------+------------------------+-----------+----------+---------
id | bigint | | not null |
uuid | character varying(36) | | not null |
name | character varying(255) | | not null |
kind | character(1) | | not null |
is_deleted | boolean | | not null | false
parent_id | bigint | | not null |
parent_has_access | boolean | | not null | true
nesting_level | smallint | | not null |
Indexes:
"test_db_bench_tenants_pkey" PRIMARY KEY, btree (id)
"test_db_bench_tenants_uuid" UNIQUE CONSTRAINT, btree (uuid)
-- test_db_bench_tenant_closure
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------------------------+-------+--------+-------------+---------------+-------+-------------
public | test_db_bench_tenant_closure | table | dbuser | permanent | heap | 22 MB |
Column | Type | Collation | Nullable | Default
-------------+--------------+-----------+----------+---------
parent_id | bigint | | not null |
child_id | bigint | | not null |
parent_kind | character(1) | | not null |
barrier | smallint | | not null | 0
Indexes:
"test_db_bench_tenant_closure_pkey" PRIMARY KEY, btree (parent_id, child_id)
"cybercache_tenants_closure_child_id_idx" btree (child_id)
Postgresql version: 15.3 (Debian 15.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
And just in case it matters, this is an experimental setup, so Postgresql running in Docker.
Thank you.
--
Mikhail
