explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DN4

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.100 21,155.743 ↑ 1.0 1 1

GroupAggregate (cost=14,070.97..14,071.05 rows=1 width=12) (actual time=21,155.742..21,155.743 rows=1 loops=1)

  • Group Key: wabadaba.programme_id
2.          

CTE weights_in_target

3. 49.664 11,551.837 ↓ 511.0 18,397 1

Nested Loop (cost=795.71..1,095.67 rows=36 width=12) (actual time=11,420.632..11,551.837 rows=18,397 loops=1)

4. 29.394 11,465.379 ↓ 511.0 18,397 1

Unique (cost=795.29..795.56 rows=36 width=20) (actual time=11,420.532..11,465.379 rows=18,397 loops=1)

5. 34.328 11,435.985 ↓ 514.3 18,514 1

Sort (cost=795.29..795.38 rows=36 width=20) (actual time=11,420.529..11,435.985 rows=18,514 loops=1)

  • Sort Key: d.date, d.person_id
  • Sort Method: quicksort Memory: 2215kB
6. 3,488.335 11,401.657 ↓ 514.3 18,514 1

HashAggregate (cost=705.28..794.36 rows=36 width=20) (actual time=11,314.242..11,401.657 rows=18,514 loops=1)

  • Group Key: d.date, d.person_id, c_1.id, s2.subcondition_count
  • Filter: (count(s.id) = s2.subcondition_count)
  • Rows Removed by Filter: 456810
7. 4,947.810 7,913.322 ↓ 465.3 3,315,812 1

Nested Loop (cost=40.93..616.21 rows=7,126 width=24) (actual time=0.294..7,913.322 rows=3,315,812 loops=1)

8. 0.098 0.472 ↓ 40.0 40 1

Nested Loop (cost=40.49..54.44 rows=1 width=28) (actual time=0.224..0.472 rows=40 loops=1)

9. 0.021 0.278 ↓ 4.0 4 1

Hash Join (cost=40.21..45.89 rows=1 width=16) (actual time=0.215..0.278 rows=4 loops=1)

  • Hash Cond: (c_1.id = s2.condition_id)
10. 0.065 0.065 ↑ 1.0 4 1

Seq Scan on condition c_1 (cost=0.00..5.66 rows=4 width=4) (actual time=0.015..0.065 rows=4 loops=1)

  • Filter: (target_id = 1)
  • Rows Removed by Filter: 289
11. 0.006 0.192 ↑ 1.0 4 1

