explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.548 4,289.056 ↓ 87.5 350 1

Sort (cost=353,839.79..353,839.80 rows=4 width=65) (actual time=4,289.038..4,289.056 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=281,099 read=417,598 written=3,689
  • I/O Timings: read=2,258.065 write=127.489
2.          

CTE cte_org

3. 0.009 0.009 ↑ 1.0 1 1

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

4.          

CTE ev_ids

5. 0.391 7.892 ↑ 2.3 4,373 1

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

  • Buffers: shared hit=4,266
6. 3.754 4.181 ↑ 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=0.675..4.181 rows=2,355 loops=1)

  • Recheck Cond: (company_id = $1)
  • Heap Blocks: exact=2,181
  • Buffers: shared hit=2,358
7.          

Initplan (for Bitmap Heap Scan)

8. 0.011 0.011 ↑ 1.0 1 1

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

9. 0.416 0.416 ↑ 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.416..0.416 rows=2,775 loops=1)

  • Index Cond: (company_id = $1)
  • Buffers: shared hit=19
10. 2.952 3.320 ↑ 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.578..3.320 rows=2,018 loops=1)

  • Recheck Cond: (company_id = $2)
  • Heap Blocks: exact=1,899
  • Buffers: shared hit=1,908
11.          

Initplan (for Bitmap Heap Scan)

12. 0.001 0.001 ↑ 1.0 1 1

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

13. 0.367 0.367 ↑ 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.367..0.367 rows=2,018 loops=1)

  • Index Cond: (company_id = $2)
  • Buffers: shared hit=9
14. 114.801 4,288.508 ↓ 87.5 350 1

