explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 70.0 350 1

Sort (cost=798,394.93..798,394.94 rows=5 width=65) (actual rows=350 loops=1)

  • Sort Key: w.userid, w.username
  • Sort Method: quicksort Memory: 53kB
  • Buffers: shared hit=177,391 read=520,321 dirtied=21,878 written=369
  • I/O Timings: read=554,954.413 write=30.506
2.          

CTE cte_org

3. 0.000 0.000 ↑ 1.0 1 1

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

4.          

CTE ev_ids

5. 0.000 0.000 ↑ 2.3 4,364 1

Append (cost=103.13..12,599.89 rows=10,062 width=8) (actual rows=4,364 loops=1)

  • Buffers: shared hit=2,349 read=1,908
  • I/O Timings: read=15.042
6. 0.000 0.000 ↑ 2.1 2,346 1

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

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

Initplan (for Bitmap Heap Scan)

8. 0.000 0.000 ↑ 1.0 1 1

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

9. 0.000 0.000 ↑ 1.8 2,749 1

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

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

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

Initplan (for Bitmap Heap Scan)

12. 0.000 0.000 ↑ 1.0 1 1

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

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

  • Index Cond: (company_id = $2)
  • Buffers: shared read=9
  • I/O Timings: read=0.076
14. 0.000 0.000 ↓ 70.0 350 1

HashAggregate (cost=785,791.77..785,794.92 rows=5 width=65) (actual rows=350 loops=1)

  • Group Key: w.userid, w.username
  • Filter: ((max(w.last_login) IS NULL) OR (max("*SELECT* 1_1".max_date) IS NULL) OR (max(("*SELECT* 1".creation_date)::date) IS NULL))
  • Rows Removed by Filter: 56
  • Buffers: shared hit=177,391 read=520,321 dirtied=21,878 written=369
  • I/O Timings: read=554,954.413 write=30.506
15. 0.000 0.000 ↑ 3.6 3,299,457 1

Merge Right Join (cost=283,340.80..460,724.75 rows=11,820,619 width=69) (actual rows=3,299,457 loops=1)

  • Merge Cond: ("*SELECT* 1_1".person_id = w.person_id)
  • Buffers: shared hit=177,391 read=520,321 dirtied=21,878 written=369
  • I/O Timings: read=554,954.413 write=30.506
16. 0.000 0.000 ↓ 8.4 126,137 1

Sort (cost=231,355.23..231,392.56 rows=14,932 width=12) (actual rows=126,137 loops=1)

  • Sort Key: "*SELECT* 1_1".person_id
  • Sort Method: quicksort Memory: 8,985kB
  • Buffers: shared hit=168,344 read=518,231 dirtied=21,878 written=369
  • I/O Timings: read=554,888.481 write=30.506
17. 0.000 0.000 ↓ 8.4 126,137 1

Append (cost=60,298.99..230,319.99 rows=14,932 width=12) (actual rows=126,137 loops=1)

  • Buffers: shared hit=168,344 read=518,231 dirtied=21,878 written=369
  • I/O Timings: read=554,888.481 write=30.506
18. 0.000 0.000 ↓ 6.8 70,133 1

Subquery Scan on *SELECT* 1_1 (cost=60,298.99..60,504.25 rows=10,263 width=12) (actual rows=70,133 loops=1)

  • Buffers: shared hit=69,009 read=46,458
  • I/O Timings: read=80,277.557
19. 0.000 0.000 ↓ 6.8 70,133 1

HashAggregate (cost=60,298.99..60,401.62 rows=10,263 width=28) (actual rows=70,133 loops=1)

  • Group Key: audit_evs_all.ntfn_id, audit_evs_all.person_id, audit_evs_all.dvc_id
  • Buffers: shared hit=69,009 read=46,458
  • I/O Timings: read=80,277.557
20.          

Initplan (for HashAggregate)

21. 0.000 0.000 ↑ 1.0 1 1

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

22. 0.000 0.000 ↓ 6.8 70,133 1

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

  • Buffers: shared hit=69,009 read=46,458
  • I/O Timings: read=80,277.557
23. 0.000 0.000 ↓ 21.8 4,364 1

HashAggregate (cost=226.40..228.40 rows=200 width=8) (actual rows=4,364 loops=1)

  • Group Key: ev_ids.ev_id
  • Buffers: shared hit=2,349 read=1,908
  • I/O Timings: read=15.042
