explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WgVx

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 4,818.171 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.42..484,225.63 rows=1 width=219) (actual time=4,761.759..4,818.171 rows=1 loops=1)

2. 59.702 59.702 ↑ 1.0 1 1

Seq Scan on pkg_report pr (cost=0.00..8,105.86 rows=1 width=93) (actual time=3.294..59.702 rows=1 loops=1)

  • Filter: (tracking_number = '1ZY3101E9007356164'::text)
  • Rows Removed by Filter: 286326
3. 0.013 0.013 ↑ 1.0 1 1

Index Scan using video_pkg_hnid_idx on video (cost=0.42..8.44 rows=1 width=134) (actual time=0.010..0.013 rows=1 loops=1)

  • Index Cond: (pr.pkg_hnid = pkg_hnid)
4.          

SubPlan (forNested Loop Left Join)

5. 3.315 4,758.442 ↑ 1.0 1 1

Aggregate (cost=476,111.31..476,111.32 rows=1 width=631) (actual time=4,758.442..4,758.442 rows=1 loops=1)

6. 1.230 4,755.127 ↑ 10.2 8 1

Subquery Scan on lines (cost=238,177.99..476,111.11 rows=82 width=631) (actual time=4,755.051..4,755.127 rows=8 loops=1)

7. 1,392.396 4,753.897 ↑ 10.2 8 1

Hash Right Join (cost=238,177.99..476,110.29 rows=82 width=639) (actual time=4,753.858..4,753.897 rows=8 loops=1)

  • Hash Cond: (ee.pkg_event_hnid = base.end_event_hnid)
8.          

CTE base

9. 0.038 0.084 ↑ 10.2 8 1

HashAggregate (cost=241.60..242.42 rows=82 width=267) (actual time=0.078..0.084 rows=8 loops=1)

  • Group Key: plr.sto_line_hnid, plr.sto_hnid, plr.line_type, plr.sku, plr.sku_description, plr.serial_number, plr.delivery_item, plr.program_code, plr.rma_number, plr.transaction_type, plr.pkg_line_hnid, plr.pkg_hnid, plr.device_cartier_imei, plr.device_cartier_imei_reason, plr.device_inquest_imei, plr.device_inquest_imei_reason, plr.device_affix_apkudo_label, plr.device_packaging_sufficient, plr.device_sim_card_removed, plr.device_sd_card_removed, plr.device_damages, plr.acc_lic, plr.acc_lic_reason, plr.acc_affix_apkudo_label, plr.acc_scanned_sku, plr.acc_sku_match, plr.acc_ca_damages, plr.exception, plr.overage, plr.shortage, plr.start_event_hnid, plr.end_event_hnid, plr.pkg_validated, plr.store_id, plr.sto_number, plr.tracking_number, plr.posting_date, plr.validation_end_timestamp, plr.pkg_closed, plr.pkg_damage, plr.device_cartier_imei_mismatch
10. 0.004 0.046 ↑ 10.2 8 1

Append (cost=0.43..233.19 rows=82 width=267) (actual time=0.022..0.046 rows=8 loops=1)

11. 0.030 0.030 ↑ 8.0 8 1

Index Scan using pkg_line_report_pkg_hnid_idx on pkg_line_report plr (cost=0.43..151.59 rows=64 width=267) (actual time=0.021..0.030 rows=8 loops=1)

  • Index Cond: (pr.pkg_hnid = pkg_hnid)
12. 0.012 0.012 ↓ 0.0 0 1

Index Scan using pkg_line_report_sto_hnid_idx on pkg_line_report plr_1 (cost=0.43..80.79 rows=18 width=267) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (pr.sto_hnid = sto_hnid)
  • Filter: (pkg_hnid IS NULL)
  • Rows Removed by Filter: 8
13. 963.883 963.883 ↓ 1.0 8,300,556 1

Seq Scan on event ee (cost=0.00..206,807.41 rows=8,299,641 width=24) (actual time=0.030..963.883 rows=8,300,556 loops=1)

14. 0.023 2,397.618 ↑ 10.2 8 1

Hash (cost=237,934.55..237,934.55 rows=82 width=623) (actual time=2,397.618..2,397.618 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 1,403.471 2,397.595 ↑ 10.2 8 1

Hash Right Join (cost=2.67..237,934.55 rows=82 width=623) (actual time=2,397.587..2,397.595 rows=8 loops=1)

  • Hash Cond: (se.pkg_event_hnid = base.start_event_hnid)
16. 994.007 994.007 ↓ 1.0 8,300,556 1

Seq Scan on event se (cost=0.00..206,807.41 rows=8,299,641 width=24) (actual time=0.019..994.007 rows=8,300,556 loops=1)

17. 0.016 0.117 ↑ 10.2 8 1

Hash (cost=1.64..1.64 rows=82 width=607) (actual time=0.117..0.117 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
18. 0.101 0.101 ↑ 10.2 8 1

CTE Scan on base (cost=0.00..1.64 rows=82 width=607) (actual time=0.082..0.101 rows=8 loops=1)

Planning time : 2.538 ms
Execution time : 4,818.677 ms