explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DSHj

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 670.488 ↑ 316.6 63 1

Finalize HashAggregate (cost=39,223.04..39,422.50 rows=19,946 width=79) (actual time=670.363..670.488 rows=63 loops=1)

  • Group Key: (flows.path[1]), nodes.name, nodes.geo_id, node_properties.is_domestic_consumption, flows.year
2. 37.960 679.620 ↑ 87.9 189 1

Gather (cost=37,228.40..38,973.71 rows=16,622 width=79) (actual time=669.523..679.620 rows=189 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 18.353 641.660 ↑ 131.9 63 3

Partial HashAggregate (cost=36,228.40..36,311.51 rows=8,311 width=79) (actual time=641.572..641.660 rows=63 loops=3)

  • Group Key: flows.path[1], nodes.name, nodes.geo_id, node_properties.is_domestic_consumption, flows.year
4. 314.465 623.307 ↓ 2.1 17,374 3

Nested Loop (cost=4,878.93..36,103.74 rows=8,311 width=79) (actual time=256.711..623.307 rows=17,374 loops=3)

5. 20.840 308.825 ↓ 2.1 17,374 3

Hash Join (cost=4,878.50..10,586.88 rows=8,282 width=128) (actual time=256.517..308.825 rows=17,374 loops=3)

  • Hash Cond: (flows.path[1] = nodes.id)
6. 21.372 43.008 ↑ 1.2 17,497 3

Parallel Bitmap Heap Scan on partitioned_flows_1 flows (cost=972.76..6,517.62 rows=21,524 width=63) (actual time=9.400..43.008 rows=17,497 loops=3)

  • Recheck Cond: (29138 = path[6])
  • Filter: (context_id = 1)
  • Heap Blocks: exact=1619
7. 21.636 21.636 ↓ 1.0 52,490 1

Bitmap Index Scan on index_partitioned_flows_1_on_path_6 (cost=0.00..959.85 rows=51,657 width=0) (actual time=21.636..21.636 rows=52,490 loops=1)

  • Index Cond: (29138 = path[6])
8. 41.859 244.977 ↓ 1.8 42,440 3

Hash (cost=3,604.47..3,604.47 rows=24,101 width=73) (actual time=244.977..244.977 rows=42,440 loops=3)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3502kB
9. 42.899 203.118 ↓ 1.8 42,440 3

Hash Join (cost=1,887.90..3,604.47 rows=24,101 width=73) (actual time=124.967..203.118 rows=42,440 loops=3)

  • Hash Cond: (node_properties.node_id = nodes.id)
10. 47.180 47.180 ↑ 1.1 42,508 3

Seq Scan on node_properties (cost=0.00..1,590.04 rows=48,202 width=5) (actual time=10.674..47.180 rows=42,508 loops=3)

  • Filter: (NOT is_domestic_consumption)
  • Rows Removed by Filter: 4
11. 68.558 113.039 ↓ 1.4 42,444 3

Hash (cost=1,496.40..1,496.40 rows=31,320 width=68) (actual time=113.039..113.039 rows=42,444 loops=3)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3171kB
12. 44.481 44.481 ↓ 1.4 42,444 3

Seq Scan on nodes (cost=0.00..1,496.40 rows=31,320 width=68) (actual time=7.476..44.481 rows=42,444 loops=3)

  • Filter: (NOT is_unknown)
  • Rows Removed by Filter: 68
13. 0.017 0.017 ↑ 1.0 1 52,123

Index Scan using index_partitioned_flow_quants_1_on_flow_id on partitioned_flow_quants_1 (cost=0.43..3.07 rows=1 width=12) (actual time=0.014..0.017 rows=1 loops=52,123)

  • Index Cond: (flow_id = flows.id)
  • Filter: (quant_id = 1)
  • Rows Removed by Filter: 7