explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DPor : Optimization for: Optimization for: Optimization for: plan #YItN; plan #OSY9; plan #Se3R

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 177.747 1,965.959 ↓ 1.2 28,477 1

Sort (cost=804,191.13..804,252.52 rows=24,554 width=1,533) (actual time=1,939.990..1,965.959 rows=28,477 loops=1)

  • Sort Key: c_order.created DESC
  • Sort Method: external merge Disk: 14528kB
  • Buffers: shared hit=670364, temp read=3936 written=3936
2.          

Initplan (for Sort)

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

4. 763.037 1,788.211 ↓ 1.2 28,477 1

Seq Scan on c_order (cost=7.16..783,229.08 rows=24,554 width=1,533) (actual time=0.499..1,788.211 rows=28,477 loops=1)

  • Filter: ((ad_client_id = ANY ('{0,2000006}'::numeric[])) AND ((c_order_id IS NULL) OR (NOT (hashed SubPlan 7))) AND (issotrx = 'Y'::bpchar) AND ((processed = 'N'::bpchar) OR (updated > (now() - '1'::numeric))))
  • Rows Removed by Filter: 68017
  • Buffers: shared hit=670361
5.          

SubPlan (for Seq Scan)

6. 199.339 199.339 ↓ 0.0 0 28,477

Index Scan using c_bpartner_pkey on c_bpartner (cost=0.41..2.64 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=28,477)

  • Index Cond: (c_bpartner_id = c_order.c_bpartner_id)
  • Filter: (created > (('now'::cstring)::date - '2 mons'::interval))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=115187
7. 170.862 170.862 ↑ 1.0 1 28,477

Index Scan using c_order_pkey on c_order co (cost=0.29..2.52 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=28,477)

  • Index Cond: (c_order_id = c_order.c_order_id)
  • Buffers: shared hit=85581
8. 28.477 313.247 ↑ 1.0 1 28,477

Aggregate (cost=16.68..16.69 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=28,477)

  • Buffers: shared hit=154714
9. 56.917 284.770 ↓ 0.0 0 28,477

Hash Semi Join (cost=13.92..16.68 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=28,477)

  • Hash Cond: (c_orderline.m_product_id = (max(m_product.m_product_id)))
  • Buffers: shared hit=154714
10. 227.816 227.816 ↑ 1.4 5 28,477

Index Scan using c_orderline_order on c_orderline (cost=0.42..3.16 rows=7 width=6) (actual time=0.005..0.008 rows=5 loops=28,477)

  • Index Cond: (c_order_id = c_order.c_order_id)
  • Buffers: shared hit=154712
11. 0.010 0.037 ↑ 1.0 1 1

Hash (cost=13.49..13.49 rows=1 width=32) (actual time=0.037..0.037 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
12. 0.007 0.027 ↑ 1.0 1 1

Aggregate (cost=13.47..13.48 rows=1 width=32) (actual time=0.027..0.027 rows=1 loops=1)

  • Buffers: shared hit=2
13. 0.020 0.020 ↑ 11.0 1 1

Index Scan using m_product_lower_description_not_like on m_product (cost=0.12..13.44 rows=11 width=6) (actual time=0.019..0.020 rows=1 loops=1)

  • Buffers: shared hit=2
14. 0.000 170.862 ↑ 1.0 1 28,477

Aggregate (cost=5.56..5.57 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=28,477)

  • Buffers: shared hit=154714
15. 56.930 170.862 ↓ 0.0 0 28,477

Hash Semi Join (cost=2.80..5.55 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=28,477)

  • Hash Cond: (c_orderline_1.m_product_id = (max(m_product_1.m_product_id)))
  • Buffers: shared hit=154714
16. 113.908 113.908 ↑ 1.4 5 28,477

Index Scan using c_orderline_order on c_orderline c_orderline_1 (cost=0.42..3.16 rows=7 width=6) (actual time=0.003..0.004 rows=5 loops=28,477)

  • Index Cond: (c_order_id = c_order.c_order_id)
  • Buffers: shared hit=154712
17. 0.008 0.024 ↑ 1.0 1 1

Hash (cost=2.36..2.36 rows=1 width=32) (actual time=0.024..0.024 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
18. 0.006 0.016 ↑ 1.0 1 1

Aggregate (cost=2.34..2.35 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=1)

  • Buffers: shared hit=2
19. 0.010 0.010 ↑ 1.0 1 1

Index Scan using m_product_lower_description_like on m_product m_product_1 (cost=0.12..2.34 rows=1 width=6) (actual time=0.010..0.010 rows=1 loops=1)

  • Buffers: shared hit=2
20. 28.477 170.862 ↑ 1.0 1 28,477

Aggregate (cost=3.18..3.19 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=28,477)

  • Buffers: shared hit=154712
21. 142.385 142.385 ↓ 0.0 0 28,477

Index Scan using c_orderline_order on c_orderline c_orderline_2 (cost=0.42..3.18 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=28,477)

  • Index Cond: (c_order_id = c_order.c_order_id)
  • Filter: (m_product_id = ANY ('{2020603,2020712}'::numeric[]))
  • Rows Removed by Filter: 5
  • Buffers: shared hit=154712
22. 0.002 0.002 ↓ 0.0 0 1

Index Scan using ad_private_access_pkey on ad_private_access (cost=0.15..7.15 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (ad_table_id = '259'::numeric)
  • Filter: ((ad_user_id <> '100'::numeric) AND (isactive = 'Y'::bpchar))
  • Buffers: shared hit=1
Planning time : 4.757 ms
Execution time : 1,970.650 ms