explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 31Tf : Optimization for: plan #WI4N

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.090 22,362.968 ↓ 85.0 85 1

Unique (cost=346,176.29..346,176.42 rows=1 width=270) (actual time=22,362.489..22,362.968 rows=85 loops=1)

2. 0.451 22,362.878 ↓ 85.0 85 1

GroupAggregate (cost=346,176.29..346,176.42 rows=1 width=270) (actual time=22,362.474..22,362.878 rows=85 loops=1)

  • Group Key: establishments.id, et.id, pe.id, p.id, g.points, v_class.classificacao
3. 0.663 22,362.427 ↓ 85.0 85 1

Sort (cost=346,176.29..346,176.30 rows=1 width=520) (actual time=22,362.411..22,362.427 rows=85 loops=1)

  • Sort Key: establishments.id, et.id, pe.id, p.id, g.points
  • Sort Method: quicksort Memory: 37kB
4. 0.950 22,361.764 ↓ 85.0 85 1

Nested Loop Left Join (cost=1.28..346,176.28 rows=1 width=520) (actual time=1,948.659..22,361.764 rows=85 loops=1)

  • Join Filter: ((reg_roles.regulation_id = reg.id) AND (reg_roles.role_id = p.role_id))
  • Rows Removed by Join Filter: 2,465
5. 0.975 22,360.219 ↓ 85.0 85 1

Nested Loop Left Join (cost=1.28..346,174.56 rows=1 width=528) (actual time=1,948.641..22,360.219 rows=85 loops=1)

  • Join Filter: (reg.id = ral.regulation_id)
  • Rows Removed by Join Filter: 1,020
6. 658.658 22,358.819 ↓ 85.0 85 1

Nested Loop Left Join (cost=1.28..346,173.29 rows=1 width=524) (actual time=1,948.620..22,358.819 rows=85 loops=1)

  • Join Filter: (ral.participant_id = p.id)
  • Rows Removed by Join Filter: 3,887,135
7. 4,012.367 20,992.706 ↓ 85.0 85 1

Nested Loop Left Join (cost=1.28..344,773.63 rows=1 width=516) (actual time=1,931.863..20,992.706 rows=85 loops=1)

  • Join Filter: (pe.establishment_id = establishments.id)
  • Rows Removed by Join Filter: 21,023,842
  • Filter: (COALESCE(pe.id, 0) = 0)
  • Rows Removed by Filter: 866
8. 4.332 14,390.766 ↓ 237.8 951 1

Nested Loop Left Join (cost=1.28..342,709.78 rows=4 width=512) (actual time=1,878.983..14,390.766 rows=951 loops=1)

9. 2,639.223 11,559.111 ↓ 475.5 951 1

Nested Loop Left Join (cost=0.42..342,691.33 rows=2 width=491) (actual time=1,874.922..11,559.111 rows=951 loops=1)

  • Join Filter: ((phds.pdv_cnpj)::text = (establishments.cnpj)::text)
  • Rows Removed by Join Filter: 12,194,149
10. 1.779 5,588.132 ↓ 194.0 388 1

Nested Loop (cost=0.42..281,983.77 rows=2 width=485) (actual time=1.435..5,588.132 rows=388 loops=1)

  • Join Filter: (establishments.establishment_type_id = et.id)
  • Rows Removed by Join Filter: 4,268
11. 0.041 0.041 ↑ 1.0 12 1

Seq Scan on establishment_types et (cost=0.00..1.12 rows=12 width=422) (actual time=0.033..0.041 rows=12 loops=1)

12. 2.109 5,586.312 ↓ 194.0 388 12

Materialize (cost=0.42..281,982.30 rows=2 width=67) (actual time=0.117..465.526 rows=388 loops=12)

13. 35.630 5,584.203 ↓ 194.0 388 1

Nested Loop (cost=0.42..281,982.29 rows=2 width=67) (actual time=1.393..5,584.203 rows=388 loops=1)