HashAggregate (cost=341,236.80..341,239.81 rows=4 width=65) (actual time=4,288.406..4,288.508 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=281,099 read=417,598 written=3,689
  • I/O Timings: read=2,258.065 write=127.489
15. 86.125 4,173.707 ↑ 4.8 357,727 1

Merge Right Join (cost=268,074.13..293,944.56 rows=1,719,718 width=69) (actual time=4,049.533..4,173.707 rows=357,727 loops=1)

  • Merge Cond: ("*SELECT* 1".person_id = p.person_id)
  • Buffers: shared hit=281,099 read=417,598 written=3,689
  • I/O Timings: read=2,258.065 write=127.489
16. 47.025 4,033.713 ↓ 8.5 126,844 1

Sort (cost=231,355.23..231,392.56 rows=14,932 width=12) (actual time=4,014.369..4,033.713 rows=126,844 loops=1)

  • Sort Key: "*SELECT* 1".person_id
  • Sort Method: quicksort Memory: 9,018kB
  • Buffers: shared hit=269,748 read=417,533 written=3,689
  • I/O Timings: read=2,241.947 write=127.489
17. 11.037 3,986.688 ↓ 8.5 126,844 1

Append (cost=60,298.99..230,319.99 rows=14,932 width=12) (actual time=599.350..3,986.688 rows=126,844 loops=1)

  • Buffers: shared hit=269,748 read=417,533 written=3,689
  • I/O Timings: read=2,241.947 write=127.489
18. 7.487 628.615 ↓ 6.9 70,840 1

Subquery Scan on *SELECT* 1 (cost=60,298.99..60,504.25 rows=10,263 width=12) (actual time=599.349..628.615 rows=70,840 loops=1)

  • Buffers: shared hit=71,430 read=44,707
  • I/O Timings: read=246.227
19. 78.560 621.128 ↓ 6.9 70,840 1

HashAggregate (cost=60,298.99..60,401.62 rows=10,263 width=28) (actual time=599.347..621.128 rows=70,840 loops=1)

  • Group Key: audit_evs_all.ntfn_id, audit_evs_all.person_id, audit_evs_all.dvc_id
  • Buffers: shared hit=71,430 read=44,707
  • I/O Timings: read=246.227
20.          

Initplan (for HashAggregate)

21. 0.001 0.001 ↑ 1.0 1 1

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

22. 31.599 542.567 ↓ 6.9 70,840 1

Nested Loop (cost=226.96..60,170.68 rows=10,263 width=32) (actual time=10.532..542.567 rows=70,840 loops=1)

  • Buffers: shared hit=71,430 read=44,707
  • I/O Timings: read=246.227
23. 3.397 12.446 ↓ 21.9 4,373 1

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

  • Group Key: ev_ids.ev_id
  • Buffers: shared hit=4,266
24. 9.049 9.049 ↑ 2.3 4,373 1

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

  • Buffers: shared hit=4,266
25. 498.522 498.522 ↓ 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=40) (actual time=0.053..0.114 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=67,164 read=44,707
  • I/O Timings: read=246.227
26. 5.486 3,347.036 ↓ 12.0 56,004 1

Subquery Scan on *SELECT* 2 (cost=169,577.67..169,741.08 rows=4,669 width=12) (actual time=3,308.539..3,347.036 rows=56,004 loops=1)

  • Buffers: shared hit=198,318 read=372,826 written=3,689
  • I/O Timings: read=1,995.720 write=127.489
27. 27.460 3,341.550 ↓ 12.0 56,004 1

GroupAggregate (cost=169,577.67..169,694.39 rows=4,669 width=28) (actual time=3,308.537..3,341.550 rows=56,004 loops=1)

  • Group Key: audit_evs_all_arc.ntfn_id, audit_evs_all_arc.person_id, audit_evs_all_arc.dvc_id
  • Buffers: shared hit=198,318 read=372,826 written=3,689
  • I/O Timings: read=1,995.720 write=127.489
28.          

Initplan (for GroupAggregate)

29. 0.002 0.002 ↑ 1.0 1 1

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

30. 39.944 3,314.088 ↓ 12.0 56,004 1

Sort (cost=169,577.65..169,589.32 rows=4,669 width=32) (actual time=3,308.524..3,314.088 rows=56,004 loops=1)

  • Sort Key: audit_evs_all_arc.ntfn_id, audit_evs_all_arc.person_id, audit_evs_all_arc.dvc_id
  • Sort Method: quicksort Memory: 5,912kB
  • Buffers: shared hit=198,318 read=372,826 written=3,689
  • I/O Timings: read=1,995.720 write=127.489
31. 16.272 3,274.144 ↓ 12.0 56,004 1

Nested Loop (cost=226.96..169,293.10 rows=4,669 width=32) (actual time=1.800..3,274.144 rows=56,004 loops=1)

  • Buffers: shared hit=198,318 read=372,826 written=3,689
  • I/O Timings: read=1,995.720 write=127.489
32. 3.950 4.360 ↓ 21.9 4,373 1

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

  • Group Key: ev_ids_1.ev_id
33. 0.410 0.410 ↑ 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.410 rows=4,373 loops=1)

