explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aQeI : Optimization for: Optimization for: Optimization for: Optimization for: plan #kX4l; plan #lca4; plan #6S3U; plan #0Hcr

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.602 5,679.667 ↓ 87.5 350 1

Sort (cost=353,514.00..353,514.01 rows=4 width=65) (actual time=5,679.649..5,679.667 rows=350 loops=1)

  • Sort Key: r.target_name, ((((p.first_name)::text || ' '::text) || (p.last_name)::text))
  • Sort Method: quicksort Memory: 53kB
  • Buffers: shared hit=238,018 read=461,277 written=5,229
  • I/O Timings: read=3,405.938 write=153.600
2.          

CTE cte_org

3. 0.003 0.003 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=40) (actual time=0.002..0.003 rows=1 loops=1)

4.          

CTE ev_ids

5. 0.586 52.039 ↑ 2.3 4,373 1

Append (cost=103.13..12,599.89 rows=10,062 width=8) (actual time=1.319..52.039 rows=4,373 loops=1)

  • Buffers: shared hit=2,365 read=1,901
  • I/O Timings: read=16.023
6. 18.960 20.006 ↑ 2.1 2,355 1

Bitmap Heap Scan on evs evs_1 (cost=103.13..6,062.44 rows=4,927 width=8) (actual time=1.318..20.006 rows=2,355 loops=1)

  • Recheck Cond: (company_id = $1)
  • Heap Blocks: exact=2,181
  • Buffers: shared hit=2,341 read=17
  • I/O Timings: read=0.196
7.          

Initplan (for Bitmap Heap Scan)

8. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on cte_org (cost=0.00..0.02 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1)

9. 1.041 1.041 ↑ 1.8 2,775 1

Bitmap Index Scan on evs_company_id_idx (cost=0.00..101.88 rows=4,927 width=0) (actual time=1.041..1.041 rows=2,775 loops=1)

  • Index Cond: (company_id = $1)
  • Buffers: shared hit=2 read=17
  • I/O Timings: read=0.196
10. 30.686 31.447 ↑ 2.5 2,018 1

Bitmap Heap Scan on evs_arc evs_arc_1 (cost=62.74..6,386.53 rows=5,135 width=8) (actual time=0.995..31.447 rows=2,018 loops=1)

  • Recheck Cond: (company_id = $2)
  • Heap Blocks: exact=1,899
  • Buffers: shared hit=24 read=1,884
  • I/O Timings: read=15.827
11.          

Initplan (for Bitmap Heap Scan)

12. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on cte_org cte_org_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

13. 0.759 0.759 ↑ 2.5 2,018 1

Bitmap Index Scan on evs_arc_company_id_idx (cost=0.00..61.44 rows=5,135 width=0) (actual time=0.759..0.759 rows=2,018 loops=1)

  • Index Cond: (company_id = $2)
  • Buffers: shared read=9
  • I/O Timings: read=0.118
14. 1.738 5,679.065 ↓ 87.5 350 1

HashAggregate (cost=340,911.00..340,914.01 rows=4 width=65) (actual time=5,678.962..5,679.065 rows=350 loops=1)

  • Group Key: r.target_name, ((((p.first_name)::text || ' '::text) || (p.last_name)::text))
  • Filter: ((max((max(p.last_login))) IS NULL) OR (max("*SELECT* 1".max_date) IS NULL) OR (max((e.creation_date)::date) IS NULL))
  • Rows Removed by Filter: 56
  • Buffers: shared hit=238,018 read=461,277 written=5,229
  • I/O Timings: read=3,405.938 write=153.600
15. 1.003 5,677.327 ↑ 498.0 3,453 1

Merge Right Join (cost=267,151.05..293,618.76 rows=1,719,718 width=69) (actual time=5,617.777..5,677.327 rows=3,453 loops=1)

  • Merge Cond: ("*SELECT* 1".person_id = p.person_id)
  • Buffers: shared hit=238,018 read=461,277 written=5,229
  • I/O Timings: read=3,405.938 write=153.600
16. 0.172 5,633.394 ↑ 15.6 955 1

Merge Append (cost=230,432.16..231,066.77 rows=14,932 width=12) (actual time=5,575.099..5,633.394 rows=955 loops=1)

  • Sort Key: "*SELECT* 1".person_id
  • Buffers: shared hit=228,091 read=459,193 written=5,226
  • I/O Timings: read=3,396.061 write=153.536
17. 0.071 804.358 ↑ 21.7 474 1

Subquery Scan on *SELECT* 1 (cost=60,854.48..61,188.03 rows=10,263 width=12) (actual time=771.947..804.358 rows=474 loops=1)

  • Buffers: shared hit=80,513 read=35,627
  • I/O Timings: read=300.707
18. 19.743 804.287 ↑ 21.7 474 1

