explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PcJo : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #wwUy; plan #nj17; plan #1teg; plan #yQjO; plan #4NN3; plan #HNXk

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 5,740.957 ↑ 1.0 25 1

Limit (cost=252,257.97..252,258.03 rows=25 width=41) (actual time=5,740.956..5,740.957 rows=25 loops=1)

2. 43.910 5,740.956 ↑ 1,374.9 25 1

Sort (cost=252,257.97..252,343.90 rows=34,373 width=41) (actual time=5,740.955..5,740.956 rows=25 loops=1)

  • Sort Key: references1_.reference_sortable, case0_.reception_date DESC, case0_.id DESC
  • Sort Method: top-N heapsort Memory: 26kB
3. 60.845 5,697.046 ↓ 2.6 88,318 1

Nested Loop Left Join (cost=94,871.23..251,287.99 rows=34,373 width=41) (actual time=1,884.833..5,697.046 rows=88,318 loops=1)

4. 62.186 4,401.051 ↓ 2.6 88,225 1

Nested Loop (cost=94,870.80..164,701.51 rows=34,373 width=16) (actual time=1,884.801..4,401.051 rows=88,225 loops=1)

5. 34.488 3,397.501 ↓ 3.9 156,894 1

Nested Loop (cost=94,870.37..127,310.68 rows=39,972 width=24) (actual time=1,884.701..3,397.501 rows=156,894 loops=1)

6. 125.956 1,936.133 ↓ 30.0 178,360 1

HashAggregate (cost=94,869.94..94,929.36 rows=5,942 width=8) (actual time=1,884.654..1,936.133 rows=178,360 loops=1)

  • Group Key: reference3_.external_id
7. 1,134.123 1,810.177 ↓ 1.9 279,200 1

Bitmap Heap Scan on reference_t reference3_ (cost=10,072.20..94,497.87 rows=148,829 width=8) (actual time=679.724..1,810.177 rows=279,200 loops=1)

  • Recheck Cond: (upper((reference)::text) ~~ '%201%'::text)
  • Rows Removed by Index Recheck: 1,235,548
  • Filter: (reference_type_id <> 0)
  • Rows Removed by Filter: 3,870
  • Heap Blocks: exact=27,921 lossy=26,540
8. 676.054 676.054 ↓ 1.6 283,072 1

Bitmap Index Scan on reference_upper_gist_idx (cost=0.00..10,034.99 rows=175,810 width=0) (actual time=676.054..676.054 rows=283,072 loops=1)

  • Index Cond: (upper((reference)::text) ~~ '%201%'::text)
9. 1,426.880 1,426.880 ↑ 1.0 1 178,360

Index Scan using pk_case_t on case_t case0_ (cost=0.42..5.44 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=178,360)

  • Index Cond: (id = reference3_.external_id)
  • Filter: ((NOT a_new_version_exists) AND ((case_type)::text <> 'UTG'::text) AND (reception_date >= '2010-01-06 00:00:00'::timestamp without time zone) AND (reception_date <= '2020-01-06 00:00:00'::timestamp without time zone) (...)
  • Rows Removed by Filter: 0
10. 941.364 941.364 ↑ 1.0 1 156,894

Index Scan using case_actor_authority_case_idx on case_actor_authority_t actors2_ (cost=0.43..0.93 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=156,894)

  • Index Cond: (case_id = case0_.id)
  • Filter: (authority_abstract_id = 20)
  • Rows Removed by Filter: 2
11. 1,235.150 1,235.150 ↑ 3.0 1 88,225

Index Scan using reference_case_idx on reference_t references1_ (cost=0.43..2.49 rows=3 width=33) (actual time=0.014..0.014 rows=1 loops=88,225)

  • Index Cond: ((case0_.id = external_id) AND (reference_type_id = 38))
Planning time : 1.438 ms
Execution time : 5,742.827 ms