Hash (cost=40.16..40.16 rows=4 width=12) (actual time=0.191..0.192 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.009 0.186 ↑ 1.0 4 1

Subquery Scan on s2 (cost=40.08..40.16 rows=4 width=12) (actual time=0.176..0.186 rows=4 loops=1)

13. 0.043 0.177 ↑ 1.0 4 1

HashAggregate (cost=40.08..40.12 rows=4 width=12) (actual time=0.173..0.177 rows=4 loops=1)

  • Group Key: c_2.id
14. 0.067 0.134 ↑ 1.0 40 1

Nested Loop (cost=0.28..39.88 rows=40 width=8) (actual time=0.016..0.134 rows=40 loops=1)

15. 0.023 0.023 ↑ 1.0 4 1

Seq Scan on condition c_2 (cost=0.00..5.66 rows=4 width=4) (actual time=0.007..0.023 rows=4 loops=1)

  • Filter: (target_id = 1)
  • Rows Removed by Filter: 289
16. 0.044 0.044 ↑ 1.0 10 4

Index Scan using subcondition_condition_id_idx on subcondition s_1 (cost=0.28..8.45 rows=10 width=8) (actual time=0.002..0.011 rows=10 loops=4)

  • Index Cond: (condition_id = c_2.id)
17. 0.096 0.096 ↑ 1.0 10 4

Index Scan using subcondition_condition_id_idx on subcondition s (cost=0.28..8.45 rows=10 width=20) (actual time=0.005..0.024 rows=10 loops=4)

  • Index Cond: (condition_id = c_1.id)
18. 2,965.040 2,965.040 ↓ 6.3 82,895 40

Index Scan using demography_demography_value_idx on demography d (cost=0.43..429.74 rows=13,203 width=16) (actual time=0.044..74.126 rows=82,895 loops=40)

  • Index Cond: ((demography = s.demography) AND (value >= s."from") AND (value <= s."to"))
19. 36.794 36.794 ↑ 1.0 1 18,397

Index Scan using weight_pkey on weight w (cost=0.42..8.33 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=18,397)

  • Index Cond: ((person_id = d.person_id) AND (date = d.date))
20. 0.186 21,155.643 ↓ 119.0 119 1

Subquery Scan on wabadaba (cost=12,975.30..12,975.36 rows=1 width=12) (actual time=21,155.217..21,155.643 rows=119 loops=1)

  • Filter: ((wabadaba.programme_data ->> 'name'::text) = 'Gwiazdy Tańczą na Lodzie'::text)
21. 0.249 21,155.457 ↓ 119.0 119 1

WindowAgg (cost=12,975.30..12,975.34 rows=1 width=190) (actual time=21,155.213..21,155.457 rows=119 loops=1)

22. 0.282 21,155.208 ↓ 119.0 119 1

Sort (cost=12,975.30..12,975.30 rows=1 width=170) (actual time=21,155.104..21,155.208 rows=119 loops=1)

  • Sort Key: pro.id, ((e.value ->> 'date'::text)), ((e.value ->> 'channel'::text)), ((e.value ->> 'minute_from'::text)), ((e.value ->> 'minute_to'::text))
  • Sort Method: quicksort Memory: 86kB
23. 0.377 21,154.926 ↓ 119.0 119 1

GroupAggregate (cost=12,975.24..12,975.29 rows=1 width=170) (actual time=21,154.369..21,154.926 rows=119 loops=1)

  • Group Key: pro.id, (sum(p.weight)), c.minute, ((e.value ->> 'date'::text)), ((e.value ->> 'channel'::text)), ((e.value ->> 'minute_from'::text)), ((e.value ->> 'minute_to'::text))
24. 0.588 21,154.549 ↓ 261.0 261 1

Sort (cost=12,975.24..12,975.25 rows=1 width=166) (actual time=21,154.353..21,154.549 rows=261 loops=1)

  • Sort Key: pro.id, (sum(p.weight)), c.minute, ((e.value ->> 'date'::text)), ((e.value ->> 'channel'::text)), ((e.value ->> 'minute_from'::text)), ((e.value ->> 'minute_to'::text))
  • Sort Method: quicksort Memory: 159kB
25. 0.506 21,153.961 ↓ 261.0 261 1

Nested Loop (cost=8.93..12,975.23 rows=1 width=166) (actual time=21,127.596..21,153.961 rows=261 loops=1)

26. 106.811 106.811 ↓ 2.0 2 1

Seq Scan on programme pro (cost=0.00..2.50 rows=1 width=22) (actual time=106.804..106.811 rows=2 loops=1)

  • Filter: ((data ->> 'name'::text) = 'Gwiazdy Tańczą na Lodzie'::text)
  • Rows Removed by Filter: 100
27. 24.066 21,046.644 ↓ 130.0 130 2

Nested Loop (cost=8.93..12,972.71 rows=1 width=48) (actual time=10,510.388..10,523.322 rows=130 loops=2)

  • Join Filter: (c.date = p.date)
  • Rows Removed by Join Filter: 15530
28. 16.242 11,598.498 ↓ 3.3 120 2

HashAggregate (cost=0.90..1.26 rows=36 width=12) (actual time=5,799.138..5,799.249 rows=120 loops=2)

  • Group Key: p.date
29. 11,582.256 11,582.256 ↓ 511.0 18,397 1

CTE Scan on weights_in_target p (cost=0.00..0.72 rows=36 width=8) (actual time=11,420.635..11,582.256 rows=18,397 loops=1)

30. 23.734 9,424.080 ↓ 65.0 130 240

Materialize (cost=8.03..12,970.02 rows=2 width=48) (actual time=19.662..39.267 rows=130 loops=240)

31. 59.040 9,400.346 ↓ 65.0 130 2

Nested Loop (cost=8.03..12,970.01 rows=2 width=48) (actual time=2,359.303..4,700.173 rows=130 loops=2)

32. 32.424 32.424 ↓ 511.0 18,397 2

CTE Scan on weights_in_target per (cost=0.00..0.72 rows=36 width=12) (actual time=0.001..16.212 rows=18,397 loops=2)

33. 3,385.175 9,308.882 ↓ 0.0 0 36,794

Hash Join (cost=8.03..368.05 rows=1 width=44) (actual time=0.253..0.253 rows=0 loops=36,794)

  • Hash Cond: (((c.channel)::text = (e.value ->> 'channel'::text)) AND (c.date = ((e.value ->> 'date'::text))::date))
  • Join Filter: ((c.minute >= ((e.value ->> 'minute_from'::text))::integer) AND (c.minute <= ((e.value ->> 'minute_to'::text))::integer))
  • Rows Removed by Join Filter: 0
34. 5,482.157 5,923.673 ↓ 1.0 95 36,793

Bitmap Heap Scan on contact c (cost=5.52..364.23 rows=94 width=14) (actual time=0.020..0.161 rows=95 loops=36,793)

  • Recheck Cond: ((person_id = per.person_id) AND (date = per.date))
  • Heap Blocks: exact=3396414
35. 441.516 441.516 ↓ 1.0 95 36,793

Bitmap Index Scan on contact_pkey (cost=0.00..5.50 rows=94 width=0) (actual time=0.012..0.012 rows=95 loops=36,793)

  • Index Cond: ((person_id = per.person_id) AND (date = per.date))
36. 0.016 0.034 ↑ 100.0 1 2

Hash (cost=1.00..1.00 rows=100 width=32) (actual time=0.017..0.017 rows=1 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.018 0.018 ↑ 50.0 2 2

Function Scan on jsonb_array_elements e (cost=0.01..1.00 rows=100 width=32) (actual time=0.007..0.009 rows=2 loops=2)

Planning time : 1.259 ms
Execution time : 21,162.817 ms