explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7BCb : Optimization for: Optimization for: plan #DS8Y; plan #HQKR

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.061 1,053.627 ↓ 1.5 64 1

Nested Loop (cost=151.65..344.10 rows=43 width=398) (actual time=119.857..1,053.627 rows=64 loops=1)

2. 0.028 1,053.502 ↓ 1.5 64 1

Nested Loop (cost=151.50..336.51 rows=43 width=106) (actual time=119.846..1,053.502 rows=64 loops=1)

3. 0.052 1,053.346 ↓ 1.5 64 1

Nested Loop (cost=151.22..321.73 rows=43 width=70) (actual time=119.837..1,053.346 rows=64 loops=1)

4. 0.040 1,053.166 ↓ 1.5 64 1

Merge Right Join (cost=150.93..306.17 rows=43 width=50) (actual time=119.829..1,053.166 rows=64 loops=1)

  • Merge Cond: (sdr.survey_distribution_id = sd.id)
5. 0.048 1,052.394 ↑ 20.9 20 1

Nested Loop (cost=0.69..64,952.27 rows=419 width=37) (actual time=33.996..1,052.394 rows=20 loops=1)

6. 45.670 1,052.266 ↑ 20.9 20 1

Index Scan using sdr_id_created_at on survey_distribution_result sdr (cost=0.42..64,765.52 rows=419 width=20) (actual time=33.990..1,052.266 rows=20 loops=1)

  • Filter: (created_at = (SubPlan 1))
  • Rows Removed by Filter: 83863
7.          

SubPlan (forIndex Scan)

8. 0.000 1,006.596 ↑ 1.0 1 83,883

Limit (cost=0.29..0.70 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=83,883)

9. 1,006.596 1,006.596 ↑ 9,321.0 1 83,883

Index Scan using sdr_created_at2 on survey_distribution_result sdr2 (cost=0.29..3,804.50 rows=9,321 width=8) (actual time=0.012..0.012 rows=1 loops=83,883)

  • Filter: (survey_distribution_id = sdr.survey_distribution_id)
  • Rows Removed by Filter: 24
10. 0.080 0.080 ↑ 1.0 1 20

Index Scan using mr_pkey on mr m_1 (cost=0.28..0.44 rows=1 width=25) (actual time=0.004..0.004 rows=1 loops=20)

  • Index Cond: (id = sdr.submitter_id)
11. 0.044 0.732 ↓ 1.5 64 1

Sort (cost=150.24..150.34 rows=43 width=17) (actual time=0.714..0.732 rows=64 loops=1)

  • Sort Key: sd.id
  • Sort Method: quicksort Memory: 30kB
12. 0.065 0.688 ↓ 1.5 64 1

Nested Loop (cost=5.19..149.07 rows=43 width=17) (actual time=0.053..0.688 rows=64 loops=1)

13. 0.014 0.175 ↓ 1.5 64 1

Nested Loop (cost=4.90..134.26 rows=43 width=4) (actual time=0.046..0.175 rows=64 loops=1)

14. 0.010 0.010 ↑ 1.0 1 1

Index Scan using unq_cwid on mr m (cost=0.28..8.29 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((cwid)::text = 'GFXTL'::text)
15. 0.136 0.151 ↓ 1.5 64 1

Bitmap Heap Scan on survey_distribution_mr sdm (cost=4.62..125.54 rows=43 width=8) (actual time=0.032..0.151 rows=64 loops=1)

  • Recheck Cond: (mr_id = m.id)
  • Heap Blocks: exact=62
16. 0.015 0.015 ↓ 1.5 64 1

Bitmap Index Scan on survey_distribution_mr_mr_id_survey_distribution_id_key (cost=0.00..4.61 rows=43 width=0) (actual time=0.015..0.015 rows=64 loops=1)

  • Index Cond: (mr_id = m.id)
17. 0.448 0.448 ↑ 1.0 1 64

Index Scan using survey_distribution_pkey on survey_distribution sd (cost=0.29..0.33 rows=1 width=17) (actual time=0.007..0.007 rows=1 loops=64)

  • Index Cond: (id = sdm.survey_distribution_id)
18. 0.128 0.128 ↑ 1.0 1 64

Index Scan using dr_pkey on dr d (cost=0.29..0.35 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=64)

  • Index Cond: (id = sd.dr_id)
19. 0.128 0.128 ↑ 1.0 1 64

Index Scan using institution_pkey on institution i (cost=0.29..0.33 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=64)

  • Index Cond: (id = sd.institution_id)
20. 0.064 0.064 ↑ 1.0 1 64

Index Scan using survey_pkey on survey su (cost=0.14..0.16 rows=1 width=296) (actual time=0.001..0.001 rows=1 loops=64)

  • Index Cond: (id = sd.survey_id)