explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hLWR

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 12.528 ↑ 3.0 1 1

HashAggregate (cost=4,610.95..4,610.98 rows=3 width=159) (actual time=12.528..12.528 rows=1 loops=1)

  • Group Key: msgtx.externaltransactionid, msgenv.envid, msgtx.txnbr, (NULL::text), (NULL::text), (NULL::text), msgenv.creationdate, (NULL::bpchar), (false), ('ENV'::text), msgtx.type, msgenv.direction, msgenv.status, (NULL::date), (NULL::character varying), pv.parametervalueid, (((0))::bigint), msgenv.exchangeid, msgenv.interchangeid, msgenv.envcreationdate
2. 0.001 12.522 ↑ 3.0 1 1

Append (cost=3,754.05..4,610.80 rows=3 width=159) (actual time=12.521..12.522 rows=1 loops=1)

3. 0.000 10.568 ↓ 0.0 0 1

Result (cost=3,754.05..3,754.15 rows=2 width=146) (actual time=10.568..10.568 rows=0 loops=1)

4. 0.001 10.568 ↓ 0.0 0 1

Unique (cost=3,754.05..3,754.15 rows=2 width=146) (actual time=10.568..10.568 rows=0 loops=1)

5. 0.018 10.567 ↓ 0.0 0 1

Sort (cost=3,754.05..3,754.05 rows=2 width=146) (actual time=10.567..10.567 rows=0 loops=1)

  • Sort Key: msgtx.externaltransactionid, msgenv.envid, msgtx.txnbr, (NULL::text), (NULL::text), (NULL::text), msgenv.creationdate, (NULL::bpchar), (false), ('ENV'::text), msgtx.type, msgenv.direction, msgenv.status, (NULL::date), (NULL::character varying), pv.parametervalueid, (0), msgenv.exchangeid, msgenv.interchangeid, msgenv.envcreationdate
  • Sort Method: quicksort Memory: 25kB
6. 0.000 10.549 ↓ 0.0 0 1

Append (cost=1.69..3,754.04 rows=2 width=146) (actual time=10.549..10.549 rows=0 loops=1)

7. 0.005 4.173 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.69..2,614.00 rows=1 width=133) (actual time=4.173..4.173 rows=0 loops=1)

8. 0.000 4.168 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.42..2,610.50 rows=1 width=133) (actual time=4.168..4.168 rows=0 loops=1)

  • Join Filter: (mfu.envid = msgenv.envid)
9. 0.002 4.168 ↓ 0.0 0 1

Nested Loop (cost=1.42..2,588.68 rows=1 width=129) (actual time=4.168..4.168 rows=0 loops=1)

10. 0.000 4.151 ↑ 10.3 3 1

Nested Loop (cost=0.99..2,412.19 rows=31 width=65) (actual time=0.145..4.151 rows=3 loops=1)

11. 1.611 1.611 ↓ 7.5 1,444 1

Index Scan using message_gsrn_idx on message (cost=0.56..783.90 rows=193 width=4) (actual time=0.033..1.611 rows=1,444 loops=1)

  • Index Cond: ((gsrn)::text = '871687400002064571'::text)
12. 2.888 2.888 ↓ 0.0 0 1,444

Index Scan using "IX_Relationship26" on msgtx (cost=0.43..8.43 rows=1 width=69) (actual time=0.002..0.002 rows=0 loops=1,444)

  • Index Cond: (kernelmessageid = message.messageid)
13. 0.015 0.015 ↓ 0.0 0 3

Index Scan using msgenv_pk on msgenv (cost=0.43..5.68 rows=1 width=68) (actual time=0.005..0.005 rows=0 loops=3)

  • Index Cond: (envid = msgtx.envid)
  • Filter: (((status)::text <> 'DROPPED'::text) AND ((status)::text = ANY ('{ERROR,DROPPED,REJECTED,NOT_SUPPORTED}'::text[])))
  • Rows Removed by Filter: 1
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on marketfollowup mfu (cost=0.00..21.75 rows=5 width=8) (never executed)

  • Filter: ((marketlevel)::text = 'ENV'::text)
