explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rD0C

Settings
# exclusive inclusive rows x rows loops node
1. 44.665 13,266.896 ↑ 25.0 2 1

Limit (cost=239,165.63..239,165.76 rows=50 width=217) (actual time=13,266.896..13,266.896 rows=2 loops=1)

  • Functions: 41
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 4.874 ms, Inlining 0.000 ms, Optimization 2.595 ms, Emission 41.774 ms, Total 49.242 ms
2. 0.012 13,222.231 ↑ 2,328.0 2 1

Sort (cost=239,165.63..239,177.27 rows=4,656 width=217) (actual time=13,222.231..13,222.231 rows=2 loops=1)

  • Sort Key: ((dat.ship_date)::date) DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.008 13,222.219 ↑ 2,328.0 2 1

Subquery Scan on dat (cost=238,859.65..239,010.97 rows=4,656 width=217) (actual time=13,222.214..13,222.219 rows=2 loops=1)

4. 0.004 13,222.211 ↑ 2,328.0 2 1

Unique (cost=238,859.65..238,941.13 rows=4,656 width=213) (actual time=13,222.207..13,222.211 rows=2 loops=1)

5. 0.011 13,222.207 ↑ 1,164.0 4 1

Sort (cost=238,859.65..238,871.29 rows=4,656 width=213) (actual time=13,222.206..13,222.207 rows=4 loops=1)

  • Sort Key: i.bol_num, (to_char((i.ship_date)::timestamp with time zone, 'MM/DD/YYYY'::text)), i.raw_file, (ltrim(i.invoice_num, '0'::text)), ((p.ar_amt)::money), sf.supplier_name
  • Sort Method: quicksort Memory: 25kB
6. 0.032 13,222.196 ↑ 1,164.0 4 1

Hash Left Join (cost=83,745.47..238,575.98 rows=4,656 width=213) (actual time=13,220.638..13,222.196 rows=4 loops=1)

  • Hash Cond: ((pams_oe_order_items_prior_2017.supplier_code)::text = (sf.supplier_code)::text)
7. 163.603 13,201.181 ↑ 1,164.0 4 1

Merge Join (cost=83,726.01..238,368.65 rows=4,656 width=128) (actual time=13,199.629..13,201.181 rows=4 loops=1)

  • Merge Cond: (pams_oe_order_items_prior_2017.oe_order_no = p.order_num)
8. 1,249.307 12,702.572 ↓ 1.1 3,479,891 1

Merge Append (cost=33,617.55..180,493.45 rows=3,078,761 width=51) (actual time=11,402.908..12,702.572 rows=3,479,891 loops=1)

  • Sort Key: pams_oe_order_items_prior_2017.oe_order_no
  • -> Index Scan using pams_oe_order_items_prior_2017_idx on pams_oe_order_items_prior_2017 (cost=0.43..116088.71 rows=2838374 width=11) (actual time=0.021..997.14
9. 160.149 11,453.265 ↓ 2.7 641,442 1

Sort (cost=79,047.69..79,648.65 rows=240,387 width=520) (actual time=11,402.885..11,453.265 rows=641,442 loops=1)

  • Sort Key: pams_oe_order_items_post_2017.oe_order_no
  • Sort Method: external merge Disk: 12520kB
10. 11,293.116 11,293.116 ↓ 2.7 644,772 1

Foreign Scan on pams_oe_order_items_post_2017 (cost=0.00..33,617.10 rows=240,387 width=520) (actual time=23.901..11,293.116 rows=644,772 loops=1)

  • Foreign File: /mnt/carysmb/pamslink/csv/pams_oe_order_items_post_2017.csv
  • Foreign File Size: 130770291 b
11. 0.014 335.006 ↑ 11.7 3 1

Sort (cost=50,108.46..50,108.54 rows=35 width=85) (actual time=335.005..335.006 rows=3 loops=1)

  • Sort Key: p.order_num
  • Sort Method: quicksort Memory: 25kB
12. 0.021 334.992 ↑ 17.5 2 1

Nested Loop (cost=40,502.18..50,107.56 rows=35 width=85) (actual time=325.811..334.992 rows=2 loops=1)

13. 334.943 334.943 ↑ 31.0 2 1

Bitmap Heap Scan on co_customer_invoices i (cost=40,501.61..49,921.71 rows=62 width=74) (actual time=325.778..334.943 rows=2 loops=1)

  • Recheck Cond: ((ship_date >= (now() - '31 days'::interval)) AND (ship_date <= now()))
  • Filter: ((invoice_type = 'INV'::text) AND ((store_num = '57051'::text) OR (btrim(customer_num) = 'VALPRIMMA'::text) OR (btrim(customer_num) = 'VALPRIM
  • Rows Removed by Filter: 37846
  • Heap Blocks: exact=3663
  • -> Bitmap Index Scan on co_customer_invoices_store_cust_idx (cost=0.00..40501.60 rows=10174 width=0) (actual time=320.439..320.439 rows=37848 loops=
  • Index Cond: ((ship_date >= (now() - '31 days'::interval)) AND (ship_date <= now()))
14. 0.028 0.028 ↑ 1.0 1 2

Index Scan using co_pams_ar_open_item2_idx on co_pams_ar_open_item p (cost=0.57..2.99 rows=1 width=18) (actual time=0.013..0.014 rows=1 loops=2)

  • Index Cond: ((ar_trx_no = ((ltrim(i.invoice_num, '0'::text))::integer)::numeric) AND ((orig_module)::text = 'OE'::text))
15. 0.036 20.983 ↓ 1.6 250 1

Hash (cost=17.50..17.50 rows=157 width=106) (actual time=20.983..20.983 rows=250 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
16. 20.947 20.947 ↓ 1.6 250 1

Foreign Scan on pams_fs_supplier_file sf (cost=0.00..17.50 rows=157 width=106) (actual time=20.738..20.947 rows=250 loops=1)

  • Foreign File: /mnt/carysmb/pamslink/csv/pams_fs_supplier_file.csv
  • Foreign File Size: 21358 b