explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5z2H

Settings
# exclusive inclusive rows x rows loops node
1. 62,276.237 62,276.237 ↓ 1.8 126 1

CTE Scan on analise (cost=4,158,718.06..4,158,736.76 rows=70 width=268) (actual time=62,275.439..62,276.237 rows=126 loops=1)

  • Filter: ((precisao > '75'::numeric) AND ((preco_previsto - preco_final) > '0'::numeric))
  • Rows Removed by Filter: 526
2.          

CTE preco

3. 41.788 41.788 ↑ 1.0 59,776 1

Index Scan using uidx_cotacao_data_codigo on cotacao (cost=0.41..2,708.87 rows=59,776 width=16) (actual time=0.027..41.788 rows=59,776 loops=1)

4.          

CTE acoes

5. 1.514 52.791 ↓ 1.0 652 1

Sort (cost=1,160.92..1,162.50 rows=632 width=6) (actual time=52.110..52.791 rows=652 loops=1)

  • Sort Key: cotacao_1.acao
  • Sort Method: quicksort Memory: 42kB
6. 24.718 51.277 ↓ 1.0 652 1

HashAggregate (cost=1,125.20..1,131.52 rows=632 width=6) (actual time=51.097..51.277 rows=652 loops=1)

  • Group Key: cotacao_1.acao
7. 26.559 26.559 ↑ 1.0 59,776 1

Seq Scan on cotacao cotacao_1 (cost=0.00..975.76 rows=59,776 width=6) (actual time=0.770..26.559 rows=59,776 loops=1)

8.          

CTE periodo

9. 0.010 0.010 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=20) (actual time=0.005..0.010 rows=1 loops=1)

10.          

CTE analise

11. 3.267 62,275.256 ↓ 1.0 652 1

Sort (cost=4,154,845.11..4,154,846.68 rows=629 width=204) (actual time=62,275.206..62,275.256 rows=652 loops=1)

  • Sort Key: acoes.acao
  • Sort Method: quicksort Memory: 97kB
12. 42.936 62,271.989 ↓ 1.0 652 1

Nested Loop (cost=0.00..4,154,815.87 rows=629 width=204) (actual time=235.823..62,271.989 rows=652 loops=1)

13. 0.035 0.035 ↑ 1.0 1 1

CTE Scan on periodo (cost=0.00..0.02 rows=1 width=20) (actual time=0.029..0.035 rows=1 loops=1)

14. 55.602 55.602 ↓ 1.0 652 1

CTE Scan on acoes (cost=0.00..14.22 rows=629 width=32) (actual time=52.115..55.602 rows=652 loops=1)

  • Filter: (acao <> ''::text)
15.          

SubPlan (forNested Loop)

16. 1.304 6,784.712 ↑ 1.0 1 652

Limit (cost=0.00..14.94 rows=1 width=32) (actual time=10.406..10.406 rows=1 loops=652)

17. 6,783.408 6,783.408 ↑ 100.0 1 652

CTE Scan on preco (cost=0.00..1,494.40 rows=100 width=32) (actual time=10.404..10.404 rows=1 loops=652)

  • Filter: ((data <= periodo.datainicial) AND (acao = acoes.acao))
  • Rows Removed by Filter: 32358
18. 0.652 3,012.240 ↑ 1.0 1 652

Limit (cost=0.00..14.94 rows=1 width=32) (actual time=4.620..4.620 rows=1 loops=652)

19. 3,011.588 3,011.588 ↑ 100.0 1 652

CTE Scan on preco preco_1 (cost=0.00..1,494.40 rows=100 width=32) (actual time=4.619..4.619 rows=1 loops=652)

  • Filter: ((data <= periodo.datafinal) AND (acao = acoes.acao))
  • Rows Removed by Filter: 14750
20. 69.112 13,035.436 ↑ 1.0 1 652

Aggregate (cost=1,643.86..1,643.87 rows=1 width=32) (actual time=19.992..19.993 rows=1 loops=652)

21. 12,966.324 12,966.324 ↓ 24.0 24 652

CTE Scan on preco preco_2 (cost=0.00..1,643.84 rows=1 width=36) (actual time=5.350..19.887 rows=24 loops=652)

  • Filter: ((data >= periodo.datainicial) AND (data <= periodo.datafinal) AND (acao = acoes.acao))
  • Rows Removed by Filter: 59752
22. 73.024 13,162.576 ↑ 1.0 1 652

Aggregate (cost=1,643.86..1,643.87 rows=1 width=32) (actual time=20.188..20.188 rows=1 loops=652)

23. 13,089.552 13,089.552 ↓ 24.0 24 652

CTE Scan on preco preco_3 (cost=0.00..1,643.84 rows=1 width=36) (actual time=5.506..20.076 rows=24 loops=652)

  • Filter: ((data >= periodo.datainicial) AND (data <= periodo.datafinal) AND (acao = acoes.acao))
  • Rows Removed by Filter: 59752
24. 69.764 13,118.892 ↑ 1.0 1 652

Aggregate (cost=1,643.86..1,643.87 rows=1 width=32) (actual time=20.121..20.121 rows=1 loops=652)

25. 13,049.128 13,049.128 ↓ 24.0 24 652

CTE Scan on preco preco_4 (cost=0.00..1,643.84 rows=1 width=36) (actual time=5.510..20.014 rows=24 loops=652)

  • Filter: ((data >= periodo.datainicial) AND (data <= periodo.datafinal) AND (acao = acoes.acao))
  • Rows Removed by Filter: 59752
26. 9.780 13,059.560 ↑ 1.0 1 652

Aggregate (cost=1,643.84..1,643.85 rows=1 width=8) (actual time=20.030..20.030 rows=1 loops=652)

27. 13,049.780 13,049.780 ↓ 24.0 24 652

CTE Scan on preco preco_5 (cost=0.00..1,643.84 rows=1 width=0) (actual time=5.605..20.015 rows=24 loops=652)

  • Filter: ((data >= periodo.datainicial) AND (data <= periodo.datafinal) AND (acao = acoes.acao))
  • Rows Removed by Filter: 59752
Planning time : 5.464 ms
Execution time : 62,283.016 ms