explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wLcR

Settings
# exclusive inclusive rows x rows loops node
1. 0.395 85,355.368 ↑ 3.5 59 1

Hash Left Join (cost=1,492.07..1,970.07 rows=205 width=287) (actual time=85,354.247..85,355.368 rows=59 loops=1)

  • Hash Cond: (i.indicator_security_level_id = isl.id)
2.          

CTE expanded

3. 0.019 1.556 ↑ 13.7 3 1

Nested Loop (cost=0.52..43.97 rows=41 width=82) (actual time=1.504..1.556 rows=3 loops=1)

4. 0.004 0.203 ↑ 1.0 1 1

Nested Loop (cost=0.27..23.31 rows=1 width=50) (actual time=0.160..0.203 rows=1 loops=1)

  • Join Filter: (s.organization_id = cm_1.organization_id)
5. 0.005 0.181 ↑ 1.0 1 1

Nested Loop (cost=0.27..21.21 rows=1 width=50) (actual time=0.146..0.181 rows=1 loops=1)

  • Join Filter: (ic.channel_id = s.channel_id)
  • Rows Removed by Join Filter: 1
6. 0.045 0.135 ↑ 1.0 1 1

Nested Loop (cost=0.27..16.24 rows=1 width=50) (actual time=0.124..0.135 rows=1 loops=1)

7. 0.007 0.007 ↑ 1.0 1 1

Index Scan using pk_indicator on indicator i_1 (cost=0.27..8.29 rows=1 width=46) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (id = 675)
8. 0.083 0.083 ↑ 1.0 1 1

Seq Scan on indicator_channel ic (cost=0.00..7.94 rows=1 width=8) (actual time=0.073..0.083 rows=1 loops=1)

  • Filter: (indicator_id = 675)
  • Rows Removed by Filter: 394
9. 0.041 0.041 ↑ 1.0 2 1

Seq Scan on account_subscription s (cost=0.00..4.95 rows=2 width=8) (actual time=0.018..0.041 rows=2 loops=1)

  • Filter: ((active IS TRUE) AND (account_id = 2))
  • Rows Removed by Filter: 154
10. 0.018 0.018 ↑ 1.0 1 1

Seq Scan on organization_member cm_1 (cost=0.00..2.08 rows=1 width=4) (actual time=0.012..0.018 rows=1 loops=1)

  • Filter: ((account_id = 2) AND (id = 58) AND (member_status_id = 'A'::bpchar))
  • Rows Removed by Filter: 61
11. 1.334 1.334 ↑ 333.3 3 1

Function Scan on my_data_source_preferences (cost=0.25..10.25 rows=1,000 width=32) (actual time=1.333..1.334 rows=3 loops=1)

12.          

CTE indicator_sharing_status

13. 3,519.393 3,519.393 ↓ 23.0 22,951 1

Function Scan on is_shared_indicator a (cost=0.25..10.25 rows=1,000 width=40) (actual time=3,516.583..3,519.393 rows=22,951 loops=1)

14. 0.037 85,354.652 ↑ 3.5 59 1

Nested Loop Left Join (cost=1,399.28..1,497.77 rows=205 width=259) (actual time=85,354.209..85,354.652 rows=59 loops=1)

15. 4.651 85,354.320 ↑ 3.5 59 1

Merge Left Join (cost=1,399.01..1,423.16 rows=205 width=259) (actual time=85,354.192..85,354.320 rows=59 loops=1)

  • Merge Cond: ((ad.indicator_id = es.indicator_id) AND ((((lower(ad.data_source_type) || ':'::text) || (ad.data_source_id)::text)) = es.data_source))
16. 0.195 81,797.801 ↑ 3.5 59 1

Sort (cost=1,329.18..1,329.69 rows=205 width=291) (actual time=81,797.788..81,797.801 rows=59 loops=1)

  • Sort Key: ad.indicator_id, (((lower(ad.data_source_type) || ':'::text) || (ad.data_source_id)::text))
  • Sort Method: quicksort Memory: 40kB
17. 0.708 81,797.606 ↑ 3.5 59 1

Nested Loop Left Join (cost=29.14..1,321.31 rows=205 width=291) (actual time=1,305.319..81,797.606 rows=59 loops=1)

18. 0.217 81,796.839 ↑ 3.5 59 1

Nested Loop Left Join (cost=27.20..1,311.98 rows=205 width=259) (actual time=1,305.281..81,796.839 rows=59 loops=1)

19. 0.101 8.816 ↓ 1.4 59 1

Hash Left Join (cost=4.18..364.41 rows=41 width=199) (actual time=3.365..8.816 rows=59 loops=1)

  • Hash Cond: (cm.organization_id = c.id)
20. 0.091 8.695 ↓ 1.4 59 1

Nested Loop Left Join (cost=1.76..361.42 rows=41 width=184) (actual time=3.328..8.695 rows=59 loops=1)

21. 0.023 1.596 ↑ 13.7 3 1

Hash Right Join (cost=1.33..3.70 rows=41 width=80) (actual time=1.583..1.596 rows=3 loops=1)

  • Hash Cond: (cm.id = e.data_source_id)
  • Join Filter: (e.data_source_type = 'Member'::text)
22. 0.011 0.011 ↑ 1.0 62 1

