Help to understand the slow running qury

shafeer

Hello,

I have two queries Query-1 running on a table with 2B rows which completes within 3 seconds.
Other Query-2 running on a table with 1B rows takes around 30 seconds to complete.

Please help me to understand the cause. Query and Anayze output has given below.

Query-1:

SELECT
a.node_short_name,
a.netif_name,
split_part(a.netif_alias, ':', 3) AS pw,
split_part(split_part(a.netif_alias, ':', 2), '-', 2) AS location,
split_part(a.netif_alias, ':', 4) AS product,
c.availability_pct::NUMERIC(18, 2) AS availability
FROM
mf_shared_provider_default.nom_interface_health c, mf_shared_provider_default.nom_entity_interface_latest a
where
a.netif_unique_id = c.netif_unique_id
and a.netif_unique_id in ('d946b79e-7d9a-4575-91c3-893172f32a5b' )
and to_timestamp(c.timestamp_utc_s) >= (NOW() - INTERVAL '15 minutes')

Query-2:

SELECT
a.node_short_name,
a.netif_name,
split_part(a.netif_alias, ':', 3) AS pw,
split_part(split_part(a.netif_alias, ':', 2), '-', 2) AS location,
split_part(a.netif_alias, ':', 4) AS product,
c.node_availability_pct::NUMERIC(18, 2) AS availability
FROM
mf_shared_provider_default.nom_component_health c, mf_shared_provider_default.nom_entity_interface_latest a
where
a.node_unique_id = c.node_unique_id
and a.netif_unique_id in ('d946b79e-7d9a-4575-91c3-893172f32a5b' )
and to_timestamp(c.timestamp_utc_s) >= (NOW() - INTERVAL '15 minutes')

==========================

Query-1:

Access Path:
+-JOIN HASH [Cost: 4M, Rows: 12M (NO STATISTICS)] (PATH ID: 1) Inner (BROADCAST)
| Join Cond: (lsampleci2.netif_unique_id = c.netif_unique_id)
| Execute on: All Nodes
| +-- Outer -> SELECT [Cost: 3M, Rows: 12M (NO STATISTICS)] (PATH ID: 2)
| | Filter: (lsampleci2.row_num = 1)
| | Filter: (lsampleci2.operation <> 'remove')
| | Filter: (lsampleci2.netif_unique_id = 'd946b79e-7d9a-4575-91c3-893172f32a5b'::uuid)
| | Execute on: All Nodes
| | +---> ANALYTICAL [Cost: 3M, Rows: 12M (NO STATISTICS)] (PATH ID: 3)
| | | Analytic Group
| | | Functions: row_number()
| | | Group Local Resegment: nom_entity_interface_raw.entity_unique_id, nom_entity_interface_raw.entity_producer_instance_id
| | | Group Sort: nom_entity_interface_raw.entity_unique_id ASC, nom_entity_interface_raw.entity_producer_instance_id ASC, nom_entity_interface_raw.timestamp_utc_s DESC NULLS FIRST
| | | Execute on: All Nodes
| | | Runtime Filter: (SIP1(HashJoin): lsampleci2.netif_unique_id)
| | | +---> STORAGE ACCESS for nom_entity_interface_raw [Cost: 192K, Rows: 12M (NO STATISTICS)] (PATH ID: 4)
| | | | Projection: mf_shared_provider_default.nom_entity_interface_raw_b0
| | | | Materialize: nom_entity_interface_raw.entity_unique_id, nom_entity_interface_raw.timestamp_utc_s, nom_entity_interface_raw.entity_producer_instance_id, nom_entity_interface_raw.operation, nom_entity_interface_raw.netif_name, nom_entity_interface_raw.netif_alias, nom_entity_interface_raw.netif_unique_id, nom_entity_interface_raw.node_short_name
| | | | Execute on: All Nodes
| +-- Inner -> STORAGE ACCESS for c [Cost: 49K, Rows: 10M (NO STATISTICS)] (PATH ID: 5)
| | Projection: mf_shared_provider_default.nom_interface_health_b0
| | Materialize: c.netif_unique_id, c.availability_pct
| | Filter: (c.netif_unique_id = 'd946b79e-7d9a-4575-91c3-893172f32a5b'::uuid)
| | Filter: ((to_timestamptz((c.timestamp_utc_s)::float))::timestamp >= '2024-11-15 17:59:33.074408+03'::timestamptz)
| | Execute on: Query Initiator

