explain.depesz.com

PostgreSQL's explain analyze made readable

Result: esh1

Settings
# exclusive inclusive rows x rows loops node
1. 1.926 1,466.378 ↓ 24.0 4,805 1

Sort (cost=130,063.89..130,064.39 rows=200 width=4) (actual time=1,465.888..1,466.378 rows=4,805 loops=1)

  • Sort Key: r.project_id
  • Sort Method: quicksort Memory: 418kB
  • Buffers: shared hit=305359
2.          

CTE r

3. 606.633 1,459.278 ↑ 79.5 4,805 1

Recursive Union (cost=8.86..121,457.43 rows=382,081 width=35) (actual time=0.032..1,459.278 rows=4,805 loops=1)

  • Buffers: shared hit=305359
4. 0.015 0.025 ↑ 1.0 1 1

Index Scan using project_related_project_id_idx on project_related (cost=8.86..16.88 rows=1 width=35) (actual time=0.025..0.025 rows=1 loops=1)

  • Index Cond: (project_id = $1)
  • Buffers: shared hit=8
5.          

Initplan (for Index Scan)

6. 0.010 0.010 ↑ 1.0 1 1

Index Scan using metapackages_effname_idx on metapackages (cost=0.42..8.44 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (effname = 'evince'::text)
  • Buffers: shared hit=4
7. 231.295 852.620 ↓ 2.6 101,059 10

Nested Loop (cost=42.01..11,379.89 rows=38,208 width=35) (actual time=0.030..85.262 rows=101,059 loops=10)

  • Buffers: shared hit=305351
8. 1.480 1.480 ↓ 48.0 480 10

WorkTable Scan on r r_1 (cost=0.00..0.20 rows=10 width=32) (actual time=0.000..0.148 rows=480 loops=10)

9. 437.255 619.845 ↑ 18.2 210 4,805

Bitmap Heap Scan on project_related project_related_1 (cost=42.01..1,099.76 rows=3,821 width=35) (actual time=0.045..0.129 rows=210 loops=4,805)

  • Recheck Cond: (urlhashes && r_1.urlhashes)
  • Heap Blocks: exact=265620
  • Buffers: shared hit=305351
10. 182.590 182.590 ↑ 18.2 210 4,805

Bitmap Index Scan on project_related_urlhashes_idx (cost=0.00..41.06 rows=3,821 width=0) (actual time=0.038..0.038 rows=210 loops=4,805)

  • Index Cond: (urlhashes && r_1.urlhashes)
  • Buffers: shared hit=39731
11. 3.080 1,464.452 ↓ 24.0 4,805 1

HashAggregate (cost=8,596.82..8,598.82 rows=200 width=4) (actual time=1,463.483..1,464.452 rows=4,805 loops=1)

  • Group Key: r.project_id
  • Buffers: shared hit=305359
12. 1,461.372 1,461.372 ↑ 79.5 4,805 1

CTE Scan on r (cost=0.00..7,641.62 rows=382,081 width=4) (actual time=0.036..1,461.372 rows=4,805 loops=1)

  • Buffers: shared hit=305359
Planning time : 0.462 ms
Execution time : 1,470.277 ms