explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P3nH

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 10,967.089 ↑ 1.0 1 1

Result (cost=1,230,621.97..1,230,621.98 rows=1 width=8) (actual time=10,967.089..10,967.089 rows=1 loops=1)

2.          

Initplan (for Result)

3. 63.053 1,125.945 ↑ 1.0 1 1

Aggregate (cost=176,228.50..176,228.51 rows=1 width=8) (actual time=1,125.945..1,125.945 rows=1 loops=1)

4.          

CTE times

5. 690.814 907.552 ↓ 1.0 588,547 1

Bitmap Heap Scan on archive_event (cost=8,933.76..163,023.10 rows=586,907 width=8) (actual time=275.143..907.552 rows=588,547 loops=1)

  • Recheck Cond: (storage_id = 40)
  • Heap Blocks: exact=137208
6. 216.738 216.738 ↓ 1.1 665,858 1

Bitmap Index Scan on archive_event_f733a512 (cost=0.00..8,787.03 rows=586,907 width=0) (actual time=216.738..216.738 rows=665,858 loops=1)

  • Index Cond: (storage_id = 40)
7. 1,062.892 1,062.892 ↓ 1.0 588,547 1

CTE Scan on times (cost=0.00..11,738.14 rows=586,907 width=8) (actual time=275.147..1,062.892 rows=588,547 loops=1)

8. 0.004 9,841.136 ↑ 1.0 1 1

Aggregate (cost=1,054,393.44..1,054,393.45 rows=1 width=8) (actual time=9,841.136..9,841.136 rows=1 loops=1)

9.          

CTE untils

10. 9,841.130 9,841.130 ↓ 0.0 0 1

Seq Scan on archive_recpart (cost=0.00..1,054,329.25 rows=2,853 width=8) (actual time=9,841.130..9,841.130 rows=0 loops=1)

  • Filter: (filepath ~~ '/media/storage//media/storage/40%'::text)
  • Rows Removed by Filter: 28160787
11. 9,841.132 9,841.132 ↓ 0.0 0 1

CTE Scan on untils (cost=0.00..57.06 rows=2,853 width=8) (actual time=9,841.132..9,841.132 rows=0 loops=1)

Planning time : 0.396 ms
Execution time : 10,978.844 ms