Seq Scan on organization_member cm (cost=0.00..1.62 rows=62 width=8) (actual time=0.005..0.011 rows=62 loops=1)

23. 0.003 1.562 ↑ 13.7 3 1

Hash (cost=0.82..0.82 rows=41 width=76) (actual time=1.562..1.562 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 1.559 1.559 ↑ 13.7 3 1

CTE Scan on expanded e (cost=0.00..0.82 rows=41 width=76) (actual time=1.507..1.559 rows=3 loops=1)

25. 7.008 7.008 ↓ 20.0 20 3

Index Scan using mat_aggregate_data_data_source_type_data_source_id_report_p_idx on aggregate_data ad (cost=0.42..8.72 rows=1 width=104) (actual time=1.192..2.336 rows=20 loops=3)

  • Index Cond: ((data_source_type = e.data_source_type) AND (data_source_id = e.data_source_id) AND (indicator_id = e.indicator_id))
  • Filter: ((report_period_start >= '2014-01-01'::date) AND (report_period_start <= '2019-03-12'::date))
  • Rows Removed by Filter: 15
26. 0.009 0.020 ↑ 1.0 19 1

Hash (cost=2.19..2.19 rows=19 width=23) (actual time=0.020..0.020 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
27. 0.011 0.011 ↑ 1.0 19 1

Seq Scan on organization c (cost=0.00..2.19 rows=19 width=23) (actual time=0.005..0.011 rows=19 loops=1)

28. 102.129 81,787.806 ↑ 5.0 1 59

Function Scan on my_data_source_preferences d (cost=23.01..45.51 rows=5 width=64) (actual time=1,386.231..1,386.234 rows=1 loops=59)

  • Filter: (id = ((e.data_source_type || ':'::text) || (e.data_source_id)::text))
  • Rows Removed by Filter: 11
29.          

SubPlan (forFunction Scan)

30. 1.475 81,685.677 ↑ 1.0 1 59

Aggregate (cost=22.75..22.76 rows=1 width=36) (actual time=1,384.503..1,384.503 rows=1 loops=59)

31. 81,684.202 81,684.202 ↑ 76.9 13 59

Function Scan on data_source_list (cost=0.25..10.25 rows=1,000 width=36) (actual time=1,384.476..1,384.478 rows=13 loops=59)

32. 0.022 0.059 ↑ 1.0 1 59

Materialize (cost=1.94..6.77 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=59)

33. 0.001 0.037 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.94..6.77 rows=1 width=32) (actual time=0.030..0.037 rows=1 loops=1)

34. 0.009 0.033 ↑ 1.0 1 1

Hash Right Join (cost=1.79..4.06 rows=1 width=4) (actual time=0.027..0.033 rows=1 loops=1)

  • Hash Cond: (o.id = om.organization_id)
35. 0.007 0.007 ↑ 1.0 19 1

Seq Scan on organization o (cost=0.00..2.19 rows=19 width=8) (actual time=0.002..0.007 rows=19 loops=1)

36. 0.002 0.017 ↑ 1.0 1 1

Hash (cost=1.77..1.77 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on organization_member om (cost=0.00..1.77 rows=1 width=4) (actual time=0.011..0.015 rows=1 loops=1)

  • Filter: (id = 58)
  • Rows Removed by Filter: 61
38. 0.003 0.003 ↑ 1.0 1 1

Index Scan using organization_type_pkey on organization_type ot (cost=0.15..2.70 rows=1 width=36) (actual time=0.002..0.003 rows=1 loops=1)

  • Index Cond: (id = o.organization_type_id)
39. 21.558 3,551.868 ↓ 20.5 20,466 1

Sort (cost=69.83..72.33 rows=1,000 width=40) (actual time=3,549.951..3,551.868 rows=20,466 loops=1)

  • Sort Key: es.indicator_id, es.data_source
  • Sort Method: quicksort Memory: 2562kB
40. 3,530.310 3,530.310 ↓ 23.0 22,951 1

CTE Scan on indicator_sharing_status es (cost=0.00..20.00 rows=1,000 width=40) (actual time=3,516.603..3,530.310 rows=22,951 loops=1)

41. 0.295 0.295 ↑ 1.0 1 59

Index Scan using pk_indicator on indicator i (cost=0.27..0.35 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=59)

  • Index Cond: (id = ad.indicator_id)
42. 0.003 0.006 ↑ 635.0 2 1

Hash (cost=22.70..22.70 rows=1,270 width=36) (actual time=0.006..0.006 rows=2 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
43. 0.003 0.003 ↑ 635.0 2 1

Seq Scan on indicator_security_level isl (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.002..0.003 rows=2 loops=1)

44.          

SubPlan (forHash Left Join)

45. 0.315 0.315 ↑ 1.5 2 21

Seq Scan on organization_member (cost=0.00..1.77 rows=3 width=4) (actual time=0.015..0.015 rows=2 loops=21)

  • Filter: (organization_id = e.data_source_id)
  • Rows Removed by Filter: 32
46. 0.000 0.000 ↓ 0.0 0

Seq Scan on organization_member organization_member_1 (cost=0.00..1.77 rows=3 width=4) (never executed)

  • Filter: (organization_id = e.data_source_id)