34. 3,253.512 3,253.512 ↓ 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=40) (actual time=0.042..0.744 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=198,318 read=372,826 written=3,689
  • I/O Timings: read=1,995.720 write=127.489
35. 19.021 53.869 ↓ 15.5 357,727 1

Sort (cost=36,718.89..36,776.48 rows=23,034 width=73) (actual time=35.157..53.869 rows=357,727 loops=1)

  • Sort Key: p.person_id
  • Sort Method: quicksort Memory: 113kB
  • Buffers: shared hit=11,351 read=65
  • I/O Timings: read=16.118
36. 0.208 34.848 ↑ 24.1 956 1

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

  • Hash Cond: ((r.target_name)::text = (e.sender)::text)
  • Buffers: shared hit=11,351 read=65
  • I/O Timings: read=16.118
37. 0.103 26.104 ↑ 3.5 864 1

Finalize GroupAggregate (cost=21,196.61..21,611.18 rows=3,011 width=73) (actual time=25.125..26.104 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,085 read=65
  • I/O Timings: read=16.118
38.          

Initplan (for Finalize GroupAggregate)

39. 0.002 0.002 ↑ 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.002 rows=1 loops=1)

40. 7.630 25.999 ↑ 2.9 864 1

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

  • Workers Planned: 2
  • Params Evaluated: $4
  • Workers Launched: 2
  • Buffers: shared hit=10,093 read=107
  • I/O Timings: read=16.568
41. 0.169 18.369 ↑ 4.4 288 3 / 3

Partial GroupAggregate (cost=20,196.56..20,237.35 rows=1,255 width=73) (actual time=18.185..18.369 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,093 read=107
  • I/O Timings: read=16.568
42. 0.247 18.200 ↑ 4.4 288 3 / 3

Sort (cost=20,196.56..20,199.70 rows=1,255 width=73) (actual time=18.178..18.200 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: 94kB
  • Worker 0: Sort Method: quicksort Memory: 41kB
  • Worker 1: Sort Method: quicksort Memory: 37kB
  • Buffers: shared hit=10,093 read=107
  • I/O Timings: read=16.568
43. 0.438 17.953 ↑ 4.4 288 3 / 3

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

  • Buffers: shared hit=10,043 read=107
  • I/O Timings: read=16.568
44. 0.148 15.787 ↑ 7.2 288 3 / 3

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

  • Buffers: shared hit=6,587 read=105
  • I/O Timings: read=16.543
45. 7.160 12.563 ↑ 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=10.614..12.563 rows=513 loops=3)

  • Recheck Cond: ((org_id = $4) AND (deleted_id = '-1'::integer))
  • Heap Blocks: exact=843
  • Buffers: shared hit=1,201 read=11
  • I/O Timings: read=15.828
46. 5.403 5.403 ↑ 5.0 1,548 1 / 3

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

  • Index Cond: (org_id = $4)
  • Buffers: shared hit=1 read=11
  • I/O Timings: read=15.828
47. 3.076 3.076 ↑ 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.006..0.006 rows=1 loops=1,538)

  • Index Cond: (dvc_id = r.recipient_id)
  • Buffers: shared hit=5,386 read=94
  • I/O Timings: read=0.715
48. 1.728 1.728 ↑ 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.006..0.006 rows=1 loops=864)

  • Index Cond: (person_id = d.person_id)
  • Buffers: shared hit=3,456 read=2
  • I/O Timings: read=0.025
49. 0.029 8.536 ↑ 11.0 139 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=4,266
50. 0.015 8.507 ↑ 11.0 139 1

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

  • Buffers: shared hit=4,266
51. 0.086 8.492 ↑ 11.0 139 1

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

  • Group Key: evs.sender, (max(evs.creation_date))
  • Buffers: shared hit=4,266
52. 0.014 8.406 ↑ 11.0 139 1

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

  • Buffers: shared hit=4,266
53. 1.012 4.827 ↑ 11.8 63 1

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

  • Group Key: evs.sender
  • Buffers: shared hit=2,358
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.001..0.002 rows=1 loops=1)

56. 3.379 3.813 ↑ 2.1 2,355 1

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

  • Recheck Cond: (company_id = $5)
  • Heap Blocks: exact=2,181
  • Buffers: shared hit=2,358
57. 0.434 0.434 ↑ 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.434..0.434 rows=2,775 loops=1)

  • Index Cond: (company_id = $5)
  • Buffers: shared hit=19
58. 0.812 3.565 ↑ 10.3 76 1

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

  • Group Key: evs_arc.sender
  • Buffers: shared hit=1,908
59.          

Initplan (for HashAggregate)

60. 0.001 0.001 ↑ 1.0 1 1

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

61. 2.431 2.752 ↑ 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.546..2.752 rows=2,018 loops=1)

  • Recheck Cond: (company_id = $6)
  • Heap Blocks: exact=1,899
  • Buffers: shared hit=1,908
62. 0.321 0.321 ↑ 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.321..0.321 rows=2,018 loops=1)

  • Index Cond: (company_id = $6)
  • Buffers: shared hit=9
Planning time : 3.344 ms
Execution time : 4,291.564 ms