explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 231.737 3,354.780 ↓ 1.1 24,434 1

Sort (cost=13,495,779.83..13,495,834.60 rows=21,906 width=1,530) (actual time=3,304.187..3,354.780 rows=24,434 loops=1)

  • Sort Key: c_order.created DESC
  • Sort Method: external merge Disk: 12432kB
  • Buffers: shared hit=538903 read=3886, temp read=1560 written=1560
2.          

Initplan (for Sort)

3. 0.003 0.003 ↑ 1.0 1 1

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

4. 1,143.879 3,123.040 ↓ 1.1 24,434 1

Seq Scan on c_order (cost=15.86..13,479,598.51 rows=21,906 width=1,530) (actual time=4.787..3,123.040 rows=24,434 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: 62217
  • Buffers: shared hit=538900 read=3886
5.          

SubPlan (for Seq Scan)

6. 244.340 244.340 ↓ 0.0 0 24,434

Index Scan using c_bpartner_pkey on c_bpartner (cost=0.29..8.31 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=24,434)

  • 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=73440
7. 244.340 244.340 ↑ 1.0 1 24,434

Index Scan using c_order_pkey on c_order co (cost=0.29..8.32 rows=1 width=32) (actual time=0.009..0.010 rows=1 loops=24,434)

  • Index Cond: (c_order_id = c_order.c_order_id)
  • Buffers: shared hit=73404 read=15
8. 73.302 659.718 ↑ 1.0 1 24,434

Aggregate (cost=142.86..142.87 rows=1 width=8) (actual time=0.026..0.027 rows=1 loops=24,434)

  • Buffers: shared hit=130704
9. 219.130 586.416 ↓ 0.0 0 24,434

Hash Semi Join (cost=132.94..142.86 rows=1 width=0) (actual time=0.024..0.024 rows=0 loops=24,434)

  • Hash Cond: (c_orderline.m_product_id = ($4))
  • Buffers: shared hit=130704
10. 366.510 366.510 ↑ 1.4 5 24,434

Index Scan using c_orderline_order on c_orderline (cost=0.42..10.32 rows=7 width=6) (actual time=0.007..0.015 rows=5 loops=24,434)

  • Index Cond: (c_order_id = c_order.c_order_id)
  • Buffers: shared hit=130474
11. 0.019 0.776 ↑ 1.0 1 1

Hash (cost=132.51..132.51 rows=1 width=32) (actual time=0.775..0.776 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=230
12. 0.006 0.757 ↑ 1.0 1 1

Result (cost=132.49..132.50 rows=1 width=32) (actual time=0.755..0.757 rows=1 loops=1)

  • Buffers: shared hit=230
13.          

Initplan (for Result)

14. 0.007 0.751 ↑ 1.0 1 1

Limit (cost=0.28..132.49 rows=1 width=6) (actual time=0.748..0.751 rows=1 loops=1)

  • Buffers: shared hit=230
15. 0.744 0.744 ↑ 11.0 1 1

Index Scan Backward using m_product_pkey on m_product (cost=0.28..1,454.52 rows=11 width=6) (actual time=0.744..0.744 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: 476
  • Buffers: shared hit=230
16. 73.302 537.548 ↑ 1.0 1 24,434

Aggregate (cost=444.20..444.21 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=24,434)

  • Buffers: shared hit=130769
17. 192.245 464.246 ↓ 0.0 0 24,434

Hash Semi Join (cost=434.28..444.20 rows=1 width=0) (actual time=0.019..0.019 rows=0 loops=24,434)

  • Hash Cond: (c_orderline_1.m_product_id = (max(m_product_1.m_product_id)))
  • Buffers: shared hit=130769
18. 268.774 268.774 ↑ 1.4 5 24,434

Index Scan using c_orderline_order on c_orderline c_orderline_1 (cost=0.42..10.32 rows=7 width=6) (actual time=0.005..0.011 rows=5 loops=24,434)

  • Index Cond: (c_order_id = c_order.c_order_id)
  • Buffers: shared hit=130474
19. 0.014 3.227 ↑ 1.0 1 1

Hash (cost=433.84..433.84 rows=1 width=32) (actual time=3.226..3.227 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=295
20. 0.026 3.213 ↑ 1.0 1 1

Aggregate (cost=433.82..433.83 rows=1 width=32) (actual time=3.210..3.213 rows=1 loops=1)

  • Buffers: shared hit=295
21. 3.187 3.187 ↑ 1.0 1 1

Seq Scan on m_product m_product_1 (cost=0.00..433.82 rows=1 width=6) (actual time=0.399..3.187 rows=1 loops=1)

  • Filter: ((lower((description)::text) ~~ '%bolsa%'::text) AND (lower((description)::text) ~~ '%descuento%'::text))
  • Rows Removed by Filter: 6940
  • Buffers: shared hit=295
22. 73.302 293.208 ↑ 1.0 1 24,434

Aggregate (cost=10.34..10.35 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=24,434)

  • Buffers: shared hit=130474
23. 219.906 219.906 ↓ 0.0 0 24,434

Index Scan using c_orderline_order on c_orderline c_orderline_2 (cost=0.42..10.34 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=24,434)

  • 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=130474
24. 0.007 0.007 ↓ 0.0 0 1

Index Scan using ad_private_access_pkey on ad_private_access (cost=0.15..15.85 rows=1 width=16) (actual time=0.006..0.007 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 : 6.788 ms
Execution time : 3,376.461 ms