explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lpQG

Settings
# exclusive inclusive rows x rows loops node
1. 3.467 1,222.569 ↓ 24.0 4,805 1

Sort (cost=4,966.03..4,966.53 rows=200 width=4) (actual time=1,221.653..1,222.569 rows=4,805 loops=1)

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

CTE r

3. 311.410 1,208.223 ↓ 16.1 11,642 1

Recursive Union (cost=8.86..4,940.15 rows=722 width=12) (actual time=0.678..1,208.223 rows=11,642 loops=1)

  • Buffers: shared hit=425573
4. 0.666 0.678 ↓ 2.5 5 1

Index Only Scan using url_relations_pkey on url_relations (cost=8.86..18.65 rows=2 width=12) (actual time=0.676..0.678 rows=5 loops=1)

  • Index Cond: (metapackage_id = $1)
  • Heap Fetches: 5
  • Buffers: shared hit=8
5.          

Initplan (for Index Only Scan)

6. 0.012 0.012 ↑ 1.0 1 1

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

  • Index Cond: (effname = 'evince'::text)
  • Buffers: shared hit=4
7. 288.053 896.135 ↓ 951.1 68,481 19

Nested Loop (cost=8.88..490.71 rows=72 width=12) (actual time=0.018..47.165 rows=68,481 loops=19)

  • Buffers: shared hit=425565
8. 2.698 2.698 ↓ 30.6 613 19

WorkTable Scan on r r_1 (cost=0.00..0.40 rows=20 width=12) (actual time=0.000..0.142 rows=613 loops=19)

9. 465.680 605.384 ↓ 28.0 112 11,642

Bitmap Heap Scan on url_relations url_relations_1 (cost=8.88..24.48 rows=4 width=12) (actual time=0.015..0.052 rows=112 loops=11,642)

  • Recheck Cond: ((metapackage_id = r_1.metapackage_id) OR (urlhash = r_1.urlhash))
  • Heap Blocks: exact=347545
  • Buffers: shared hit=425565
10. 11.642 139.704 ↓ 0.0 0 11,642

BitmapOr (cost=8.88..8.88 rows=4 width=0) (actual time=0.012..0.012 rows=0 loops=11,642)

  • Buffers: shared hit=78020
11. 34.926 34.926 ↓ 2.5 5 11,642

Bitmap Index Scan on url_relations_pkey (cost=0.00..4.44 rows=2 width=0) (actual time=0.003..0.003 rows=5 loops=11,642)

  • Index Cond: (metapackage_id = r_1.metapackage_id)
  • Buffers: shared hit=35201
12. 93.136 93.136 ↓ 54.0 108 11,642

Bitmap Index Scan on url_relations_urlhash_metapackage_id_idx (cost=0.00..4.44 rows=2 width=0) (actual time=0.008..0.008 rows=108 loops=11,642)

  • Index Cond: (urlhash = r_1.urlhash)
  • Buffers: shared hit=42819
13. 6.235 1,219.102 ↓ 24.0 4,805 1

HashAggregate (cost=16.25..18.25 rows=200 width=4) (actual time=1,217.465..1,219.102 rows=4,805 loops=1)

  • Group Key: r.metapackage_id
  • Buffers: shared hit=425573
14. 1,212.867 1,212.867 ↓ 16.1 11,642 1

CTE Scan on r (cost=0.00..14.44 rows=722 width=4) (actual time=0.679..1,212.867 rows=11,642 loops=1)

  • Buffers: shared hit=425573
Planning time : 0.402 ms
Execution time : 1,223.330 ms