14. 3,590.573 3,590.573 ↓ 12.5 24,475 1

Seq Scan on view_classification v_class (cost=0.00..268,226.91 rows=1,963 width=24) (actual time=0.072..3,590.573 rows=24,475 loops=1)

  • Filter: ((classificacao = 'Objetivo'::text) AND (((year)::text || lpad((month)::text, 2, '0'::text)) = to_char(now(), 'YYYYmm'::text)))
  • Rows Removed by Filter: 4,780,535
15. 1,958.000 1,958.000 ↓ 0.0 0 24,475

Index Scan using idx_establichment_cnpj_uniq on establishments (cost=0.42..7.01 rows=1 width=62) (actual time=0.080..0.080 rows=0 loops=24,475)

  • Index Cond: ((cnpj)::text = (v_class.cnpj)::text)
  • Filter: ((additional_info @> '{"indicator_distributors": ["P1277"]}'::jsonb) AND (status <> 3) AND (status <> 2) AND (status <> 0))
  • Rows Removed by Filter: 1
16. 1,467.668 3,331.756 ↓ 4.7 31,430 388

Materialize (cost=0.00..60,525.21 rows=6,631 width=21) (actual time=1.703..8.587 rows=31,430 loops=388)

17. 1,864.088 1,864.088 ↓ 4.7 31,430 1

Seq Scan on pdv_history_daily_sales phds (cost=0.00..60,492.05 rows=6,631 width=21) (actual time=660.554..1,864.088 rows=31,430 loops=1)

  • Filter: (((year)::text || lpad((month)::text, 2, '0'::text)) = to_char(now(), 'YYYYmm'::text))
  • Rows Removed by Filter: 1,294,866
18. 5.706 2,827.323 ↑ 1.0 1 951

Nested Loop Left Join (cost=0.86..9.21 rows=1 width=25) (actual time=2.150..2.973 rows=1 loops=951)

19. 775.065 775.065 ↑ 1.0 1 951

Index Scan using estab_id on participants p (cost=0.42..8.05 rows=1 width=20) (actual time=0.814..0.815 rows=1 loops=951)

  • Index Cond: (establishment_id = establishments.id)
20. 2,046.552 2,046.552 ↑ 1.0 1 951

Index Scan using idx_part on goals g (cost=0.43..1.15 rows=1 width=9) (actual time=1.331..2.152 rows=1 loops=951)

  • Index Cond: (participant_id = p.id)
  • Filter: (((year)::text || lpad((period_num)::text, 2, '0'::text)) = to_char(now(), 'YYYYmm'::text))
  • Rows Removed by Filter: 22
21. 2,547.618 2,589.573 ↑ 1.0 22,108 951

Materialize (cost=0.00..571.50 rows=22,109 width=8) (actual time=0.007..2.723 rows=22,108 loops=951)

22. 41.955 41.955 ↑ 1.0 22,108 1

Seq Scan on participant_establishments pe (cost=0.00..460.95 rows=22,109 width=8) (actual time=6.126..41.955 rows=22,108 loops=1)

  • Filter: (role_id = 1)
  • Rows Removed by Filter: 2,287
23. 707.455 707.455 ↓ 1.0 45,731 85

Seq Scan on regulation_accepted_logs ral (cost=0.00..828.18 rows=45,718 width=12) (actual time=0.012..8.323 rows=45,731 loops=85)

24. 0.425 0.425 ↑ 1.0 12 85

Seq Scan on regulations reg (cost=0.00..1.12 rows=12 width=8) (actual time=0.003..0.005 rows=12 loops=85)

25. 0.595 0.595 ↑ 1.0 29 85

Seq Scan on regulation_roles reg_roles (cost=0.00..1.29 rows=29 width=8) (actual time=0.004..0.007 rows=29 loops=85)

Planning time : 71.855 ms
Execution time : 22,364.507 ms