explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E0pS : Optimization for: plan #sJ4P

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.018 861.271 ↑ 1.0 1 1

Aggregate (cost=3,839,365.53..3,839,365.54 rows=1 width=32) (actual time=861.271..861.271 rows=1 loops=1)

2. 3.371 861.253 ↑ 4.2 8 1

Subquery Scan on ff (cost=5,078.65..3,839,365.45 rows=34 width=32) (actual time=157.640..861.253 rows=8 loops=1)

  • Filter: ((SubPlan 1) = 'Marca:OPEL@Modelo:ASTRA G Fastback (T98)'::text)
  • Rows Removed by Filter: 7,497
3. 11.652 62.352 ↓ 1.1 7,505 1

HashAggregate (cost=5,078.65..5,163.03 rows=6,751 width=65) (actual time=57.031..62.352 rows=7,505 loops=1)

  • Group Key: ol.c_orderline_id
4. 7.388 50.700 ↓ 2.2 15,010 1

Hash Join (cost=2,110.54..5,044.89 rows=6,751 width=37) (actual time=31.029..50.700 rows=15,010 loops=1)

  • Hash Cond: ((ol.c_order_id)::text = (o.c_order_id)::text)
5. 13.054 13.054 ↑ 1.0 22,752 1

Seq Scan on c_orderline ol (cost=0.00..2,781.52 rows=22,752 width=70) (actual time=0.032..13.054 rows=22,752 loops=1)

6. 2.597 30.258 ↓ 2.2 7,686 1

Hash (cost=2,065.93..2,065.93 rows=3,569 width=33) (actual time=30.258..30.258 rows=7,686 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 552kB
7. 12.247 27.661 ↓ 2.2 7,686 1

Nested Loop (cost=0.00..2,065.93 rows=3,569 width=33) (actual time=0.067..27.661 rows=7,686 loops=1)

  • Join Filter: (date_part('month'::text, age(now(), (o.dateordered)::timestamp with time zone)) >= (COALESCE(conf.meses_a_actualziar, '1'::numeric))::double precision)
  • Rows Removed by Join Filter: 574
8. 15.414 15.414 ↑ 1.3 4,130 1

Seq Scan on c_order o (cost=0.00..1,770.43 rows=5,354 width=41) (actual time=0.020..15.414 rows=4,130 loops=1)

  • Filter: (((docstatus)::text = 'CO'::text) AND (issotrx = 'Y'::bpchar))
  • Rows Removed by Filter: 7,899
9. 0.000 0.000 ↑ 1.0 2 4,130

Materialize (cost=0.00..1.03 rows=2 width=32) (actual time=0.000..0.000 rows=2 loops=4,130)

10. 0.018 0.018 ↑ 1.0 2 1

Seq Scan on opxesde_configuracion conf (cost=0.00..1.02 rows=2 width=32) (actual time=0.016..0.018 rows=2 loops=1)

11.          

SubPlan (for Subquery Scan)

12. 7.505 795.530 ↑ 1.0 1 7,505

Aggregate (cost=567.92..567.93 rows=1 width=32) (actual time=0.106..0.106 rows=1 loops=7,505)

13. 15.010 788.025 ↓ 2.0 2 7,505

Sort (cost=567.90..567.91 rows=1 width=59) (actual time=0.105..0.105 rows=2 loops=7,505)

  • Sort Key: attr.name
  • Sort Method: quicksort Memory: 25kB
14. 9.710 773.015 ↓ 2.0 2 7,505

Nested Loop Left Join (cost=2.09..567.89 rows=1 width=59) (actual time=0.033..0.103 rows=2 loops=7,505)

15. 8.525 697.965 ↓ 2.0 2 7,505

Nested Loop (cost=1.67..560.08 rows=1 width=68) (actual time=0.029..0.093 rows=2 loops=7,505)

16. 7.505 120.080 ↑ 1.0 1 7,505

Nested Loop (cost=0.97..17.01 rows=1 width=66) (actual time=0.016..0.016 rows=1 loops=7,505)

17. 45.030 45.030 ↑ 1.0 1 7,505

Index Scan using c_orderline_key on c_orderline ol_1 (cost=0.41..8.43 rows=1 width=33) (actual time=0.006..0.006 rows=1 loops=7,505)

  • Index Cond: ((c_orderline_id)::text = (ff.c_orderline_id)::text)
18. 67.545 67.545 ↑ 1.0 1 7,505

Index Only Scan using m_attributesetinstance_key on m_attributesetinstance aol (cost=0.56..8.58 rows=1 width=33) (actual time=0.009..0.009 rows=1 loops=7,505)

  • Index Cond: (m_attributesetinstance_id = (ol_1.m_attributesetinstance_id)::text)
  • Heap Fetches: 5,176
19. 20.704 569.360 ↑ 39.0 3 5,176

Nested Loop (cost=0.70..541.90 rows=117 width=101) (actual time=0.019..0.110 rows=3 loops=5,176)

20. 144.928 144.928 ↑ 1.0 13 5,176

Seq Scan on m_attribute attr (cost=0.00..10.40 rows=13 width=60) (actual time=0.002..0.028 rows=13 loops=5,176)

  • Filter: (em_awobg_filtrar_buscar_venta = 'Y'::bpchar)
  • Rows Removed by Filter: 179
21. 403.728 403.728 ↓ 0.0 0 67,288

Index Scan using m_attributeinstance_attribu_un on m_attributeinstance attrinstance (cost=0.70..40.79 rows=9 width=107) (actual time=0.006..0.006 rows=0 loops=67,288)

  • Index Cond: (((m_attributesetinstance_id)::text = (aol.m_attributesetinstance_id)::text) AND ((m_attribute_id)::text = (attr.m_attribute_id)::text))
22. 65.340 65.340 ↑ 1.0 1 13,068

Index Scan using m_attributevalue_key on m_attributevalue attrvalue (cost=0.42..7.80 rows=1 width=43) (actual time=0.005..0.005 rows=1 loops=13,068)

  • Index Cond: ((attrinstance.m_attributevalue_id)::text = (m_attributevalue_id)::text)
Planning time : 7.381 ms
Execution time : 861.582 ms