explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bbX3

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 12,346.480 ↑ 19.0 5 1

Limit (cost=399,419.72..399,489.25 rows=95 width=4) (actual time=12,341.791..12,346.480 rows=5 loops=1)

  • Output: series.source_id
  • Buffers: shared hit=9,377,646
2.          

CTE regions_ids_and_descendants

3. 1.049 203.520 ↓ 71.9 7,188 1

ProjectSet (cost=106,668.79..106,669.31 rows=100 width=4) (actual time=202.992..203.520 rows=7,188 loops=1)

  • Output: unnest(array_cat($1, '{0,1211}'::integer[]))
  • Buffers: shared hit=44,832
4.          

Initplan (for ProjectSet)

5. 0.569 202.470 ↑ 13.0 7,186 1

Limit (cost=106,436.05..106,668.79 rows=93,098 width=4) (actual time=201.365..202.470 rows=7,186 loops=1)

  • Output: graph.end_node
  • Buffers: shared hit=44,832
6. 2.240 201.901 ↑ 13.0 7,186 1

Sort (cost=106,436.05..106,668.79 rows=93,098 width=4) (actual time=201.364..201.901 rows=7,186 loops=1)

  • Output: graph.end_node
  • Sort Key: graph.end_node
  • Sort Method: quicksort Memory: 529kB
  • Buffers: shared hit=44,832
7. 4.627 199.661 ↑ 13.0 7,186 1

HashAggregate (cost=97,821.47..98,752.45 rows=93,098 width=4) (actual time=197.794..199.661 rows=7,186 loops=1)

  • Output: graph.end_node
  • Group Key: graph.end_node
  • Buffers: shared hit=44,832
8. 1.267 195.034 ↑ 26.2 7,616 1

Gather (cost=1,000.43..97,322.93 rows=199,418 width=4) (actual time=0.289..195.034 rows=7,616 loops=1)

  • Output: graph.end_node
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=44,832
9. 193.767 193.767 ↑ 32.7 2,539 3 / 3

Parallel Index Scan using ontology_regions_full_graph_paths_start_index on ontology.regions_full_graph_paths graph (cost=0.43..76,381.13 rows=83,091 width=4) (actual time=0.014..193.767 rows=2,539 loops=3)

  • Output: graph.end_node
  • Index Cond: (graph.start_node = ANY ('{0,1211}'::integer[]))
  • Filter: ((graph.end_node IS NOT NULL) AND (graph.distance >= 1) AND (graph.distance <= 2))
  • Rows Removed by Filter: 945,153
  • Buffers: shared hit=44,832
  • Worker 0: actual time=0.016..193.844 rows=1,453 loops=1
  • Buffers: shared hit=11,664
  • Worker 1: actual time=0.011..194.377 rows=3,431 loops=1
  • Buffers: shared hit=21,573
10. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

11.          

CTE partnerregions_ids_and_descendants

12. 0.024 0.123 ↓ 1.4 135 1

ProjectSet (cost=30.03..30.55 rows=100 width=4) (actual time=0.113..0.123 rows=135 loops=1)

  • Output: unnest(array_cat($3, '{1178}'::integer[]))
  • Buffers: shared hit=5
13.          

Initplan (for ProjectSet)

14. 0.011 0.098 ↓ 2.6 134 1

Limit (cost=29.77..30.03 rows=51 width=4) (actual time=0.063..0.098 rows=134 loops=1)

  • Output: graph_1.end_node
  • Buffers: shared hit=5
15. 0.019 0.087 ↓ 2.6 134 1

Unique (cost=29.77..30.03 rows=51 width=4) (actual time=0.062..0.087 rows=134 loops=1)

  • Output: graph_1.end_node
  • Buffers: shared hit=5
16. 0.023 0.068 ↓ 2.6 134 1

