explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OG5S

Settings
# exclusive inclusive rows x rows loops node
1. 182.921 21,415.246 ↑ 2.0 9,268 1

Nested Loop (cost=489,199.19..739,140.75 rows=18,386 width=73) (actual time=20,788.733..21,415.246 rows=9,268 loops=1)

2.          

CTE order_invoice_unique_skus

3. 275.144 20,867.294 ↑ 2.6 22,898 1

HashAggregate (cost=488,614.70..489,198.76 rows=58,406 width=8) (actual time=20,788.564..20,867.294 rows=22,898 loops=1)

  • Group Key: vi.unique_sku_id
4. 340.943 20,592.150 ↑ 1.0 56,960 1

Append (cost=48,676.81..488,468.69 rows=58,406 width=8) (actual time=7,679.392..20,592.150 rows=56,960 loops=1)

5. 287.722 8,749.691 ↓ 1.0 27,998 1

Hash Join (cost=48,676.81..211,957.51 rows=26,951 width=8) (actual time=7,679.386..8,749.691 rows=27,998 loops=1)

  • Hash Cond: (vi.purchase_order_id = po.id)
6. 842.937 842.937 ↓ 1.0 27,998 1

Seq Scan on vendor_items vi (cost=0.00..157,844.12 rows=26,951 width=16) (actual time=27.911..842.937 rows=27,998 loops=1)

  • Filter: ((received_at >= '2017-11-01 00:00:00+00'::timestamp with time zone) AND (received_at <= '2017-11-09 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 4083557
7. 3,853.598 7,619.032 ↑ 1.0 1,228,989 1

Hash (cost=28,511.58..28,511.58 rows=1,229,058 width=8) (actual time=7,619.032..7,619.032 rows=1,228,989 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 2541kB
8. 3,765.434 3,765.434 ↑ 1.0 1,228,989 1

Seq Scan on purchase_orders po (cost=0.00..28,511.58 rows=1,229,058 width=8) (actual time=0.051..3,765.434 rows=1,228,989 loops=1)

9. 370.625 11,501.516 ↑ 1.1 28,962 1

Hash Join (cost=75,386.36..275,927.12 rows=31,455 width=8) (actual time=10,683.271..11,501.516 rows=28,962 loops=1)

  • Hash Cond: (dili.distributor_invoice_id = di.id)
10. 1,136.722 1,136.722 ↑ 1.1 28,962 1

Seq Scan on distributor_invoice_line_items dili (cost=0.00..193,710.94 rows=31,455 width=16) (actual time=479.980..1,136.722 rows=28,962 loops=1)

  • Filter: ((received_at >= '2017-11-01 00:00:00+00'::timestamp with time zone) AND (received_at <= '2017-11-09 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 6854034
11. 5,041.056 9,994.169 ↑ 1.0 1,548,638 1

Hash (cost=49,978.38..49,978.38 rows=1,548,638 width=8) (actual time=9,994.169..9,994.169 rows=1,548,638 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 2989kB
12. 4,953.113 4,953.113 ↑ 1.0 1,548,638 1

Seq Scan on distributor_invoices di (cost=0.00..49,978.38 rows=1,548,638 width=8) (actual time=0.120..4,953.113 rows=1,548,638 loops=1)

13. 21,003.345 21,003.345 ↑ 2.6 22,898 1

CTE Scan on order_invoice_unique_skus oius (cost=0.00..1,168.12 rows=58,406 width=8) (actual time=20,788.574..21,003.345 rows=22,898 loops=1)

14. 228.980 228.980 ↓ 0.0 0 22,898

Index Scan using unique_skus_pkey on unique_skus us (cost=0.43..4.25 rows=1 width=73) (actual time=0.008..0.010 rows=0 loops=22,898)

  • Index Cond: (id = oius.unique_sku_id)
  • Filter: ((distributor_id = 50) AND (inventory_status_id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13}'::smallint[])))
  • Rows Removed by Filter: 1