explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h2gM : Optimization for: Optimization for: plan #DN4; plan #Kd3I

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 871.131 272,978.692 ↓ 391,014.0 391,014 1

WindowAgg (cost=14,070.97..14,071.05 rows=1 width=172) (actual time=271,624.509..272,978.692 rows=391,014 loops=1)

2.          

CTE weights_in_target

3. 41.327 11,744.981 ↓ 511.0 18,397 1

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

4. 29.465 11,630.066 ↓ 511.0 18,397 1

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

5. 34.676 11,600.601 ↓ 514.3 18,514 1

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

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

HashAggregate (cost=705.28..794.36 rows=36 width=20) (actual time=11,477.308..11,565.925 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,982.754 8,071.680 ↓ 465.3 3,315,812 1

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

8. 0.092 0.486 ↓ 40.0 40 1

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

9. 0.021 0.294 ↓ 4.0 4 1

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

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

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

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

Hash (cost=40.16..40.16 rows=4 width=12) (actual time=0.211..0.212 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.008 0.204 ↑ 1.0 4 1

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

13. 0.046 0.196 ↑ 1.0 4 1

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

  • Group Key: c_2.id
14. 0.074 0.150 ↑ 1.0 40 1

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

15. 0.024 0.024 ↑ 1.0 4 1

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

  • Filter: (target_id = 1)
  • Rows Removed by Filter: 289
16. 0.052 0.052 ↑ 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.004..0.013 rows=10 loops=4)

  • Index Cond: (condition_id = c_2.id)
17. 0.100 0.100 ↑ 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.006..0.025 rows=10 loops=4)

  • Index Cond: (condition_id = c_1.id)
18. 3,088.440 3,088.440 ↓ 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.539..77.211 rows=82,895 loops=40)

  • Index Cond: ((demography = s.demography) AND (value >= s."from") AND (value <= s."to"))
19. 73.588 73.588 ↑ 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.004..0.004 rows=1 loops=18,397)

  • Index Cond: ((person_id = d.person_id) AND (date = d.date))
20. 1,317.957 272,107.561 ↓ 391,014.0 391,014 1

Sort (cost=12,975.30..12,975.30 rows=1 width=184) (actual time=271,624.434..272,107.561 rows=391,014 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: external merge Disk: 62040kB
21. 1,368.489 270,789.604 ↓ 391,014.0 391,014 1

GroupAggregate (cost=12,975.24..12,975.29 rows=1 width=184) (actual time=268,191.220..270,789.604 rows=391,014 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))
22. 4,646.520 269,421.115 ↓ 1,144,601.0 1,144,601 1

Sort (cost=12,975.24..12,975.25 rows=1 width=180) (actual time=268,191.200..269,421.115 rows=1,144,601 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: external merge Disk: 172736kB
23. 2,149.549 264,774.595 ↓ 1,144,601.0 1,144,601 1

Nested Loop (cost=8.93..12,975.23 rows=1 width=180) (actual time=36,774.224..264,774.595 rows=1,144,601 loops=1)

24. 129.882 129.882 ↑ 1.0 1 1

Seq Scan on programme pro (cost=0.00..2.50 rows=1 width=22) (actual time=127.844..129.882 rows=1 loops=1)

  • Filter: ((data ->> 'name'::text) = '13 Posterunek'::text)
  • Rows Removed by Filter: 101
25. 104,134.479 262,495.164 ↓ 1,144,601.0 1,144,601 1

Nested Loop (cost=8.93..12,972.71 rows=1 width=48) (actual time=36,646.369..262,495.164 rows=1,144,601 loops=1)

  • Join Filter: (c.date = p.date)
  • Rows Removed by Join Filter: 136207519
26. 16.576 11,792.085 ↓ 3.3 120 1

HashAggregate (cost=0.90..1.26 rows=36 width=12) (actual time=11,791.678..11,792.085 rows=120 loops=1)

  • Group Key: p.date
27. 11,775.509 11,775.509 ↓ 511.0 18,397 1

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

28. 125,705.908 146,568.600 ↓ 572,300.5 1,144,601 120

Materialize (cost=8.03..12,970.02 rows=2 width=48) (actual time=0.252..1,221.405 rows=1,144,601 loops=120)

29. 1,767.251 20,862.692 ↓ 572,300.5 1,144,601 1

Nested Loop (cost=8.03..12,970.01 rows=2 width=48) (actual time=29.001..20,862.692 rows=1,144,601 loops=1)

30. 17.752 17.752 ↓ 511.0 18,397 1

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

31. 6,854.511 19,077.689 ↓ 62.0 62 18,397

Hash Join (cost=8.03..368.05 rows=1 width=44) (actual time=0.248..1.037 rows=62 loops=18,397)

  • 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: 312
32. 10,946.215 12,197.211 ↓ 1.0 95 18,397

Bitmap Heap Scan on contact c (cost=5.52..364.23 rows=94 width=14) (actual time=0.078..0.663 rows=95 loops=18,397)

  • Recheck Cond: ((person_id = per.person_id) AND (date = per.date))
  • Heap Blocks: exact=1698263
33. 1,250.996 1,250.996 ↓ 1.0 95 18,397

Bitmap Index Scan on contact_pkey (cost=0.00..5.50 rows=94 width=0) (actual time=0.068..0.068 rows=95 loops=18,397)

  • Index Cond: ((person_id = per.person_id) AND (date = per.date))
34. 14.786 25.967 ↓ 125.8 12,577 1

Hash (cost=1.00..1.00 rows=100 width=32) (actual time=25.966..25.967 rows=12,577 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1713kB
35. 11.181 11.181 ↓ 125.8 12,577 1

Function Scan on jsonb_array_elements e (cost=0.01..1.00 rows=100 width=32) (actual time=1.761..11.181 rows=12,577 loops=1)

Planning time : 1.326 ms
Execution time : 273,307.833 ms