Query-2:

+-JOIN HASH [Cost: 24M, Rows: 1B (NO STATISTICS)] (PATH ID: 1) Outer (RESEGMENT)(LOCAL ROUND ROBIN) Inner (RESEGMENT)
| Join Cond: (lsampleci2.node_unique_id = c.node_unique_id)
| Execute on: All Nodes
| +-- Outer -> STORAGE ACCESS for c [Cost: 2M, Rows: 1B (NO STATISTICS)] (PATH ID: 2)
| | Projection: mf_shared_provider_default.nom_component_health_b0
| | Materialize: c.node_unique_id, c.node_availability_pct
| | Filter: ((to_timestamptz((c.timestamp_utc_s)::float))::timestamp >= '2024-11-15 18:00:55.087099+03'::timestamptz)
| | Filter: (c.node_unique_id IS NOT NULL)
| | Execute on: All Nodes
| +-- Inner -> SELECT [Cost: 4M, Rows: 12M (NO STATISTICS)] (PATH ID: 3)
| | Filter: (lsampleci2.row_num = 1)
| | Filter: (lsampleci2.operation <> 'remove')
| | Filter: (lsampleci2.netif_unique_id = 'd946b79e-7d9a-4575-91c3-893172f32a5b'::uuid)
| | Execute on: All Nodes
| | +---> ANALYTICAL [Cost: 4M, Rows: 12M (NO STATISTICS)] (PATH ID: 4)
| | | Analytic Group
| | | Functions: row_number()
| | | Group Local Resegment: nom_entity_interface_raw.entity_unique_id, nom_entity_interface_raw.entity_producer_instance_id
| | | Group Sort: nom_entity_interface_raw.entity_unique_id ASC, nom_entity_interface_raw.entity_producer_instance_id ASC, nom_entity_interface_raw.timestamp_utc_s DESC NULLS FIRST
| | | Execute on: All Nodes
| | | +---> STORAGE ACCESS for nom_entity_interface_raw [Cost: 200K, Rows: 12M (NO STATISTICS)] (PATH ID: 5)
| | | | Projection: mf_shared_provider_default.nom_entity_interface_raw_b0
| | | | Materialize: nom_entity_interface_raw.entity_unique_id, nom_entity_interface_raw.timestamp_utc_s, nom_entity_interface_raw.entity_producer_instance_id, nom_entity_interface_raw.operation, nom_entity_interface_raw.netif_name, nom_entity_interface_raw.netif_alias, nom_entity_interface_raw.netif_unique_id, nom_entity_interface_raw.node_short_name, nom_entity_interface_raw.node_unique_id
| | | | Execute on: All Nodes

SergeB

Query 1 and Query 2 are not joining on the same column:

Q1 where
a.netif_unique_id = c.netif_unique_id
and a.netif_unique_id in ('d946b79e-7d9a-4575-91c3-893172f32a5b' )

Q2 where
a.node_unique_id = c.node_unique_id
and a.netif_unique_id in ('d946b79e-7d9a-4575-91c3-893172f32a5b' )

shafeer

Thanks sergB

Projection for that table is on another column "component_unique_id".
My requirement is to query on column "node_unique_id". Is it possible to add project for another column as well?

Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]

mf_shared_provider_default.nom_component_health_b1 [Segmented: Yes] [Seg Cols: "mf_shared_provider_default.nom_component_health.component_unique_id"] [K: 1] [mf_shared_provider_default.nom_component_health_b0] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
mf_shared_provider_default.nom_component_health_b0 [Segmented: Yes] [Seg Cols: "mf_shared_provider_default.nom_component_health.component_unique_id"] [K: 1] [mf_shared_provider_default.nom_component_health_b1] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]