explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9VFH

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 3,444.209 ↑ 1.0 10 1

Limit (cost=3,011.19..3,980.58 rows=10 width=495) (actual time=337.972..3,444.209 rows=10 loops=1)

2. 0.018 3,444.204 ↑ 20.0 10 1

Nested Loop Left Join (cost=3,011.19..22,398.97 rows=200 width=495) (actual time=337.971..3,444.204 rows=10 loops=1)

3. 0.037 26.886 ↑ 20.0 10 1

Hash Right Join (cost=3,010.19..3,361.65 rows=200 width=225) (actual time=26.848..26.886 rows=10 loops=1)

  • Hash Cond: ((deliverypoint.gsrn)::text = (message.gsrn)::text)
4. 0.051 0.051 ↑ 289.9 20 1

Seq Scan on deliverypoint (cost=0.00..189.99 rows=5,799 width=145) (actual time=0.037..0.051 rows=20 loops=1)

  • Filter: (deleted IS FALSE)
5. 0.431 26.798 ↓ 14.3 2,869 1

Hash (cost=3,007.69..3,007.69 rows=200 width=80) (actual time=26.798..26.798 rows=2,869 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 194kB
6. 10.855 26.367 ↓ 14.3 2,869 1

Sort (cost=3,005.19..3,005.69 rows=200 width=56) (actual time=26.083..26.367 rows=2,869 loops=1)

  • Sort Key: message.gsrn
  • Sort Method: quicksort Memory: 321kB
7. 2.914 15.512 ↓ 14.3 2,869 1

HashAggregate (cost=2,995.05..2,997.55 rows=200 width=56) (actual time=14.650..15.512 rows=2,869 loops=1)

  • Group Key: message.gsrn
8. 2.023 12.598 ↓ 4.1 3,033 1

HashAggregate (cost=2,972.76..2,980.19 rows=743 width=23) (actual time=12.052..12.598 rows=3,033 loops=1)

  • Group Key: message.gsrn, (min(message.creationdate)), (max(message.creationdate)), (count(*))
9. 0.272 10.575 ↓ 4.1 3,033 1

Append (cost=1,694.00..2,965.33 rows=743 width=23) (actual time=2.559..10.575 rows=3,033 loops=1)

10. 0.581 2.627 ↓ 17.5 193 1

HashAggregate (cost=1,694.00..1,694.11 rows=11 width=23) (actual time=2.558..2.627 rows=193 loops=1)

  • Group Key: message.gsrn
11. 2.046 2.046 ↓ 1.8 469 1

Index Scan using message_msgstatus_idx on message (cost=0.42..1,691.35 rows=265 width=23) (actual time=0.037..2.046 rows=469 loops=1)

  • Index Cond: ((msgstatus)::text = ANY ('{REJECTED,QUARANTINED,ERROR}'::text[]))
  • Filter: ((gsrn IS NOT NULL) AND ((counterpart)::text ~~* '%adapter%'::text))
  • Rows Removed by Filter: 89
12. 3.221 7.676 ↓ 3.9 2,840 1

HashAggregate (cost=1,256.47..1,263.79 rows=732 width=23) (actual time=6.955..7.676 rows=2,840 loops=1)

  • Group Key: msgtx.accesspointid
13. 3.308 4.455 ↑ 1.4 3,800 1

Bitmap Heap Scan on msgtx (cost=113.24..1,204.20 rows=5,227 width=23) (actual time=1.341..4.455 rows=3,800 loops=1)

  • Recheck Cond: ((status)::text = ANY ('{REJECTED,ERROR,DROPPED}'::text[]))
  • Filter: (accesspointid IS NOT NULL)
  • Rows Removed by Filter: 1899
  • Heap Blocks: exact=678
14. 1.147 1.147 ↑ 1.0 5,699 1

Bitmap Index Scan on msgtx_operationalstatus_idx (cost=0.00..111.94 rows=5,742 width=0) (actual time=1.147..1.147 rows=5,699 loops=1)

  • Index Cond: ((status)::text = ANY ('{REJECTED,ERROR,DROPPED}'::text[]))
15. 0.020 3,417.300 ↑ 1.0 1 10

Limit (cost=1.00..95.17 rows=1 width=270) (actual time=341.730..341.730 rows=1 loops=10)

16. 1,392.517 3,417.280 ↑ 1,355.0 1 10

Nested Loop Left Join (cost=1.00..127,596.71 rows=1,355 width=270) (actual time=341.728..341.728 rows=1 loops=10)

  • Filter: (((message_1.gsrn)::text = (message.gsrn)::text) OR (((msgtx_1.accesspointid)::text = (message.gsrn)::text) AND (msgenv.sender IS NOT NULL)))
  • Rows Removed by Filter: 585897
17. 46.900 91.820 ↑ 4.9 8,404 10

Merge Left Join (cost=0.58..7,126.43 rows=41,321 width=289) (actual time=0.012..9.182 rows=8,404 loops=10)

  • Merge Cond: (msgenv.envid = msgtx_1.envid)
18. 25.230 25.230 ↑ 5.3 6,166 10

Index Scan using msgenv_pk on msgenv (cost=0.29..4,311.67 rows=32,985 width=270) (actual time=0.006..2.523 rows=6,166 loops=10)

19. 19.690 19.690 ↑ 8.7 4,769 10

Index Scan using msgtx_envid_idx on msgtx msgtx_1 (cost=0.29..2,215.79 rows=41,321 width=23) (actual time=0.005..1.969 rows=4,769 loops=10)

20. 1,932.943 1,932.943 ↓ 8.6 69 84,041

Index Scan using message_exchange_idx on message message_1 (cost=0.42..2.80 rows=8 width=19) (actual time=0.002..0.023 rows=69 loops=84,041)

  • Index Cond: (exchangeid = msgtx_1.exchangeid)