explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E7pA

Settings
# exclusive inclusive rows x rows loops node
1. 31.408 6,855.228 ↑ 1.0 1 1

Aggregate (cost=1,321,253.48..1,321,253.49 rows=1 width=8) (actual time=6,855.228..6,855.228 rows=1 loops=1)

2. 56.991 6,823.820 ↓ 6.1 309,817 1

Nested Loop (cost=296,973.49..1,321,126.81 rows=50,667 width=8) (actual time=3,397.781..6,823.820 rows=309,817 loops=1)

3. 130.683 3,790.295 ↓ 1.8 330,726 1

Unique (cost=296,973.06..303,745.67 rows=179,932 width=8) (actual time=3,394.264..3,790.295 rows=330,726 loops=1)

4. 860.493 3,659.612 ↓ 1.0 1,371,524 1

Sort (cost=296,973.06..300,359.37 rows=1,354,522 width=8) (actual time=3,394.263..3,659.612 rows=1,371,524 loops=1)

  • Sort Key: reference2_.external_id
  • Sort Method: external merge Disk: 24112kB
5. 2,799.119 2,799.119 ↓ 1.0 1,371,524 1

Seq Scan on reference_t reference2_ (cost=0.00..140,497.39 rows=1,354,522 width=8) (actual time=0.036..2,799.119 rows=1,371,524 loops=1)

  • Filter: (upper((reference)::text) ~~ '%PU%'::text)
  • Rows Removed by Filter: 2845235
6. 2,047.083 2,976.534 ↑ 1.0 1 330,726

Index Scan using pk_case_t on case_t case0_ (cost=0.42..5.64 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=330,726)

  • Index Cond: (id = reference2_.external_id)
  • Filter: ((NOT a_new_version_exists) AND ((case_type)::text <> 'UTG'::text) AND ((case_type)::text = 'URBANISM'::text) AND (SubPlan 1))
  • Rows Removed by Filter: 0
7.          

SubPlan (forIndex Scan)

8. 929.451 929.451 ↑ 1.5 2 309,817

Index Scan using case_actor_authority_case_idx on case_actor_authority_t actors1_ (cost=0.43..8.66 rows=3 width=8) (actual time=0.003..0.003 rows=2 loops=309,817)

  • Index Cond: (case0_.id = case_id)
Planning time : 10.862 ms