Sort (cost=29.77..29.90 rows=51 width=4) (actual time=0.062..0.068 rows=134 loops=1)

  • Output: graph_1.end_node
  • Sort Key: graph_1.end_node
  • Sort Method: quicksort Memory: 31kB
  • Buffers: shared hit=5
17. 0.045 0.045 ↓ 2.6 134 1

Index Scan using ontology_regions_full_graph_paths_start_index on ontology.regions_full_graph_paths graph_1 (cost=0.43..28.33 rows=51 width=4) (actual time=0.021..0.045 rows=134 loops=1)

  • Output: graph_1.end_node
  • Index Cond: (graph_1.start_node = 1,178)
  • Filter: ((graph_1.end_node IS NOT NULL) AND (graph_1.distance >= 1) AND (graph_1.distance <= 2))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=5
18. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

19. 2.705 12,346.478 ↑ 19.0 5 1

Unique (cost=292,719.86..292,789.39 rows=95 width=4) (actual time=12,341.790..12,346.478 rows=5 loops=1)

  • Output: series.source_id
  • Buffers: shared hit=9,377,646
20. 10.741 12,343.773 ↓ 3.0 41,397 1

Sort (cost=292,719.86..292,754.62 rows=13,907 width=4) (actual time=12,341.789..12,343.773 rows=41,397 loops=1)

  • Output: series.source_id
  • Sort Key: series.source_id
  • Sort Method: quicksort Memory: 3,477kB
  • Buffers: shared hit=9,377,646
21. 520.696 12,333.032 ↓ 3.0 41,397 1

Hash Semi Join (cost=6.06..291,762.81 rows=13,907 width=4) (actual time=213.401..12,333.032 rows=41,397 loops=1)

  • Output: series.source_id
  • Hash Cond: (series.partner_region_id = partnerregions_ids_and_descendants.ids)
  • Buffers: shared hit=9,377,646
22. 865.630 11,812.171 ↓ 189.4 7,321,527 1

Nested Loop (cost=2.81..291,503.36 rows=38,662 width=8) (actual time=213.110..11,812.171 rows=7,321,527 loops=1)

  • Output: series.source_id, series.partner_region_id
  • Buffers: shared hit=9,377,641
23. 4.330 209.163 ↓ 71.9 7,187 1

HashAggregate (cost=2.25..3.25 rows=100 width=4) (actual time=206.638..209.163 rows=7,187 loops=1)

  • Output: regions_ids_and_descendants.ids
  • Group Key: regions_ids_and_descendants.ids
  • Buffers: shared hit=44,832
24. 204.833 204.833 ↓ 71.9 7,188 1

CTE Scan on regions_ids_and_descendants (cost=0.00..2.00 rows=100 width=4) (actual time=202.994..204.833 rows=7,188 loops=1)

  • Output: regions_ids_and_descendants.ids
  • Buffers: shared hit=44,832
25. 10,737.378 10,737.378 ↓ 2.6 1,019 7,187

Index Scan using series_region_id on data.series (cost=0.56..2,911.13 rows=387 width=12) (actual time=0.299..1.494 rows=1,019 loops=7,187)

  • Output: series.metric_id, series.item_id, series.region_id, series.partner_region_id, series.frequency_id, series.source_id, series.start_date, series.end_date, series.data_count, series.hierarchy_id, series.time_stamp
  • Index Cond: (series.region_id = regions_ids_and_descendants.ids)
  • Filter: (series.metric_id = 41,078)
  • Rows Removed by Filter: 3,703
  • Buffers: shared hit=9,332,809
26. 0.014 0.165 ↓ 1.4 135 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.165..0.165 rows=135 loops=1)

  • Output: partnerregions_ids_and_descendants.ids
  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=5
27. 0.151 0.151 ↓ 1.4 135 1

CTE Scan on partnerregions_ids_and_descendants (cost=0.00..2.00 rows=100 width=4) (actual time=0.114..0.151 rows=135 loops=1)

  • Output: partnerregions_ids_and_descendants.ids
  • Buffers: shared hit=5