explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W8De

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 1,487.683 ↑ 1.0 1 1

GroupAggregate (cost=22,375.69..22,375.71 rows=1 width=16) (actual time=1,487.683..1,487.683 rows=1 loops=1)

  • Group Key: ae.person_delivery_status
2.          

CTE get_subscription_audits

3. 576.794 576.794 ↓ 3.1 5,646 1

Index Scan using idx_audit_evs_all_eia on audit_evs_all ae_sub_1 (cost=0.56..6,883.75 rows=1,807 width=8) (actual time=59.876..576.794 rows=5,646 loops=1)

  • Index Cond: ((ev_id = 1171307000) AND ((ap_audit_ev_type)::text = ANY ('{CREATE_SUBSCRIPTION,CREATE_SUBSCRIPTION_NOTIFICATION,CREATE_SUBSCRIPTION_WITH_DELAY}'::text[])))
4. 0.069 1,487.635 ↓ 14.0 14 1

Sort (cost=15,491.94..15,491.95 rows=1 width=16) (actual time=1,487.634..1,487.635 rows=14 loops=1)

  • Sort Key: ae.person_delivery_status
  • Sort Method: quicksort Memory: 26kB
5. 0.017 1,487.566 ↓ 14.0 14 1

Nested Loop (cost=13,277.46..15,491.93 rows=1 width=16) (actual time=1,487.456..1,487.566 rows=14 loops=1)

6. 0.006 1,467.809 ↑ 43.9 14 1

Unique (cost=13,276.89..13,279.96 rows=614 width=32) (actual time=1,467.797..1,467.809 rows=14 loops=1)

7. 0.101 1,467.803 ↑ 18.1 34 1

Sort (cost=13,276.89..13,278.43 rows=614 width=32) (actual time=1,467.796..1,467.803 rows=34 loops=1)

  • Sort Key: aei.person_id, aei.delivery_status_order, aei.insert_order DESC
  • Sort Method: quicksort Memory: 27kB
8. 0.048 1,467.702 ↑ 18.1 34 1

Nested Loop Anti Join (cost=60.14..13,248.46 rows=614 width=32) (actual time=750.857..1,467.702 rows=34 loops=1)

  • Join Filter: (aei.delivery_status_order = 1)
9. 1.392 1,467.382 ↑ 18.1 34 1

Hash Anti Join (cost=59.29..10,852.17 rows=615 width=32) (actual time=750.785..1,467.382 rows=34 loops=1)

  • Hash Cond: (aei.root_ntfn_id = ae_sub.ntfn_id)
10. 849.858 883.649 ↓ 4.3 5,156 1

Index Scan using idx_audit_evs_all_eipdd on audit_evs_all aei (cost=0.56..10,652.09 rows=1,199 width=40) (actual time=54.985..883.649 rows=5,156 loops=1)

  • Index Cond: ((ev_id = 1171307000) AND (init_or_conclusion = 'N'::bpchar) AND (person_id IS NOT NULL) AND (delivery_status_order IS NOT NULL))
  • Filter: (((response IS NULL) OR (lower((response)::text) <> ALL ('{respond,excalate,terminate,fart}'::text[]))) AND ((delivery_status_order <> 2) OR (NOT (alternatives: SubPlan 2 or hashed SubPlan 3))))
11.          

SubPlan (for Index Scan)

12. 0.000 0.000 ↓ 0.0 0

Index Only Scan using aea_ev_id_person_id_dvc_id_dso_pidx on audit_evs_all aef_1 (cost=0.43..3.45 rows=1 width=0) (never executed)

  • Index Cond: ((ev_id = 1171307000) AND (person_id = aei.person_id) AND (dvc_id = aei.dvc_id))
  • Heap Fetches: 0
13. 33.791 33.791 ↑ 4,085.0 1 1

Index Only Scan using aea_ev_id_person_id_dvc_id_dso_pidx on audit_evs_all aef_2 (cost=0.43..1,617.76 rows=4,085 width=16) (actual time=33.789..33.791 rows=1 loops=1)

  • Index Cond: (ev_id = 1171307000)
  • Heap Fetches: 1
14. 2.383 582.341 ↓ 3.1 5,646 1

Hash (cost=36.14..36.14 rows=1,807 width=8) (actual time=582.341..582.341 rows=5,646 loops=1)

  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 285kB
15. 579.958 579.958 ↓ 3.1 5,646 1

CTE Scan on get_subscription_audits ae_sub (cost=0.00..36.14 rows=1,807 width=8) (actual time=59.883..579.958 rows=5,646 loops=1)

16. 0.034 0.272 ↓ 0.0 0 34

Nested Loop (cost=0.84..3.88 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=34)

17. 0.238 0.238 ↓ 0.0 0 34

Index Only Scan using aea_ev_id_person_id_dvc_id_dso_pidx on audit_evs_all aef (cost=0.43..2.28 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=34)

  • Index Cond: ((ev_id = 1171307000) AND (person_id = aei.person_id) AND (dvc_id = aei.dvc_id))
  • Heap Fetches: 0
18. 0.000 0.000 ↓ 0.0 0

Index Scan using dvc_pk on dvc (cost=0.42..1.61 rows=1 width=8) (never executed)

  • Index Cond: (dvc_id = aef.dvc_id)
  • Filter: ((dvc_type)::text = 'TEXT_PHONE'::text)
19. 19.740 19.740 ↑ 1.0 1 14

Index Scan using audit_ev_insert_order on audit_evs_all ae (cost=0.56..3.58 rows=1 width=24) (actual time=1.410..1.410 rows=1 loops=14)

  • Index Cond: (insert_order = aei.insert_order)
  • Filter: ((ev_id = 1171307000) AND (aei.person_id = person_id))
Planning time : 783.616 ms
Execution time : 1,488.385 ms