explain.depesz.com

PostgreSQL's explain analyze made readable

Result: au9E : Optimization for: Optimization for: Optimization for: Optimization for: plan #jJl1; plan #ev9W; plan #zcrb; plan #w1J7

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 27.337 48.402 ↑ 1.0 1 1

CTE Scan on xx (cost=6,749.17..6,749.19 rows=1 width=9) (actual time=48.401..48.402 rows=1 loops=1)

2.          

CTE xx

3. 1.557 27.326 ↑ 1.0 1 1

Aggregate (cost=3,467.25..3,467.26 rows=1 width=8) (actual time=27.326..27.326 rows=1 loops=1)

4. 5.650 25.769 ↑ 1.0 26,999 1

Hash Join (cost=234.95..3,399.53 rows=27,089 width=0) (actual time=1.690..25.769 rows=26,999 loops=1)

  • Hash Cond: (ar.definition_id = rd.id)
5. 18.496 18.496 ↑ 1.0 26,999 1

Seq Scan on analytical_results ar (cost=0.00..3,093.41 rows=27,089 width=4) (actual time=0.013..18.496 rows=26,999 loops=1)

  • Filter: ((type)::text = 'text'::text)
  • Rows Removed by Filter: 45041
6. 0.767 1.623 ↑ 1.0 5,420 1

Hash (cost=167.20..167.20 rows=5,420 width=4) (actual time=1.623..1.623 rows=5,420 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 255kB
7. 0.856 0.856 ↑ 1.0 5,420 1

Seq Scan on result_definitions rd (cost=0.00..167.20 rows=5,420 width=4) (actual time=0.007..0.856 rows=5,420 loops=1)

8.          

Initplan (for CTE Scan)

9. 0.017 21.065 ↑ 1.0 1 1

Aggregate (cost=3,281.89..3,281.91 rows=1 width=1) (actual time=21.065..21.065 rows=1 loops=1)

10. 0.011 21.048 ↓ 6.9 55 1

Nested Loop (cost=0.28..3,281.87 rows=8 width=0) (actual time=0.286..21.048 rows=55 loops=1)

11. 0.024 0.024 ↑ 1.0 1 1

Index Only Scan using result_definitions_pkey on result_definitions rd_1 (cost=0.28..8.30 rows=1 width=4) (actual time=0.021..0.024 rows=1 loops=1)

  • Index Cond: (id = 4827)
  • Heap Fetches: 1
12. 21.013 21.013 ↓ 6.9 55 1

Seq Scan on analytical_results ar_1 (cost=0.00..3,273.49 rows=8 width=4) (actual time=0.263..21.013 rows=55 loops=1)

  • Filter: ((definition_id = 4827) AND ((type)::text = 'text'::text))
  • Rows Removed by Filter: 71985
Planning time : 0.625 ms
Execution time : 48.535 ms