24. 0.000 0.000 ↑ 2.3 4,364 1

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

  • Buffers: shared hit=2,349 read=1,908
  • I/O Timings: read=15.042
25. 0.000 0.000 ↓ 16.0 16 4,364

Index Scan using idx_audit_evs_all_eia on audit_evs_all (cost=0.56..299.70 rows=1 width=40) (actual rows=16 loops=4,364)

  • 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=66,660 read=44,550
  • I/O Timings: read=80,262.516
26. 0.000 0.000 ↓ 12.0 56,004 1

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

  • Buffers: shared hit=99,335 read=471,773 dirtied=21,878 written=369
  • I/O Timings: read=474,610.924 write=30.506
27. 0.000 0.000 ↓ 12.0 56,004 1

GroupAggregate (cost=169,577.67..169,694.39 rows=4,669 width=28) (actual 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=99,335 read=471,773 dirtied=21,878 written=369
  • I/O Timings: read=474,610.924 write=30.506
28.          

Initplan (for GroupAggregate)

29. 0.000 0.000 ↑ 1.0 1 1

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

30. 0.000 0.000 ↓ 12.0 56,004 1

Sort (cost=169,577.65..169,589.32 rows=4,669 width=32) (actual 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=99,335 read=471,773 dirtied=21,878 written=369
  • I/O Timings: read=474,610.924 write=30.506
31. 0.000 0.000 ↓ 12.0 56,004 1

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

  • Buffers: shared hit=99,335 read=471,773 dirtied=21,878 written=369
  • I/O Timings: read=474,610.924 write=30.506
32. 0.000 0.000 ↓ 21.8 4,364 1

HashAggregate (cost=226.40..228.40 rows=200 width=8) (actual rows=4,364 loops=1)

  • Group Key: ev_ids_1.ev_id
33. 0.000 0.000 ↑ 2.3 4,364 1

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

34. 0.000 0.000 ↓ 13.0 13 4,364

Index Scan using idx_audit_proc_evs_arc_evid on audit_evs_all_arc (cost=0.56..845.31 rows=1 width=40) (actual rows=13 loops=4,364)

  • 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=99,335 read=471,773 dirtied=21,878 written=369
  • I/O Timings: read=474,610.924 write=30.506
35. 0.000 0.000 ↓ 20.8 3,299,457 1

Sort (cost=51,985.57..52,381.38 rows=158,326 width=73) (actual rows=3,299,457 loops=1)

  • Sort Key: w.person_id
  • Sort Method: quicksort Memory: 777kB
  • Buffers: shared hit=9,047 read=2,090
  • I/O Timings: read=65.933
36. 0.000 0.000 ↑ 30.6 5,173 1

Merge Left Join (cost=35,921.48..38,312.11 rows=158,326 width=73) (actual rows=5,173 loops=1)

  • Merge Cond: ((w.userid)::text = ("*SELECT* 1".sender)::text)
  • Buffers: shared hit=9,047 read=2,090
  • I/O Timings: read=65.933
37. 0.000 0.000 ↑ 3.6 864 1

Sort (cost=22,652.64..22,660.50 rows=3,147 width=65) (actual rows=864 loops=1)

  • Sort Key: w.userid
  • Sort Method: quicksort Memory: 96kB
  • Buffers: shared hit=6,697 read=183
  • I/O Timings: read=54.928
38. 0.000 0.000 ↑ 3.6 864 1

Subquery Scan on w (cost=22,005.23..22,469.80 rows=3,147 width=65) (actual rows=864 loops=1)

  • Buffers: shared hit=6,697 read=183
  • I/O Timings: read=54.928
39. 0.000 0.000 ↑ 3.6 864 1

Finalize GroupAggregate (cost=22,005.23..22,438.33 rows=3,147 width=73) (actual 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=6,697 read=183
  • I/O Timings: read=54.928
40.          

Initplan (for Finalize GroupAggregate)

41. 0.000 0.000 ↑ 1.0 1 1

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

42. 0.000 0.000 ↑ 3.0 864 1

Gather Merge (cost=22,005.21..22,350.46 rows=2,622 width=73) (actual rows=864 loops=1)

  • Workers Planned: 2
  • Params Evaluated: $4
  • Workers Launched: 2
  • Buffers: shared hit=9,942 read=258
  • I/O Timings: read=72.414
43. 0.000 0.000 ↑ 4.6 288 3 / 3

Partial GroupAggregate (cost=21,005.19..21,047.79 rows=1,311 width=73) (actual 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=9,942 read=258
  • I/O Timings: read=72.414
44. 0.000 0.000 ↑ 4.6 288 3 / 3

Sort (cost=21,005.19..21,008.46 rows=1,311 width=73) (actual 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: 92kB
  • Worker 0: Sort Method: quicksort Memory: 41kB
  • Worker 1: Sort Method: quicksort Memory: 38kB
  • Buffers: shared hit=9,942 read=258
  • I/O Timings: read=72.414
45. 0.000 0.000 ↑ 4.6 288 3 / 3

Nested Loop (cost=159.59..20,937.30 rows=1,311 width=73) (actual rows=288 loops=3)

  • Buffers: shared hit=9,892 read=258
  • I/O Timings: read=72.414
46. 0.000 0.000 ↑ 7.5 288 3 / 3

Nested Loop (cost=159.17..19,934.75 rows=2,161 width=29) (actual rows=288 loops=3)

  • Buffers: shared hit=6,434 read=258
  • I/O Timings: read=72.414
47. 0.000 0.000 ↑ 6.5 513 3 / 3

Parallel Bitmap Heap Scan on recipients r (cost=158.74..10,527.13 rows=3,350 width=21) (actual rows=513 loops=3)

  • Recheck Cond: ((org_id = $4) AND (deleted_id = '-1'::integer))
  • Heap Blocks: exact=806
  • Buffers: shared hit=1,212
48. 0.000 0.000 ↑ 5.2 1,541 1 / 3

Bitmap Index Scan on idx_recipients_del_org_id_cat (cost=0.00..156.73 rows=8,040 width=0) (actual rows=1,541 loops=1)

  • Index Cond: (org_id = $4)
  • Buffers: shared hit=12
49. 0.000 0.000 ↑ 1.0 1 1,538 / 3

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

  • Index Cond: (dvc_id = r.recipient_id)
  • Buffers: shared hit=5,222 read=258
  • I/O Timings: read=72.414
50. 0.000 0.000 ↑ 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 rows=1 loops=864)

  • Index Cond: (person_id = d.person_id)
  • Buffers: shared hit=3,458
51. 0.000 0.000 ↑ 1.4 7,383 1

Sort (cost=13,268.85..13,294.00 rows=10,062 width=20) (actual rows=7,383 loops=1)

  • Sort Key: "*SELECT* 1".sender
  • Sort Method: quicksort Memory: 510kB
  • Buffers: shared hit=2,350 read=1,907
  • I/O Timings: read=11.004
52. 0.000 0.000 ↑ 2.3 4,364 1

Append (cost=103.13..12,599.89 rows=10,062 width=20) (actual rows=4,364 loops=1)

  • Buffers: shared hit=2,350 read=1,907
  • I/O Timings: read=11.004
53. 0.000 0.000 ↑ 2.1 2,346 1

Subquery Scan on *SELECT* 1 (cost=103.13..6,111.71 rows=4,927 width=20) (actual rows=2,346 loops=1)

  • Buffers: shared hit=2,349
54. 0.000 0.000 ↑ 2.1 2,346 1

Bitmap Heap Scan on evs (cost=103.13..6,062.44 rows=4,927 width=20) (actual rows=2,346 loops=1)

  • Recheck Cond: (company_id = $5)
  • Heap Blocks: exact=2,174
  • Buffers: shared hit=2,349
55.          

Initplan (for Bitmap Heap Scan)

56. 0.000 0.000 ↑ 1.0 1 1

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

57. 0.000 0.000 ↑ 1.8 2,749 1

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

  • Index Cond: (company_id = $5)
  • Buffers: shared hit=19
58. 0.000 0.000 ↑ 2.5 2,018 1

Subquery Scan on *SELECT* 2 (cost=62.74..6,437.88 rows=5,135 width=20) (actual rows=2,018 loops=1)

  • Buffers: shared hit=1 read=1,907
  • I/O Timings: read=11.004
59. 0.000 0.000 ↑ 2.5 2,018 1

Bitmap Heap Scan on evs_arc (cost=62.74..6,386.53 rows=5,135 width=20) (actual 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=11.004
60.          

Initplan (for Bitmap Heap Scan)

61. 0.000 0.000 ↑ 1.0 1 1

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

62. 0.000 0.000 ↑ 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 rows=2,018 loops=1)

  • Index Cond: (company_id = $6)
  • Buffers: shared read=9
  • I/O Timings: read=0.069