explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DKA6

Settings
# exclusive inclusive rows x rows loops node
1. 0.535 21,615.266 ↑ 191.1 78 1

Sort (cost=27,680,458.87..27,680,496.14 rows=14,906 width=158) (actual time=21,615.249..21,615.266 rows=78 loops=1)

  • Sort Key: utl_arka_mview_storico.start_local_timestamp DESC
  • Sort Method: quicksort Memory: 885kB
2. 0.071 21,614.731 ↑ 191.1 78 1

Append (cost=0.28..27,679,425.62 rows=14,906 width=158) (actual time=2,313.746..21,614.731 rows=78 loops=1)

3. 0.011 0.011 ↓ 0.0 0 1

Index Scan using idx_utl_arka_mview_storico_date on utl_arka_mview_storico (cost=0.28..4.30 rows=1 width=6,622) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (reference_day > '2020-07-27'::date)
4. 1.124 21,614.649 ↑ 191.1 78 1

Nested Loop (cost=137,192.17..27,679,272.26 rows=14,905 width=158) (actual time=2,313.734..21,614.649 rows=78 loops=1)

5.          

Initplan (for Nested Loop)

6. 0.003 0.147 ↑ 1.0 1 1

Result (cost=0.31..0.32 rows=1 width=8) (actual time=0.147..0.147 rows=1 loops=1)

7.          

Initplan (for Result)

8. 0.001 0.144 ↑ 1.0 1 1

Limit (cost=0.28..0.31 rows=1 width=8) (actual time=0.144..0.144 rows=1 loops=1)

9. 0.143 0.143 ↑ 5,769.0 1 1

Index Only Scan Backward using idx_utl_arka_miew_storico_startlocaltime on utl_arka_mview_storico utl_arka_mview_storico_1 (cost=0.28..173.24 rows=5,769 width=8) (actual time=0.143..0.143 rows=1 loops=1)

  • Index Cond: (start_local_timestamp IS NOT NULL)
  • Heap Fetches: 0
10. 0.332 21,551.914 ↑ 191.1 78 1

Nested Loop (cost=135,373.09..569,953.26 rows=14,905 width=74) (actual time=2,312.823..21,551.914 rows=78 loops=1)

11. 12,577.514 14,833.208 ↑ 191.1 78 1

Hash Anti Join (cost=135,372.22..186,024.80 rows=14,905 width=54) (actual time=2,291.909..14,833.208 rows=78 loops=1)

  • Hash Cond: ((ga.device_id = gb.device_id) AND ((ga.local_timestamp)::date = (gb.local_timestamp)::date))
  • Join Filter: ((gb.local_timestamp < ga.local_timestamp) AND ((ga.local_timestamp - gb.local_timestamp) < '00:15:00'::interval))
  • Rows Removed by Join Filter: 53,339,315
12. 44.258 56.349 ↓ 3.1 52,625 1

Bitmap Heap Scan on utl_arka ga (cost=1,283.67..39,123.94 rows=16,768 width=54) (actual time=12.691..56.349 rows=52,625 loops=1)

  • Recheck Cond: (((local_timestamp)::date > '2020-07-27'::date) AND (local_timestamp > $1))
  • Heap Blocks: exact=2,063
13. 12.091 12.091 ↓ 3.1 52,625 1

Bitmap Index Scan on idx_utl_arka_timestamp_date_devid_ltimestamp (cost=0.00..1,279.48 rows=16,768 width=0) (actual time=12.091..12.091 rows=52,625 loops=1)

  • Index Cond: (((local_timestamp)::date > '2020-07-27'::date) AND (local_timestamp > $1))
14. 1,013.433 2,199.345 ↑ 1.0 2,398,789 1

Hash (cost=86,393.22..86,393.22 rows=2,398,822 width=12) (actual time=2,199.345..2,199.345 rows=2,398,789 loops=1)

  • Buckets: 131,072 Batches: 64 Memory Usage: 3,342kB
15. 1,185.912 1,185.912 ↑ 1.0 2,398,789 1

Seq Scan on utl_arka gb (cost=0.00..86,393.22 rows=2,398,822 width=12) (actual time=0.087..1,185.912 rows=2,398,789 loops=1)

16. 0.156 6,718.374 ↑ 1.0 1 78

Limit (cost=0.86..25.74 rows=1 width=864) (actual time=86.132..86.133 rows=1 loops=78)

17. 4,280.308 6,718.218 ↑ 72.0 1 78

Nested Loop Anti Join (cost=0.86..1,791.75 rows=72 width=864) (actual time=86.131..86.131 rows=1 loops=78)

  • Join Filter: ((sb.local_timestamp > s.local_timestamp) AND (sb.device_id = s.device_id) AND ((sb.local_timestamp - s.local_timestamp) < '00:15:00'::interval) AND ((sb.local_timestamp)::date = (s.local_timestamp)::date))
  • Rows Removed by Join Filter: 686,758
18. 17.160 17.160 ↓ 8.3 675 78

Index Scan using idx_utl_arka_timestamp_date_devid_ltimestamp on utl_arka s (cost=0.43..317.37 rows=81 width=24) (actual time=0.024..0.220 rows=675 loops=78)

  • Index Cond: (((local_timestamp)::date = (ga.local_timestamp)::date) AND (device_id = ga.device_id) AND (local_timestamp >= ga.local_timestamp))
19. 2,339.708 2,420.750 ↓ 4.2 1,019 52,625

Materialize (cost=0.43..947.41 rows=244 width=12) (actual time=0.000..0.046 rows=1,019 loops=52,625)

20. 81.042 81.042 ↓ 7.3 1,786 78

Index Only Scan using idx_utl_arka_timestamp_date_devid_ltimestamp on utl_arka sb (cost=0.43..946.19 rows=244 width=12) (actual time=0.014..1.039 rows=1,786 loops=78)

  • Index Cond: ((((local_timestamp)::date) = (ga.local_timestamp)::date) AND (device_id = ga.device_id))
  • Heap Fetches: 139,288
21. 19.734 61.464 ↑ 1.0 1 78

Aggregate (cost=1,818.76..1,818.77 rows=1 width=32) (actual time=0.788..0.788 rows=1 loops=78)

22. 41.730 41.730 ↑ 1.5 675 78

Index Scan using idx_utl_arka_devid_date_day on utl_arka gea (cost=0.43..1,806.26 rows=1,000 width=40) (actual time=0.029..0.535 rows=675 loops=78)

  • Index Cond: ((device_id = ga.device_id) AND (local_timestamp >= ga.local_timestamp) AND (local_timestamp <= s.local_timestamp))
Planning time : 3.761 ms
Execution time : 21,615.614 ms