explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AoCI

Settings
# exclusive inclusive rows x rows loops node
1. 26.989 135.885 ↑ 1.2 2,475 1

WindowAgg (cost=13,413.55..13,427.88 rows=2,867 width=236) (actual time=108.743..135.885 rows=2,475 loops=1)

2.          

CTE source

3. 34.633 56.908 ↑ 2.3 2,475 1

Index Scan using tb_bar_one_day_crypto_pk on tb_bar_one_day tee (cost=0.06..13,271.74 rows=5,731 width=140) (actual time=0.188..56.908 rows=2,475 loops=1)

  • Index Cond: ((id_stock = 6,098) AND (id_point >= 20,100,902) AND (id_point <= 20,200,902))
4.          

SubPlan (for Index Scan)

5. 4.950 22.275 ↑ 1.0 1 2,475

Aggregate (cost=1.56..1.57 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=2,475)

6. 0.000 17.325 ↑ 1.0 2 2,475

Limit (cost=0.06..1.56 rows=2 width=14) (actual time=0.006..0.007 rows=2 loops=2,475)

7. 17.325 17.325 ↑ 990.5 2 2,475

Index Scan Backward using tb_bar_one_day_crypto_pk on tb_bar_one_day (cost=0.06..1,491.99 rows=1,981 width=14) (actual time=0.006..0.007 rows=2 loops=2,475)

  • Index Cond: ((id_stock = 6,098) AND (id_point <= tee.id_point))
8.          

CTE zero_based

9. 1.091 64.050 ↑ 1.2 2,475 1

Sort (cost=70.20..70.92 rows=2,867 width=172) (actual time=63.888..64.050 rows=2,475 loops=1)

  • Sort Key: "*SELECT* 1".id_point
  • Sort Method: quicksort Memory: 445kB
10. 2.352 62.959 ↑ 1.2 2,475 1

HashAggregate (cost=50.87..53.74 rows=2,867 width=172) (actual time=62.472..62.959 rows=2,475 loops=1)

  • Group Key: "*SELECT* 1".id_stock, "*SELECT* 1".id_point, "*SELECT* 1".mesano, "*SELECT* 1".ano, "*SELECT* 1".idx, "*SELECT* 1".day_agg, (("*SELECT* 1".day_agg2)::numeric)
11. 0.156 60.607 ↑ 1.2 2,475 1

Append (cost=14.33..45.85 rows=2,867 width=172) (actual time=58.994..60.607 rows=2,475 loops=1)

12. 0.005 58.994 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=14.33..14.33 rows=1 width=172) (actual time=58.993..58.994 rows=1 loops=1)

13. 0.002 58.989 ↑ 1.0 1 1

Limit (cost=14.33..14.33 rows=1 width=144) (actual time=58.988..58.989 rows=1 loops=1)

14. 0.594 58.987 ↑ 5,731.0 1 1

Sort (cost=14.33..15.76 rows=5,731 width=144) (actual time=58.987..58.987 rows=1 loops=1)

  • Sort Key: s.id_point
  • Sort Method: top-N heapsort Memory: 25kB
15. 58.393 58.393 ↑ 2.3 2,475 1

CTE Scan on source s (cost=0.00..11.46 rows=5,731 width=144) (actual time=0.191..58.393 rows=2,475 loops=1)

16. 0.734 1.457 ↑ 1.2 2,474 1

CTE Scan on source s_1 (cost=14.33..27.22 rows=2,866 width=172) (actual time=0.749..1.457 rows=2,474 loops=1)

  • Filter: (NOT (hashed SubPlan 3))
  • Rows Removed by Filter: 1
17.          

SubPlan (for CTE Scan)

18. 0.002 0.723 ↑ 1.0 1 1

Limit (cost=14.33..14.33 rows=1 width=8) (actual time=0.721..0.723 rows=1 loops=1)

19. 0.360 0.721 ↑ 5,731.0 1 1

Sort (cost=14.33..15.76 rows=5,731 width=8) (actual time=0.721..0.721 rows=1 loops=1)

  • Sort Key: x.id_point
  • Sort Method: top-N heapsort Memory: 25kB
20. 0.361 0.361 ↑ 2.3 2,475 1

CTE Scan on source x (cost=0.00..11.46 rows=5,731 width=8) (actual time=0.001..0.361 rows=2,475 loops=1)

21. 1.405 108.896 ↑ 1.2 2,475 1

Sort (cost=70.90..71.61 rows=2,867 width=188) (actual time=108.715..108.896 rows=2,475 loops=1)

  • Sort Key: zero_based.ano, zero_based.id_point
  • Sort Method: quicksort Memory: 445kB
22. 21.545 107.491 ↑ 1.2 2,475 1

WindowAgg (cost=46.55..54.43 rows=2,867 width=188) (actual time=85.766..107.491 rows=2,475 loops=1)

23. 1.245 85.946 ↑ 1.2 2,475 1

Sort (cost=46.55..47.26 rows=2,867 width=180) (actual time=85.745..85.946 rows=2,475 loops=1)

  • Sort Key: zero_based.mesano, zero_based.id_point
  • Sort Method: quicksort Memory: 445kB
24. 19.437 84.701 ↑ 1.2 2,475 1

WindowAgg (cost=22.20..30.08 rows=2,867 width=180) (actual time=65.319..84.701 rows=2,475 loops=1)

25. 0.656 65.264 ↑ 1.2 2,475 1

Sort (cost=22.20..22.91 rows=2,867 width=172) (actual time=65.094..65.264 rows=2,475 loops=1)

  • Sort Key: zero_based.id_stock, zero_based.id_point
  • Sort Method: quicksort Memory: 445kB
26. 64.608 64.608 ↑ 1.2 2,475 1

CTE Scan on zero_based (cost=0.00..5.73 rows=2,867 width=172) (actual time=63.889..64.608 rows=2,475 loops=1)

Planning time : 1.710 ms
Execution time : 137.863 ms