GroupAggregate (cost=60,854.48..61,085.40 rows=10,263 width=20) (actual time=771.946..804.287 rows=474 loops=1)

  • Group Key: audit_evs_all.person_id, audit_evs_all.dvc_id
  • Buffers: shared hit=80,513 read=35,627
  • I/O Timings: read=300.707
19.          

Initplan (for GroupAggregate)

20. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on cte_org cte_org_5 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

21. 47.105 784.542 ↓ 6.9 70,840 1

Sort (cost=60,854.46..60,880.12 rows=10,263 width=24) (actual time=771.730..784.542 rows=70,840 loops=1)

  • Sort Key: audit_evs_all.person_id, audit_evs_all.dvc_id
  • Sort Method: quicksort Memory: 8,607kB
  • Buffers: shared hit=80,513 read=35,627
  • I/O Timings: read=300.707
22. 36.177 737.437 ↓ 6.9 70,840 1

Nested Loop (cost=226.96..60,170.68 rows=10,263 width=24) (actual time=56.159..737.437 rows=70,840 loops=1)

  • Buffers: shared hit=80,513 read=35,627
  • I/O Timings: read=300.707
23. 4.783 58.429 ↓ 21.9 4,373 1

HashAggregate (cost=226.40..228.40 rows=200 width=8) (actual time=56.034..58.429 rows=4,373 loops=1)

  • Group Key: ev_ids.ev_id
  • Buffers: shared hit=2,365 read=1,901
  • I/O Timings: read=16.023
24. 53.646 53.646 ↑ 2.3 4,373 1

CTE Scan on ev_ids (cost=0.00..201.24 rows=10,062 width=8) (actual time=1.321..53.646 rows=4,373 loops=1)

  • Buffers: shared hit=2,365 read=1,901
  • I/O Timings: read=16.023
25. 642.831 642.831 ↓ 16.0 16 4,373

Index Scan using idx_audit_evs_all_eia on audit_evs_all (cost=0.56..299.70 rows=1 width=32) (actual time=0.057..0.147 rows=16 loops=4,373)

  • Index Cond: ((ev_id = ev_ids.ev_id) AND ((ap_audit_ev_type)::text = ANY ('{LIVE_NOTIFICATION_PROVIDER_DELIVERED,LIVE_NOTIFICATION_DEVICE_DELIVERED,LIVE_NOTIFICATION_DEVICE_READ}'::text[])))
  • Filter: (org_id = $7)
  • Buffers: shared hit=78,148 read=33,726
  • I/O Timings: read=284.685
26. 0.074 4,828.864 ↑ 9.7 481 1

Subquery Scan on *SELECT* 2 (cost=169,577.67..169,729.41 rows=4,669 width=12) (actual time=4,803.149..4,828.864 rows=481 loops=1)

  • Buffers: shared hit=147,578 read=423,566 written=5,226
  • I/O Timings: read=3,095.353 write=153.536
27. 15.627 4,828.790 ↑ 9.7 481 1

GroupAggregate (cost=169,577.67..169,682.72 rows=4,669 width=20) (actual time=4,803.147..4,828.790 rows=481 loops=1)

  • Group Key: audit_evs_all_arc.person_id, audit_evs_all_arc.dvc_id
  • Buffers: shared hit=147,578 read=423,566 written=5,226
  • I/O Timings: read=3,095.353 write=153.536
28.          

Initplan (for GroupAggregate)

29. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on cte_org cte_org_6 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1)

30. 52.908 4,813.160 ↓ 12.0 56,004 1

Sort (cost=169,577.65..169,589.32 rows=4,669 width=24) (actual time=4,802.972..4,813.160 rows=56,004 loops=1)

  • Sort Key: audit_evs_all_arc.person_id, audit_evs_all_arc.dvc_id
  • Sort Method: quicksort Memory: 5,912kB
  • Buffers: shared hit=147,578 read=423,566 written=5,226
  • I/O Timings: read=3,095.353 write=153.536
31. 18.220 4,760.252 ↓ 12.0 56,004 1

Nested Loop (cost=226.96..169,293.10 rows=4,669 width=24) (actual time=2.478..4,760.252 rows=56,004 loops=1)

  • Buffers: shared hit=147,578 read=423,566 written=5,226
  • I/O Timings: read=3,095.353 write=153.536
32. 5.569 6.073 ↓ 21.9 4,373 1

HashAggregate (cost=226.40..228.40 rows=200 width=8) (actual time=2.168..6.073 rows=4,373 loops=1)

  • Group Key: ev_ids_1.ev_id
33. 0.504 0.504 ↑ 2.3 4,373 1

CTE Scan on ev_ids ev_ids_1 (cost=0.00..201.24 rows=10,062 width=8) (actual time=0.002..0.504 rows=4,373 loops=1)

