explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Tp4f

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 539.787 ↑ 19.0 5 1

Limit (cost=153,122.98..153,124.64 rows=95 width=4) (actual time=539.783..539.787 rows=5 loops=1)

  • Buffers: shared hit=64,759 read=2,283
  • I/O Timings: read=4.546
2.          

CTE regions_ids_and_descendants

3. 1.106 267.213 ↓ 71.9 7,188 1

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

  • Buffers: shared hit=45,882
4.          

Initplan (for ProjectSet)

5. 0.659 266.106 ↑ 13.0 7,186 1

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

  • Buffers: shared hit=45,882
6. 2.460 265.447 ↑ 13.0 7,186 1

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

  • Sort Key: graph.end_node
  • Sort Method: quicksort Memory: 529kB
  • Buffers: shared hit=45,882
7. 4.556 262.987 ↑ 13.0 7,186 1

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

  • Group Key: graph.end_node
  • Buffers: shared hit=45,882
8. 2.038 258.431 ↑ 26.2 7,616 1

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

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=45,882
9. 256.393 256.393 ↑ 32.7 2,539 3 / 3

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

  • Index Cond: (start_node = ANY ('{0,1211}'::integer[]))
  • Filter: ((end_node IS NOT NULL) AND (distance >= 1) AND (distance <= 2))
  • Rows Removed by Filter: 945,153
  • Buffers: shared hit=45,879
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.026 0.122 ↓ 1.4 135 1

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

  • Buffers: shared hit=5
13.          

Initplan (for ProjectSet)

14. 0.011 0.095 ↓ 2.6 134 1

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

  • Buffers: shared hit=5
15. 0.018 0.084 ↓ 2.6 134 1

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

  • Buffers: shared hit=5
16. 0.066 0.066 ↓ 2.6 134 1

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

  • Sort Key: graph_1.end_node
  • Sort Method: quicksort Memory: 31kB
  • Buffers: shared hit=5
  • Index Cond: (start_node = 1,178)
  • Filter: ((end_node IS NOT NULL) AND (distance >= 1) AND (distance <= 2))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=5
17. 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)

18.          

CTE available

19. 0.002 539.654 ↑ 19.0 5 1

Limit (cost=46,370.81..46,372.90 rows=95 width=4) (actual time=539.494..539.654 rows=5 loops=1)

  • Buffers: shared hit=64,712 read=2,283
  • I/O Timings: read=4.546
20. 0.089 539.652 ↑ 19.0 5 1

Unique (cost=46,370.81..46,372.90 rows=95 width=4) (actual time=539.494..539.652 rows=5 loops=1)

  • Buffers: shared hit=64,712 read=2,283
  • I/O Timings: read=4.546
21. 0.339 539.563 ↓ 3.1 1,283 1

Sort (cost=46,370.81..46,371.85 rows=417 width=4) (actual time=539.493..539.563 rows=1,283 loops=1)

  • Sort Key: series.source_id
  • Sort Method: quicksort Memory: 109kB
  • Buffers: shared hit=64,712 read=2,283
  • I/O Timings: read=4.546
22. 17.731 539.224 ↓ 3.1 1,283 1

Hash Semi Join (cost=6.50..46,352.66 rows=417 width=4) (actual time=284.670..539.224 rows=1,283 loops=1)

  • Hash Cond: (series.partner_region_id = partnerregions_ids_and_descendants.ids)
  • Buffers: shared hit=64,712 read=2,283
  • I/O Timings: read=4.546
23. 45.113 521.326 ↓ 187.7 217,736 1

Hash Semi Join (cost=3.25..46,341.73 rows=1,160 width=8) (actual time=269.484..521.326 rows=217,736 loops=1)

  • Hash Cond: (series.region_id = regions_ids_and_descendants.ids)
  • Buffers: shared hit=64,707 read=2,283
  • I/O Timings: read=4.546
24. 206.795 206.795 ↑ 1.0 217,755 1

Sample Scan on series (cost=0.00..45,750.53 rows=219,066 width=12) (actual time=0.055..206.795 rows=217,755 loops=1)

  • Sampling: system ('3'::real) REPEATABLE ('4'::double precision)
  • Filter: (metric_id = 41,078)
  • Rows Removed by Filter: 1,550,965
  • Buffers: shared hit=18,825 read=2,283
  • I/O Timings: read=4.546
25. 0.843 269.418 ↓ 71.9 7,188 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=269.417..269.418 rows=7,188 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 317kB
  • Buffers: shared hit=45,882
26. 268.575 268.575 ↓ 71.9 7,188 1

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

  • Buffers: shared hit=45,882
27. 0.020 0.167 ↓ 1.4 135 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=5
28. 0.147 0.147 ↓ 1.4 135 1

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

  • Buffers: shared hit=5
29. 0.002 539.786 ↑ 19.0 5 1

Subquery Scan on ranked (cost=50.22..51.88 rows=95 width=4) (actual time=539.783..539.786 rows=5 loops=1)

  • Buffers: shared hit=64,759 read=2,283
  • I/O Timings: read=4.546
30. 0.000 539.784 ↑ 19.0 5 1

Limit (cost=50.22..50.93 rows=95 width=19) (actual time=539.782..539.784 rows=5 loops=1)

  • Buffers: shared hit=64,759 read=2,283
  • I/O Timings: read=4.546
31. 0.003 539.784 ↑ 19.0 5 1

Unique (cost=50.22..50.93 rows=95 width=19) (actual time=539.781..539.784 rows=5 loops=1)

  • Buffers: shared hit=64,759 read=2,283
  • I/O Timings: read=4.546
32. 0.036 539.781 ↑ 19.0 5 1

Sort (cost=50.22..50.46 rows=95 width=19) (actual time=539.781..539.781 rows=5 loops=1)

  • Sort Key: sources.name, sources.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=64,759 read=2,283
  • I/O Timings: read=4.546
33. 0.033 539.745 ↑ 19.0 5 1

Hash Semi Join (cost=3.09..47.10 rows=95 width=19) (actual time=539.681..539.745 rows=5 loops=1)

  • Hash Cond: (sources.id = available.ids)
  • Buffers: shared hit=64,753 read=2,283
  • I/O Timings: read=4.546
34. 0.046 0.046 ↑ 1.0 155 1

Seq Scan on sources (cost=0.00..42.55 rows=155 width=19) (actual time=0.003..0.046 rows=155 loops=1)

  • Buffers: shared hit=41
35. 0.008 539.666 ↑ 19.0 5 1

Hash (cost=1.90..1.90 rows=95 width=4) (actual time=539.666..539.666 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=64,712 read=2,283
  • I/O Timings: read=4.546
36. 539.658 539.658 ↑ 19.0 5 1

CTE Scan on available (cost=0.00..1.90 rows=95 width=4) (actual time=539.496..539.658 rows=5 loops=1)

  • Buffers: shared hit=64,712 read=2,283
  • I/O Timings: read=4.546