explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 57nA

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 4,266.051 ↑ 363,000.0 1 1

Subquery Scan on q (cost=615,123.95..622,479.96 rows=363,000 width=429) (actual time=4,178.584..4,266.051 rows=1 loops=1)

2. 24.327 4,266.048 ↑ 363,000.0 1 1

Subquery Scan on a (cost=615,123.95..618,849.96 rows=363,000 width=397) (actual time=4,178.581..4,266.048 rows=1 loops=1)

  • Filter: (((a.status)::text <> 'DROPPED'::text) AND (a.gsrn = '871687400002064571'::bpchar))
  • Rows Removed by Filter: 154429
3. 218.958 4,241.721 ↓ 2.1 154,430 1

HashAggregate (cost=615,123.95..615,852.78 rows=72,883 width=141) (actual time=4,177.794..4,241.721 rows=154,430 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
4. 15.319 4,022.763 ↓ 2.1 154,430 1

Append (cost=185,308.59..611,479.80 rows=72,883 width=141) (actual time=2,267.970..4,022.763 rows=154,430 loops=1)

5. 20.295 2,334.525 ↓ 1.2 79,632 1

Result (cost=185,308.59..185,982.66 rows=67,407 width=137) (actual time=2,267.969..2,334.525 rows=79,632 loops=1)

6. 109.178 2,314.230 ↓ 1.2 79,632 1

HashAggregate (cost=185,308.59..185,982.66 rows=67,407 width=137) (actual time=2,267.964..2,314.230 rows=79,632 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), msgenv.exchangeid, msgenv.interchangeid, msgenv.envcreationdate
7. 7.412 2,205.052 ↓ 1.2 79,632 1

Append (cost=8,757.29..181,938.24 rows=67,407 width=137) (actual time=65.779..2,205.052 rows=79,632 loops=1)

8. 16.081 2,092.916 ↓ 1.2 68,896 1

Hash Left Join (cost=8,757.29..109,104.92 rows=58,021 width=133) (actual time=65.779..2,092.916 rows=68,896 loops=1)

  • Hash Cond: (msgenv.envid = mfu.envid)
9. 1,468.187 2,076.834 ↓ 1.2 68,896 1

Hash Right Join (cost=8,717.96..108,848.00 rows=58,021 width=129) (actual time=65.763..2,076.834 rows=68,896 loops=1)

  • Hash Cond: (msgtx.envid = msgenv.envid)
10. 542.920 542.920 ↓ 1.1 2,207,575 1

Seq Scan on msgtx (cost=0.00..77,749.60 rows=1,941,960 width=69) (actual time=0.003..542.920 rows=2,207,575 loops=1)

11. 16.932 65.727 ↓ 1.1 66,476 1

Hash (cost=7,992.70..7,992.70 rows=58,021 width=68) (actual time=65.727..65.727 rows=66,476 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 6851kB
12. 48.795 48.795 ↓ 1.1 66,476 1

Index Scan using test_status_idx on msgenv (cost=0.43..7,992.70 rows=58,021 width=68) (actual time=0.042..48.795 rows=66,476 loops=1)

  • Index Cond: ((status)::text = ANY ('{ERROR,DROPPED,REJECTED,NOT_SUPPORTED}'::text[]))
13. 0.001 0.001 ↓ 0.0 0 1

Hash (cost=39.26..39.26 rows=5 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
14. 0.000 0.000 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.28..39.26 rows=5 width=8) (actual time=0.000..0.000 rows=0 loops=1)

15. 0.000 0.000 ↓ 0.0 0 1

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

  • Filter: ((marketlevel)::text = 'ENV'::text)
16. 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
17. 3.203 104.724 ↓ 1.1 10,736 1

Subquery Scan on *SELECT* 2 (cost=5,026.55..72,253.11 rows=9,386 width=159) (actual time=46.905..104.724 rows=10,736 loops=1)

18. 4.171 101.521 ↓ 1.1 10,736 1

Hash Left Join (cost=5,026.55..72,159.25 rows=9,386 width=159) (actual time=46.903..101.521 rows=10,736 loops=1)

  • Hash Cond: (msgenv_1.envid = mfu_1.envid)
19. 4.189 97.348 ↓ 1.1 10,736 1

Hash Left Join (cost=4,987.23..72,084.72 rows=9,386 width=155) (actual time=46.883..97.348 rows=10,736 loops=1)

  • Hash Cond: ((msgtx_1.accesspointid)::bpchar = (dp.gsrn)::bpchar)
20. 8.563 46.366 ↓ 1.1 10,736 1

Nested Loop (cost=0.86..66,946.12 rows=9,386 width=148) (actual time=0.057..46.366 rows=10,736 loops=1)

21. 5.595 5.595 ↓ 1.1 10,736 1

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

  • Index Cond: ((status)::text = ANY ('{ERROR,DROPPED,REJECTED}'::text[]))
22. 32.208 32.208 ↑ 1.0 1 10,736

Index Scan using msgenv_pk on msgenv msgenv_1 (cost=0.43..7.01 rows=1 width=61) (actual time=0.003..0.003 rows=1 loops=10,736)

  • Index Cond: (envid = msgtx_1.envid)
23. 18.099 46.793 ↓ 1.0 86,834 1

Hash (cost=3,901.72..3,901.72 rows=86,772 width=26) (actual time=46.793..46.793 rows=86,834 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 4919kB
24. 28.694 28.694 ↓ 1.0 86,834 1

Seq Scan on deliverypoint dp (cost=0.00..3,901.72 rows=86,772 width=26) (actual time=0.007..28.694 rows=86,834 loops=1)

  • Filter: (deleted IS FALSE)
25. 0.001 0.002 ↓ 0.0 0 1

Hash (cost=39.26..39.26 rows=5 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
26. 0.000 0.001 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.28..39.26 rows=5 width=8) (actual time=0.001..0.001 rows=0 loops=1)

27. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: ((marketlevel)::text = 'TX'::text)
28. 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
29. 19.118 1,672.919 ↓ 13.7 74,798 1

Subquery Scan on *SELECT* 3 (cost=424,703.12..425,497.14 rows=5,476 width=184) (actual time=1,364.660..1,672.919 rows=74,798 loops=1)

30. 280.701 1,653.801 ↓ 13.7 74,798 1

GroupAggregate (cost=424,703.12..425,442.38 rows=5,476 width=184) (actual time=1,364.657..1,653.801 rows=74,798 loops=1)

  • Group Key: msgtx_2.externaltransactionid, msgenv_2.envid, message.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
31. 407.301 1,373.100 ↓ 13.8 75,773 1

Sort (cost=424,703.12..424,716.81 rows=5,476 width=184) (actual time=1,364.614..1,373.100 rows=75,773 loops=1)

  • Sort Key: msgtx_2.externaltransactionid, msgenv_2.envid, message.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
  • Sort Method: quicksort Memory: 23200kB
32. 52.519 965.799 ↓ 13.8 75,773 1

Hash Left Join (cost=4,989.23..424,363.09 rows=5,476 width=184) (actual time=48.121..965.799 rows=75,773 loops=1)

  • Hash Cond: ((message.gsrn)::text = (dp_1.gsrn)::text)
33. 6.525 865.435 ↓ 13.8 75,773 1

Nested Loop Left Join (cost=2.86..419,275.60 rows=5,476 width=177) (actual time=0.237..865.435 rows=75,773 loops=1)

34. 37.223 707.364 ↓ 13.8 75,773 1

Nested Loop Left Join (cost=2.43..392,311.18 rows=5,476 width=177) (actual time=0.224..707.364 rows=75,773 loops=1)

35. 61.473 670.141 ↓ 13.8 75,773 1

Nested Loop Left Join (cost=2.00..360,126.40 rows=5,476 width=177) (actual time=0.222..670.141 rows=75,773 loops=1)

36. 24.385 532.895 ↓ 13.8 75,773 1

Nested Loop Left Join (cost=1.57..323,253.49 rows=5,476 width=177) (actual time=0.207..532.895 rows=75,773 loops=1)

  • Join Filter: (mfu_2.envid = msgenv_2.envid)
37. 67.314 508.510 ↓ 13.8 75,773 1

Nested Loop (cost=1.29..322,803.51 rows=5,476 width=173) (actual time=0.203..508.510 rows=75,773 loops=1)

38. 41.715 289.650 ↓ 12.2 75,773 1

Nested Loop (cost=0.87..279,213.48 rows=6,207 width=120) (actual time=0.187..289.650 rows=75,773 loops=1)

39. 96.383 96.383 ↓ 5.5 75,776 1

Index Scan using message_msgstatus_idx on message (cost=0.44..181,432.12 rows=13,716 width=63) (actual time=0.169..96.383 rows=75,776 loops=1)

  • Index Cond: ((msgstatus)::text = ANY ('{ERROR,REJECTED,QUARANTINED}'::text[]))
  • Filter: (((counterpart)::text <> 'BackOffice'::text) AND ((msgdir)::text = 'IN'::text))
  • Rows Removed by Filter: 2409
40. 151.552 151.552 ↑ 1.0 1 75,776

Index Scan using msgenv_pk on msgenv msgenv_2 (cost=0.43..7.12 rows=1 width=57) (actual time=0.002..0.002 rows=1 loops=75,776)

  • Index Cond: (envid = message.envid)
41. 151.546 151.546 ↑ 1.0 1 75,773

Index Scan using msgtx_pk on msgtx msgtx_2 (cost=0.43..7.01 rows=1 width=65) (actual time=0.002..0.002 rows=1 loops=75,773)

  • Index Cond: ((txnbr = message.txnbr) AND (envid = message.envid))
42. 0.000 0.000 ↓ 0.0 0 75,773

Materialize (cost=0.28..39.29 rows=5 width=8) (actual time=0.000..0.000 rows=0 loops=75,773)

43. 0.000 0.002 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.28..39.26 rows=5 width=8) (actual time=0.002..0.002 rows=0 loops=1)

44. 0.002 0.002 ↓ 0.0 0 1

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

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

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

  • Index Cond: (parametervalueid = mfu_2.followupstatusparamid)
  • Heap Fetches: 0
46. 75.773 75.773 ↓ 0.0 0 75,773

Index Only Scan using processmessages_pk on processmessages (cost=0.43..6.72 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=75,773)

  • Index Cond: (messageid = message.messageid)
  • Heap Fetches: 0
47. 0.000 0.000 ↓ 0.0 0 75,773

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

  • Index Cond: (processmessages.processid = processid)
48. 151.546 151.546 ↑ 1.0 1 75,773

Index Only Scan using exchange_pkey on exchange (cost=0.43..4.91 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=75,773)

  • Index Cond: (exchangeid = message.exchangeid)
  • Heap Fetches: 28
49. 18.797 47.845 ↓ 1.0 86,834 1

Hash (cost=3,901.72..3,901.72 rows=86,772 width=26) (actual time=47.845..47.845 rows=86,834 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 4919kB
50. 29.048 29.048 ↓ 1.0 86,834 1

Seq Scan on deliverypoint dp_1 (cost=0.00..3,901.72 rows=86,772 width=26) (actual time=0.010..29.048 rows=86,834 loops=1)

  • Filter: (deleted IS FALSE)
Planning time : 12.007 ms
Execution time : 4,267.168 ms