34. 4,735.959 4,735.959 ↓ 13.0 13 4,373

Index Scan using idx_audit_proc_evs_arc_evid on audit_evs_all_arc (cost=0.56..845.31 rows=1 width=32) (actual time=0.059..1.083 rows=13 loops=4,373)

  • Index Cond: (ev_id = ev_ids_1.ev_id)
  • Filter: ((org_id = $8) AND ((ap_audit_ev_type)::text = ANY ('{LIVE_NOTIFICATION_PROVIDER_DELIVERED,LIVE_NOTIFICATION_DEVICE_DELIVERED,LIVE_NOTIFICATION_DEVICE_READ}'::text[])))
  • Rows Removed by Filter: 117
  • Buffers: shared hit=147,578 read=423,566 written=5,226
  • I/O Timings: read=3,095.353 write=153.536
35. 0.605 42.930 ↑ 6.7 3,453 1

Sort (cost=36,718.89..36,776.48 rows=23,034 width=73) (actual time=42.672..42.930 rows=3,453 loops=1)

  • Sort Key: p.person_id
  • Sort Method: quicksort Memory: 113kB
  • Buffers: shared hit=9,927 read=2,084 written=3
  • I/O Timings: read=9.877 write=0.064
36. 0.266 42.325 ↑ 24.1 956 1

Hash Left Join (cost=33,791.51..35,049.91 rows=23,034 width=73) (actual time=40.956..42.325 rows=956 loops=1)

  • Hash Cond: ((r.target_name)::text = (e.sender)::text)
  • Buffers: shared hit=9,927 read=2,084 written=3
  • I/O Timings: read=9.877 write=0.064
37. 0.173 19.380 ↑ 3.5 864 1

Finalize GroupAggregate (cost=21,196.61..21,611.18 rows=3,011 width=73) (actual time=18.238..19.380 rows=864 loops=1)

  • Group Key: r.recipient_id, p.person_id, ((((p.first_name)::text || ' '::text) || (p.last_name)::text)), ((p.when_created)::date)
  • Buffers: shared hit=7,585 read=160 written=3
  • I/O Timings: read=0.903 write=0.064
38.          

Initplan (for Finalize GroupAggregate)

39. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on cte_org cte_org_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1)

40. 8.612 19.204 ↑ 2.9 864 1

Gather Merge (cost=21,196.59..21,527.09 rows=2,510 width=73) (actual time=18.221..19.204 rows=864 loops=1)

  • Workers Planned: 2
  • Params Evaluated: $4
  • Workers Launched: 2
  • Buffers: shared hit=10,000 read=200 written=3
  • I/O Timings: read=1.445 write=0.064
41. 0.183 10.592 ↑ 4.4 288 3 / 3

Partial GroupAggregate (cost=20,196.56..20,237.35 rows=1,255 width=73) (actual time=10.387..10.592 rows=288 loops=3)

  • Group Key: r.recipient_id, p.person_id, ((((p.first_name)::text || ' '::text) || (p.last_name)::text)), ((p.when_created)::date)
  • Buffers: shared hit=10,000 read=200 written=3
  • I/O Timings: read=1.445 write=0.064
42. 0.320 10.409 ↑ 4.4 288 3 / 3

Sort (cost=20,196.56..20,199.70 rows=1,255 width=73) (actual time=10.378..10.409 rows=288 loops=3)

  • Sort Key: r.recipient_id, p.person_id, ((((p.first_name)::text || ' '::text) || (p.last_name)::text)), ((p.when_created)::date)
  • Sort Method: quicksort Memory: 99kB
  • Worker 0: Sort Method: quicksort Memory: 37kB
  • Worker 1: Sort Method: quicksort Memory: 35kB
  • Buffers: shared hit=10,000 read=200 written=3
  • I/O Timings: read=1.445 write=0.064
43. 0.283 10.089 ↑ 4.4 288 3 / 3

Nested Loop (cost=152.37..20,131.97 rows=1,255 width=73) (actual time=0.403..10.089 rows=288 loops=3)

  • Buffers: shared hit=9,950 read=200 written=3
  • I/O Timings: read=1.445 write=0.064
44. 0.387 7.502 ↑ 7.2 288 3 / 3

Nested Loop (cost=151.94..19,172.56 rows=2,068 width=29) (actual time=0.310..7.502 rows=288 loops=3)

  • Buffers: shared hit=6,493 read=199 written=3
  • I/O Timings: read=1.439 write=0.064
45. 2.866 3.014 ↑ 6.2 513 3 / 3

Parallel Bitmap Heap Scan on recipients r (cost=151.52..10,111.81 rows=3,204 width=21) (actual time=0.230..3.014 rows=513 loops=3)

  • Recheck Cond: ((org_id = $4) AND (deleted_id = '-1'::integer))
  • Heap Blocks: exact=918
  • Buffers: shared hit=1,203 read=9
  • I/O Timings: read=0.056
