explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bRty : Slow query

Settings
# exclusive inclusive rows x rows loops node
1. 237.532 75,989.090 ↓ 95.2 571 1

Unique (cost=392.02..392.24 rows=6 width=440) (actual time=74,621.477..75,989.090 rows=571 loops=1)

2.          

CTE cte_timer

3. 36.838 36.838 ↓ 1,465.0 1,465 1

Foreign Scan on timer_ctr_set_info (cost=100.00..113.49 rows=1 width=388) (actual time=3.174..36.838 rows=1,465 loops=1)

4.          

CTE cte_alarms

5. 0.000 174.859 ↓ 1,158.0 1,158 1

Nested Loop (cost=100.07..113.80 rows=1 width=838) (actual time=48.016..174.859 rows=1,158 loops=1)

  • Join Filter: ((a_1.oid)::text = (adh_1.facl_id)::text)
  • Rows Removed by Join Filter: 51566
6.          

Initplan (forNested Loop)

7. 0.294 38.937 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=38.937..38.937 rows=1 loops=1)

8. 38.643 38.643 ↓ 1,465.0 1,465 1

CTE Scan on cte_timer (cost=0.00..0.02 rows=1 width=8) (actual time=3.179..38.643 rows=1,465 loops=1)

9. 0.198 0.317 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.317..0.317 rows=1 loops=1)

10. 0.119 0.119 ↓ 1,465.0 1,465 1

CTE Scan on cte_timer cte_timer_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.119 rows=1,465 loops=1)

11. 109.449 109.449 ↓ 7,532.0 7,532 1

Foreign Scan on alarm_data_history adh_1 (cost=100.00..111.92 rows=1 width=838) (actual time=44.432..109.449 rows=7,532 loops=1)

12. 37.660 37.660 ↑ 1.0 7 7,532

Seq Scan on adapters a_1 (cost=0.00..1.73 rows=7 width=468) (actual time=0.001..0.005 rows=7 loops=7,532)

  • Filter: (id = ANY ('{94,95,96,97,98,99,100}'::bigint[]))
  • Rows Removed by Filter: 32
13. 11,553.054 75,751.558 ↓ 86,246.3 517,478 1

Sort (cost=164.73..164.74 rows=6 width=440) (actual time=74,621.476..75,751.558 rows=517,478 loops=1)

  • Sort Key: a.id, t.key_datetime_1_each_ctr_type, sq.reqid, ((outdoorunits_history.connectiontype)::text), outdoorunits_history.connectionnumber, outdoorunits_history.refrigcircuitno, ((t.timer_ctr_set_value)::text), sq.status, t.key_datetime_2_each_ (...)
  • Sort Method: external merge Disk: 156168kB
14. 1,060.377 64,198.504 ↓ 86,246.3 517,478 1

Nested Loop (cost=100.48..164.65 rows=6 width=440) (actual time=1,965.861..64,198.504 rows=517,478 loops=1)

  • Join Filter: ((t.facl_id)::text = (a.oid)::text)
  • Rows Removed by Join Filter: 1995201
15. 0.063 1.131 ↓ 13.0 13 1

Nested Loop (cost=0.28..17.03 rows=1 width=530) (actual time=0.141..1.131 rows=13 loops=1)

16. 0.335 0.964 ↓ 13.0 13 1

Nested Loop (cost=0.00..11.58 rows=1 width=517) (actual time=0.128..0.964 rows=13 loops=1)

  • Join Filter: (a.id = outdoorunits_history.adapters_id)
  • Rows Removed by Join Filter: 498
17. 0.191 0.191 ↓ 73.0 73 1

Seq Scan on outdoorunits_history (cost=0.00..9.76 rows=1 width=47) (actual time=0.017..0.191 rows=73 loops=1)

  • Filter: (((refrigcircuitgroupoduid)::text = '0'::text) AND (lower(delete_flg) = 'false'::text))
  • Rows Removed by Filter: 142
18. 0.438 0.438 ↑ 1.0 7 73

Seq Scan on adapters a (cost=0.00..1.73 rows=7 width=478) (actual time=0.002..0.006 rows=7 loops=73)

  • Filter: (id = ANY ('{94,95,96,97,98,99,100}'::bigint[]))
  • Rows Removed by Filter: 32
19. 0.104 0.104 ↑ 1.0 1 13

