explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RCe : Optimization for: Optimization for: plan #bCCI; plan #e3AZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 7,481.072 ↑ 1.0 25 1

Limit (cost=243,142.23..243,142.29 rows=25 width=41) (actual time=7,481.070..7,481.072 rows=25 loops=1)

2. 44.768 7,481.070 ↑ 799.4 25 1

Sort (cost=243,142.23..243,192.20 rows=19,986 width=41) (actual time=7,481.069..7,481.070 rows=25 loops=1)

  • Sort Key: references1_.reference_sortable, case0_.reception_date DESC, case0_.id DESC
  • Sort Method: top-N heapsort Memory: 26kB
3. 46.214 7,436.302 ↓ 4.4 88,318 1

Nested Loop Left Join (cost=94,870.80..242,578.24 rows=19,986 width=41) (actual time=1,892.627..7,436.302 rows=88,318 loops=1)

4. 32.585 4,831.563 ↓ 4.4 88,225 1

Nested Loop (cost=94,870.37..154,313.58 rows=19,986 width=16) (actual time=1,892.599..4,831.563 rows=88,225 loops=1)

5. 128.631 1,945.218 ↓ 30.0 178,360 1

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

  • Group Key: reference3_.external_id
6. 1,138.176 1,816.587 ↓ 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=682.133..1,816.587 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
7. 678.411 678.411 ↓ 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=678.411..678.411 rows=283,072 loops=1)

  • Index Cond: (upper((reference)::text) ~~ '%201%'::text)
8. 1,598.608 2,853.760 ↓ 0.0 0 178,360

Index Scan using pk_case_t on case_t case0_ (cost=0.42..9.98 rows=1 width=16) (actual time=0.016..0.016 rows=0 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) AND ( (...)
  • Rows Removed by Filter: 0
9.          

SubPlan (for Index Scan)

10. 1,255.152 1,255.152 ↑ 1.5 2 156,894

Index Scan using case_actor_authority_case_idx on case_actor_authority_t actors2_ (cost=0.43..8.65 rows=3 width=8) (actual time=0.008..0.008 rows=2 loops=156,894)

  • Index Cond: (case0_.id = case_id)
11. 2,558.525 2,558.525 ↑ 3.0 1 88,225

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

  • Index Cond: (case0_.id = external_id)
  • Filter: (reference_type_id = 38)
  • Rows Removed by Filter: 14
Planning time : 1.069 ms
Execution time : 7,482.941 ms