15. 0.000 0.000 ↓ 0.0 0

Index Only Scan using parametervalueid on parametervalue pv (cost=0.28..3.49 rows=1 width=4) (never executed)

  • Index Cond: (parametervalueid = mfu.followupstatusparamid)
  • Heap Fetches: 0
16. 0.001 6.376 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=9.58..1,140.03 rows=1 width=159) (actual time=6.376..6.376 rows=0 loops=1)

17. 0.000 6.375 ↓ 0.0 0 1

Nested Loop Left Join (cost=9.58..1,140.02 rows=1 width=159) (actual time=6.375..6.375 rows=0 loops=1)

18. 0.000 6.375 ↓ 0.0 0 1

Nested Loop Left Join (cost=9.31..1,136.52 rows=1 width=159) (actual time=6.375..6.375 rows=0 loops=1)

  • Join Filter: (mfu_1.envid = msgenv_1.envid)
19. 0.006 6.375 ↓ 0.0 0 1

Nested Loop (cost=9.31..1,114.70 rows=1 width=155) (actual time=6.375..6.375 rows=0 loops=1)

20. 0.718 6.369 ↓ 0.0 0 1

Hash Join (cost=8.88..1,107.68 rows=1 width=98) (actual time=6.369..6.369 rows=0 loops=1)

  • Hash Cond: ((msgtx_1.accesspointid)::bpchar = (dp.gsrn)::bpchar)
21. 5.626 5.626 ↓ 1.1 10,736 1

Index Scan using msgtx_status_idx on msgtx msgtx_1 (cost=0.43..1,064.03 rows=9,386 width=91) (actual time=0.027..5.626 rows=10,736 loops=1)

  • Index Cond: ((status)::text = ANY ('{ERROR,DROPPED,REJECTED}'::text[]))
  • Filter: ((status)::text <> 'DROPPED'::text)
22. 0.003 0.025 ↑ 1.0 1 1

Hash (cost=8.44..8.44 rows=1 width=26) (actual time=0.025..0.025 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
23. 0.022 0.022 ↑ 1.0 1 1

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp (cost=0.42..8.44 rows=1 width=26) (actual time=0.022..0.022 rows=1 loops=1)

  • Index Cond: ((gsrn)::text = '871687400002064571'::text)
  • Filter: (deleted IS FALSE)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using msgenv_pk on msgenv msgenv_1 (cost=0.43..7.01 rows=1 width=61) (never executed)

  • Index Cond: (envid = msgtx_1.envid)
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on marketfollowup mfu_1 (cost=0.00..21.75 rows=5 width=8) (never executed)

  • Filter: ((marketlevel)::text = 'TX'::text)
26. 0.000 0.000 ↓ 0.0 0

Index Only Scan using parametervalueid on parametervalue pv_1 (cost=0.28..3.49 rows=1 width=4) (never executed)

  • Index Cond: (parametervalueid = mfu_1.followupstatusparamid)
  • Heap Fetches: 0
27. 0.004 1.953 ↑ 1.0 1 1

Subquery Scan on *SELECT* 3 (cost=856.56..856.65 rows=1 width=184) (actual time=1.952..1.953 rows=1 loops=1)

28. 0.033 1.949 ↑ 1.0 1 1

HashAggregate (cost=856.56..856.64 rows=1 width=184) (actual time=1.949..1.949 rows=1 loops=1)

  • Group Key: msgtx_2.externaltransactionid, msgenv_2.envid, message_1.gsrn, (dp_1.deliverypointid IS NOT NULL), 'MSG'::text, msgtx_2.type, msgenv_2.direction, dp_1.market, pv_2.parametervalueid, exchange.exchangeid, msgtx_2.txnbr, msgenv_2.interchangeid, msgenv_2.envcreationdate, msgenv_2.creationdate
29. 0.005 1.916 ↑ 1.0 1 1

Nested Loop Left Join (cost=3.40..856.51 rows=1 width=184) (actual time=1.894..1.916 rows=1 loops=1)

  • Join Filter: ((dp_1.gsrn)::text = (message_1.gsrn)::text)
30. 0.004 1.886 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.98..848.06 rows=1 width=177) (actual time=1.864..1.886 rows=1 loops=1)