46. 0.148 0.148 ↑ 5.0 1,549 1 / 3

Bitmap Index Scan on idx_recipients_del_org_id_cat (cost=0.00..149.59 rows=7,689 width=0) (actual time=0.444..0.444 rows=1,549 loops=1)

  • Index Cond: (org_id = $4)
  • Buffers: shared hit=3 read=9
  • I/O Timings: read=0.056
47. 4.101 4.101 ↑ 1.0 1 1,538 / 3

Index Scan using dvc_pk on dvc d (cost=0.43..2.83 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=1,538)

  • Index Cond: (dvc_id = r.recipient_id)
  • Buffers: shared hit=5,290 read=190 written=3
  • I/O Timings: read=1.383 write=0.064
48. 2.304 2.304 ↑ 1.0 1 864 / 3

Index Scan using idx_persons_person_id_not_deleted on persons p (cost=0.42..0.46 rows=1 width=40) (actual time=0.008..0.008 rows=1 loops=864)

  • Index Cond: (person_id = d.person_id)
  • Buffers: shared hit=3,457 read=1
  • I/O Timings: read=0.006
49. 0.033 22.679 ↑ 11.0 139 1

Hash (cost=12,575.77..12,575.77 rows=1,530 width=226) (actual time=22.679..22.679 rows=139 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=2,342 read=1,924
  • I/O Timings: read=8.974
50. 0.016 22.646 ↑ 11.0 139 1

Subquery Scan on e (cost=12,545.17..12,575.77 rows=1,530 width=226) (actual time=22.603..22.646 rows=139 loops=1)

  • Buffers: shared hit=2,342 read=1,924
  • I/O Timings: read=8.974
51. 0.098 22.630 ↑ 11.0 139 1

HashAggregate (cost=12,545.17..12,560.47 rows=1,530 width=226) (actual time=22.601..22.630 rows=139 loops=1)

  • Group Key: evs.sender, (max(evs.creation_date))
  • Buffers: shared hit=2,342 read=1,924
  • I/O Timings: read=8.974
52. 0.017 22.532 ↑ 11.0 139 1

Append (cost=6,087.07..12,537.52 rows=1,530 width=226) (actual time=5.692..22.532 rows=139 loops=1)

  • Buffers: shared hit=2,342 read=1,924
  • I/O Timings: read=8.974
53. 1.132 5.703 ↑ 11.8 63 1

HashAggregate (cost=6,087.07..6,094.53 rows=746 width=20) (actual time=5.690..5.703 rows=63 loops=1)

  • Group Key: evs.sender
  • Buffers: shared hit=2,341 read=17
  • I/O Timings: read=0.119
54.          

Initplan (for HashAggregate)

55. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on cte_org cte_org_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

56. 3.750 4.569 ↑ 2.1 2,355 1

Bitmap Heap Scan on evs (cost=103.11..6,062.42 rows=4,927 width=20) (actual time=1.080..4.569 rows=2,355 loops=1)

  • Recheck Cond: (company_id = $5)
  • Heap Blocks: exact=2,181
  • Buffers: shared hit=2,341 read=17
  • I/O Timings: read=0.119
57. 0.819 0.819 ↑ 1.8 2,775 1

Bitmap Index Scan on evs_company_id_idx (cost=0.00..101.88 rows=4,927 width=0) (actual time=0.818..0.819 rows=2,775 loops=1)

  • Index Cond: (company_id = $5)
  • Buffers: shared hit=2 read=17
  • I/O Timings: read=0.119
58. 0.988 16.812 ↑ 10.3 76 1

HashAggregate (cost=6,412.20..6,420.04 rows=784 width=20) (actual time=16.796..16.812 rows=76 loops=1)

  • Group Key: evs_arc.sender
  • Buffers: shared hit=1 read=1,907
  • I/O Timings: read=8.856
59.          

Initplan (for HashAggregate)

60. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on cte_org cte_org_4 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

61. 15.264 15.822 ↑ 2.5 2,018 1

Bitmap Heap Scan on evs_arc (cost=62.72..6,386.51 rows=5,135 width=20) (actual time=0.795..15.822 rows=2,018 loops=1)

  • Recheck Cond: (company_id = $6)
  • Heap Blocks: exact=1,899
  • Buffers: shared hit=1 read=1,907
  • I/O Timings: read=8.856
62. 0.558 0.558 ↑ 2.5 2,018 1

Bitmap Index Scan on evs_arc_company_id_idx (cost=0.00..61.44 rows=5,135 width=0) (actual time=0.558..0.558 rows=2,018 loops=1)

  • Index Cond: (company_id = $6)
  • Buffers: shared read=9
  • I/O Timings: read=0.071
Planning time : 13.279 ms
Execution time : 5,683.362 ms