Index Scan using timezonemaster_pkey on timezonemaster tm (cost=0.28..5.44 rows=1 width=19) (actual time=0.006..0.008 rows=1 loops=13)

  • Index Cond: (id = a.timezone)
20. 2,794.676 63,136.996 ↓ 32,213.8 193,283 13

Hash Join (cost=100.21..147.49 rows=6 width=926) (actual time=64.719..4,856.692 rows=193,283 loops=13)

  • Hash Cond: ((c.request_id)::text = (t.request_id)::text)
21. 60,003.086 60,003.086 ↓ 2,149.5 2,366,603 13

Foreign Scan on collect_data_for_analysis c (cost=100.00..143.09 rows=1,101 width=50) (actual time=0.352..4,615.622 rows=2,366,603 loops=13)

22. 0.147 339.234 ↓ 192.0 192 1

Hash (cost=0.20..0.20 rows=1 width=976) (actual time=339.234..339.234 rows=192 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 82kB
23. 18.453 339.087 ↓ 192.0 192 1

Nested Loop (cost=0.12..0.20 rows=1 width=976) (actual time=282.657..339.087 rows=192 loops=1)

  • Join Filter: (((t.facl_id)::text = (sq.caoid)::text) AND ((t.request_id)::text = (sq.reqid)::text))
  • Rows Removed by Join Filter: 284018
24. 0.039 282.222 ↓ 194.0 194 1

Subquery Scan on sq (cost=0.12..0.16 rows=1 width=596) (actual time=281.812..282.222 rows=194 loops=1)

  • Filter: (sq.rn = 1)
  • Rows Removed by Filter: 385
25. 0.362 282.183 ↓ 579.0 579 1

WindowAgg (cost=0.12..0.15 rows=1 width=894) (actual time=281.808..282.183 rows=579 loops=1)

26. 1.271 281.821 ↓ 579.0 579 1

Sort (cost=0.12..0.12 rows=1 width=894) (actual time=281.806..281.821 rows=579 loops=1)

  • Sort Key: (CASE WHEN (((adh.reqid)::text = '-'::text) AND ((adh.preqid)::text <> '-'::text) AND ((adh.pcaoid)::text = (adh.caoid)::text)) THEN adh.preqid ELSE adh.reqid END), adh.datetime
  • Sort Method: quicksort Memory: 106kB
27. 0.094 280.550 ↓ 579.0 579 1

Subquery Scan on adh (cost=0.07..0.11 rows=1 width=894) (actual time=280.164..280.550 rows=579 loops=1)

28. 0.357 280.456 ↓ 579.0 579 1

WindowAgg (cost=0.07..0.10 rows=1 width=838) (actual time=280.141..280.456 rows=579 loops=1)

29. 1.621 280.099 ↓ 579.0 579 1

Sort (cost=0.07..0.08 rows=1 width=838) (actual time=280.085..280.099 rows=579 loops=1)

  • Sort Key: adhr.facl_id, adhr.occur_datetime
  • Sort Method: quicksort Memory: 106kB
30. 25.936 278.478 ↓ 579.0 579 1

Nested Loop (cost=0.00..0.06 rows=1 width=838) (actual time=48.037..278.478 rows=579 loops=1)

  • Join Filter: (((adhr.facl_id)::text = (adhc.facl_id)::text) AND (adhr.occur_datetime = adhc.occur_datetime))
  • Rows Removed by Join Filter: 334662
31. 48.155 48.155 ↓ 579.0 579 1

CTE Scan on cte_alarms adhr (cost=0.00..0.02 rows=1 width=556) (actual time=48.021..48.155 rows=579 loops=1)

  • Filter: ((property_id)::text = 'RSC101'::text)
  • Rows Removed by Filter: 579
32. 204.387 204.387 ↓ 579.0 579 579

CTE Scan on cte_alarms adhc (cost=0.00..0.02 rows=1 width=564) (actual time=0.000..0.353 rows=579 loops=579)

  • Filter: ((property_id)::text = 'RSC108'::text)
  • Rows Removed by Filter: 579
33. 38.412 38.412 ↓ 1,465.0 1,465 194

CTE Scan on cte_timer t (cost=0.00..0.02 rows=1 width=380) (actual time=0.000..0.198 rows=1,465 loops=194)

  • Filter: (key_datetime_3_each_ctr_type > '2018-10-29 03:47:10'::timestamp without time zone)