explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UcHb

Settings
# exclusive inclusive rows x rows loops node
1. 28.968 25,171.877 ↑ 3.8 2,499 1

Unique (cost=1.81..10,570,035.80 rows=9,615 width=72) (actual time=1.757..25,171.877 rows=2,499 loops=1)

2. 306.338 25,142.909 ↑ 1.0 86,921 1

Nested Loop (cost=1.81..10,569,817.87 rows=87,170 width=72) (actual time=1.755..25,142.909 rows=86,921 loops=1)

3. 53.203 237.928 ↑ 1.0 86,921 1

Merge Join (cost=1.81..13,467.39 rows=87,170 width=33) (actual time=1.207..237.928 rows=86,921 loops=1)

  • Merge Cond: (a_bk.file_id = a_fl.file_id)
4. 180.884 180.884 ↑ 1.0 86,921 1

Index Only Scan using idx_pcsa_adhoc_bookmark_session_id_file_id on pcsa_adhoc_bookmark a_bk (cost=0.42..11,805.80 rows=87,170 width=16) (actual time=0.038..180.884 rows=86,921 loops=1)

  • Index Cond: (session_id = 5)
  • Heap Fetches: 86921
5. 3.841 3.841 ↑ 2.9 4,704 1

Index Scan using pcsa_adhoc_file_pkey on pcsa_adhoc_file a_fl (cost=0.29..537.71 rows=13,837 width=25) (actual time=0.007..3.841 rows=4,704 loops=1)

6. 0.000 0.000 ↑ 1.0 1 86,921

Materialize (cost=0.00..3.50 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=86,921)

7. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on pcsa_adhoc_session a_ses (cost=0.00..3.50 rows=1 width=8) (actual time=0.009..0.023 rows=1 loops=1)

  • Filter: (session_id = 5)
  • Rows Removed by Filter: 119
8.          

SubPlan (forNested Loop)

9. 13,733.518 24,598.643 ↑ 1.0 1 86,921

Aggregate (cost=121.07..121.09 rows=1 width=32) (actual time=0.282..0.283 rows=1 loops=86,921)

10.          

CTE _

11. 6,692.917 7,127.522 ↓ 62.0 62 86,921

Bitmap Heap Scan on pcsa_hit_audit_session au (cost=4.23..121.05 rows=1 width=104) (actual time=0.010..0.082 rows=62 loops=86,921)

  • Recheck Cond: ((file_name)::text = a_fl.filename)
  • Filter: (session_id = a_ses.session_id)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=4598653
12. 434.605 434.605 ↓ 2.0 62 86,921

Bitmap Index Scan on file_name_idx (cost=0.00..4.23 rows=31 width=0) (actual time=0.005..0.005 rows=62 loops=86,921)

  • Index Cond: ((file_name)::text = a_fl.filename)
13. 10,865.125 10,865.125 ↓ 62.0 62 86,921

CTE Scan on _ (cost=0.00..0.02 rows=1 width=24) (actual time=0.015..0.125 rows=62 loops=86,921)

Planning time : 0.537 ms
Execution time : 25,173.337 ms