explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cvNn

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 187.741 2,090.301 ↓ 1.1 28,247 1

Sort (cost=13,439,924.62..13,439,986.44 rows=24,728 width=1,533) (actual time=2,063.308..2,090.301 rows=28,247 loops=1)

  • Sort Key: c_order.created DESC
  • Sort Method: external merge Disk: 14408kB
  • Buffers: shared hit=665963, temp read=3921 written=3921
2.          

Initplan (for Sort)

3. 0.000 0.000 ↑ 1.0 1 1

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

4. 829.172 1,902.560 ↓ 1.1 28,247 1

Seq Scan on c_order (cost=7.16..13,418,813.32 rows=24,728 width=1,533) (actual time=3.791..1,902.560 rows=28,247 loops=1)

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

SubPlan (for Seq Scan)

6. 197.729 197.729 ↓ 0.0 0 28,247

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,247)

  • 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=114260
7. 169.482 169.482 ↑ 1.0 1 28,247

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,247)

  • Index Cond: (c_order_id = c_order.c_order_id)
  • Buffers: shared hit=84891
8. 28.247 338.964 ↑ 1.0 1 28,247

Aggregate (cost=52.62..52.63 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=28,247)

  • Buffers: shared hit=153891
9. 83.982 310.717 ↓ 0.0 0 28,247

Hash Semi Join (cost=49.87..52.62 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=28,247)

  • Hash Cond: (c_orderline.m_product_id = ($4))
  • Buffers: shared hit=153891
10. 225.976 225.976 ↑ 1.4 5 28,247

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,247)

  • Index Cond: (c_order_id = c_order.c_order_id)
  • Buffers: shared hit=153596
11. 0.012 0.759 ↑ 1.0 1 1

Hash (cost=49.43..49.43 rows=1 width=32) (actual time=0.759..0.759 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=295
12. 0.003 0.747 ↑ 1.0 1 1

Result (cost=49.41..49.42 rows=1 width=32) (actual time=0.746..0.747 rows=1 loops=1)

  • Buffers: shared hit=295
13.          

Initplan (for Result)

14. 0.002 0.744 ↑ 1.0 1 1

Limit (cost=0.28..49.41 rows=1 width=6) (actual time=0.744..0.744 rows=1 loops=1)

  • Buffers: shared hit=295
15. 0.742 0.742 ↑ 12.0 1 1

Index Scan Backward using m_product_pkey on m_product (cost=0.28..589.83 rows=12 width=6) (actual time=0.742..0.742 rows=1 loops=1)

  • Index Cond: (m_product_id IS NOT NULL)
  • Filter: ((lower((description)::text) ~~ '%bolsa%'::text) AND (lower((description)::text) !~~ '%descuento%'::text))
  • Rows Removed by Filter: 627
  • Buffers: shared hit=295
16. 28.247 197.729 ↑ 1.0 1 28,247

Aggregate (cost=480.38..480.39 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=28,247)

  • Buffers: shared hit=153926
17. 54.026 169.482 ↓ 0.0 0 28,247

Hash Semi Join (cost=477.62..480.37 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=28,247)

  • Hash Cond: (c_orderline_1.m_product_id = (max(m_product_1.m_product_id)))
  • Buffers: shared hit=153926
18. 112.988 112.988 ↑ 1.4 5 28,247

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,247)

  • Index Cond: (c_order_id = c_order.c_order_id)
  • Buffers: shared hit=153596
19. 0.008 2.468 ↑ 1.0 1 1

Hash (cost=477.18..477.18 rows=1 width=32) (actual time=2.468..2.468 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=330
20. 0.011 2.460 ↑ 1.0 1 1

Aggregate (cost=477.16..477.17 rows=1 width=32) (actual time=2.460..2.460 rows=1 loops=1)

  • Buffers: shared hit=330
21. 2.449 2.449 ↑ 1.0 1 1

Seq Scan on m_product m_product_1 (cost=0.00..477.16 rows=1 width=6) (actual time=0.416..2.449 rows=1 loops=1)

  • Filter: ((lower((description)::text) ~~ '%bolsa%'::text) AND (lower((description)::text) ~~ '%descuento%'::text))
  • Rows Removed by Filter: 7091
  • Buffers: shared hit=330
22. 28.247 169.482 ↑ 1.0 1 28,247

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

  • Buffers: shared hit=153596
23. 141.235 141.235 ↓ 0.0 0 28,247

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,247)

  • 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=153596
24. 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.338 ms
Execution time : 2,095.820 ms