31. 0.009 1.882 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.71..844.56 rows=1 width=177) (actual time=1.861..1.882 rows=1 loops=1)

  • Join Filter: (mfu_2.envid = msgenv_2.envid)
32. 0.003 1.873 ↑ 1.0 1 1

Nested Loop (cost=2.71..822.75 rows=1 width=173) (actual time=1.852..1.873 rows=1 loops=1)

  • Join Filter: (message_1.envid = msgenv_2.envid)
33. 0.005 1.859 ↑ 1.0 1 1

Nested Loop (cost=2.28..816.84 rows=1 width=124) (actual time=1.838..1.859 rows=1 loops=1)

34. 0.003 1.847 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.85..808.38 rows=1 width=63) (actual time=1.827..1.847 rows=1 loops=1)

35. 0.002 1.836 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.42..799.92 rows=1 width=63) (actual time=1.816..1.836 rows=1 loops=1)

36. 0.003 1.833 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.99..794.04 rows=1 width=63) (actual time=1.814..1.833 rows=1 loops=1)

37. 1.817 1.817 ↑ 1.0 1 1

Index Scan using message_gsrn_idx on message message_1 (cost=0.56..785.58 rows=1 width=63) (actual time=1.798..1.817 rows=1 loops=1)

  • Index Cond: ((gsrn)::text = '871687400002064571'::text)
  • Filter: (((counterpart)::text <> 'BackOffice'::text) AND ((msgdir)::text = 'IN'::text) AND ((msgstatus)::text = ANY ('{ERROR,REJECTED,QUARANTINED}'::text[])))
  • Rows Removed by Filter: 1443
38. 0.013 0.013 ↓ 0.0 0 1

Index Scan using "IX_processmessages_messageid_processmessages" on processmessages (cost=0.43..8.45 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (message_1.messageid = messageid)
39. 0.001 0.001 ↓ 0.0 0 1

Index Scan using process_pkey on process (cost=0.43..5.87 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (processmessages.processid = processid)
40. 0.008 0.008 ↑ 1.0 1 1

Index Only Scan using exchange_pkey on exchange (cost=0.43..8.45 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (exchangeid = message_1.exchangeid)
  • Heap Fetches: 0
41. 0.007 0.007 ↑ 1.0 1 1

Index Scan using msgtx_pk on msgtx msgtx_2 (cost=0.43..8.45 rows=1 width=65) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: ((txnbr = message_1.txnbr) AND (envid = message_1.envid))
42. 0.011 0.011 ↑ 1.0 1 1

Index Scan using msgenv_pk on msgenv msgenv_2 (cost=0.43..5.89 rows=1 width=57) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (envid = msgtx_2.envid)
43. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on marketfollowup mfu_2 (cost=0.00..21.75 rows=5 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((marketlevel)::text = 'MSG'::text)
44. 0.000 0.000 ↓ 0.0 0 1

Index Only Scan using parametervalueid on parametervalue pv_2 (cost=0.28..3.49 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (parametervalueid = mfu_2.followupstatusparamid)
  • Heap Fetches: 0
45. 0.025 0.025 ↑ 1.0 1 1

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp_1 (cost=0.42..8.44 rows=1 width=26) (actual time=0.025..0.025 rows=1 loops=1)

  • Index Cond: ((gsrn)::text = '871687400002064571'::text)
  • Filter: (deleted IS FALSE)
Planning time : 6.141 ms
Execution time : 12.962 ms