explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CAQl

Settings
# exclusive inclusive rows x rows loops node
1. 0.501 28,571.613 ↑ 1.0 1,000 1

Limit (cost=275,080.12..279,104.10 rows=1,000 width=83) (actual time=28,445.831..28,571.613 rows=1,000 loops=1)

2. 1.246 28,571.112 ↑ 425.9 1,000 1

Nested Loop (cost=275,080.12..1,989,078.61 rows=425,946 width=83) (actual time=28,445.829..28,571.112 rows=1,000 loops=1)

3. 1.091 28,567.866 ↑ 425.9 1,000 1

Nested Loop (cost=275,079.98..1,919,861.82 rows=425,946 width=82) (actual time=28,445.804..28,567.866 rows=1,000 loops=1)

4. 1.081 28,479.775 ↑ 425.9 1,000 1

Nested Loop Left Join (cost=275,079.55..1,702,514.68 rows=425,946 width=55) (actual time=28,445.780..28,479.775 rows=1,000 loops=1)

  • Filter: ((op.operation_type <> 'UNKNOWN_RPI_REGISTRATION'::text) OR (s.route_id IS NOT NULL))
5. 1.652 28,449.694 ↑ 427.5 1,000 1

Hash Left Join (cost=275,079.13..1,506,797.19 rows=427,484 width=51) (actual time=28,445.753..28,449.694 rows=1,000 loops=1)

  • Hash Cond: (op.linked_item_barcode = c.barcode)
  • Filter: ((c.barcode IS NULL) OR (op.operation_type = 'RPI_IS_SENT'::text))
  • Rows Removed by Filter: 660
6. 2.491 2.491 ↑ 5,476.1 1,660 1

Seq Scan on rpi_operation op (cost=0.00..1,048,641.45 rows=9,090,251 width=68) (actual time=0.013..2.491 rows=1,660 loops=1)

  • Filter: (operation_type = ANY ('{CONTAINER_RECEIVING,RPO_REGISTRATION,UNKNOWN_RPI_REGISTRATION,RPI_REGISTRATION_WITH_NEW_BARCODE,RPO_RETURN_OPERATION,RPO_RETURN_SAB_OPERATION,RPI_IS_RETURNED_AFTER_SENT,CONTAINER_IS_RETURNED_AFTER_SENT,OPENING,CONTAINER_EXTRACT_FROM_CONTAINER,RPI_EXTRACT_FROM_CONTAINER,ASSIGNMENT_TO_VESSEL,ASSIGNMENT_RPO_TO_CONTAINER,CONTAINER_FORMING,CONTAINER_IS_SENT,CONTAINER_SIMPLE_OPENING,CONTAINER_REFORMING,CONTAINER_TERMINATION,REGULAR_CONTAINER_REFORMING,RPI_IS_SENT}'::text[]))
  • Rows Removed by Filter: 442
7. 15.845 28,445.551 ↑ 3.3 10,340 1

Hash (cost=274,647.68..274,647.68 rows=34,516 width=17) (actual time=28,445.551..28,445.551 rows=10,340 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1007kB
8. 386.944 28,429.706 ↑ 3.3 10,340 1

Hash Join (cost=54,699.53..274,647.68 rows=34,516 width=17) (actual time=202.527..28,429.706 rows=10,340 loops=1)

  • Hash Cond: (op_1.container_id = c.id)
9. 27,850.406 27,914.668 ↑ 1.0 591,645 1

Bitmap Heap Scan on container_operation op_1 (cost=17,550.10..234,881.12 rows=607,682 width=8) (actual time=74.098..27,914.668 rows=591,645 loops=1)

  • Recheck Cond: (operation_type = 'CONTAINER_IS_SENT'::text)
  • Heap Blocks: exact=42001
10. 64.262 64.262 ↑ 1.0 598,680 1

Bitmap Index Scan on idx_container_operation_type (cost=0.00..17,398.17 rows=607,682 width=0) (actual time=64.262..64.262 rows=598,680 loops=1)

  • Index Cond: (operation_type = 'CONTAINER_IS_SENT'::text)
11. 32.478 128.094 ↑ 1.0 77,321 1

Hash (cost=36,178.06..36,178.06 rows=77,710 width=25) (actual time=128.094..128.094 rows=77,321 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5329kB
12. 85.465 95.616 ↑ 1.0 77,321 1

Bitmap Heap Scan on session_container c (cost=1,462.68..36,178.06 rows=77,710 width=25) (actual time=15.997..95.616 rows=77,321 loops=1)

  • Recheck Cond: (container_type_id = 5)
  • Heap Blocks: exact=27765
13. 10.151 10.151 ↓ 1.0 78,306 1

Bitmap Index Scan on idx_session_container_container_type_id (cost=0.00..1,443.25 rows=77,710 width=0) (actual time=10.151..10.151 rows=78,306 loops=1)

  • Index Cond: (container_type_id = 5)
14. 29.000 29.000 ↓ 0.0 0 1,000

Index Scan using session_pkey on session s (cost=0.42..0.45 rows=1 width=20) (actual time=0.029..0.029 rows=0 loops=1,000)

  • Index Cond: (op.session_id = id)
15. 87.000 87.000 ↑ 1.0 1 1,000

Index Scan using session_rpi_pkey on session_rpi r (cost=0.43..0.50 rows=1 width=35) (actual time=0.086..0.087 rows=1 loops=1,000)

  • Index Cond: (id = op.rpi_id)
16. 2.000 2.000 ↑ 1.0 1 1,000

Index Scan using sc_site_pkey on sc_site st (cost=0.14..0.15 rows=1 width=9) (actual time=0.001..0.002 rows=1 loops=1,000)

  • Index Cond: (id = op.sc_site_id)