explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KM2Cq

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,855.467 ↑ 1.0 25 1

Limit (cost=243,142.21..243,142.27 rows=25 width=33) (actual time=4,855.464..4,855.467 rows=25 loops=1)

2. 13.015 4,855.465 ↑ 799.4 25 1

Sort (cost=243,142.21..243,192.17 rows=19,986 width=33) (actual time=4,855.463..4,855.465 rows=25 loops=1)

  • Sort Key: references1_.reference_sortable
  • Sort Method: top-N heapsort Memory: 26kB
3. 10.994 4,842.450 ↓ 1.3 26,293 1

Nested Loop Left Join (cost=94,870.78..242,578.22 rows=19,986 width=33) (actual time=1,886.082..4,842.450 rows=26,293 loops=1)

4. 76.951 4,148.930 ↓ 1.3 26,251 1

Nested Loop (cost=94,870.34..154,313.56 rows=19,986 width=8) (actual time=1,886.054..4,148.930 rows=26,251 loops=1)

5. 120.200 1,931.659 ↓ 30.0 178,360 1

HashAggregate (cost=94,869.92..94,929.34 rows=5,942 width=8) (actual time=1,885.903..1,931.659 rows=178,360 loops=1)

  • Group Key: reference3_.external_id
6. 1,134.118 1,811.459 ↓ 1.9 279,200 1

Bitmap Heap Scan on reference_t reference3_ (cost=10,072.19..94,497.85 rows=148,829 width=8) (actual time=681.009..1,811.459 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. 677.341 677.341 ↓ 1.6 283,072 1

Bitmap Index Scan on reference_upper_gist_idx (cost=0.00..10,034.98 rows=175,809 width=0) (actual time=677.341..677.341 rows=283,072 loops=1)

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

Index Scan using pk_case_t on case_t case0_ (cost=0.42..9.98 rows=1 width=8) (actual time=0.012..0.012 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: 1
9.          

SubPlan (for Index Scan)

10. 784.470 784.470 ↑ 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.005..0.005 rows=2 loops=156,894)

  • Index Cond: (case0_.id = case_id)
11. 682.526 682.526 ↑ 3.0 1 26,251

Index Scan using reference_case_idx on reference_t references1_ (cost=0.43..4.39 rows=3 width=33) (actual time=0.021..0.026 rows=1 loops=26,251)

  • Index Cond: (case0_.id = external_id)
  • Filter: (reference_type_id = 38)
  • Rows Removed by Filter: 10
Planning time : 1.054 ms
Execution time : 4,857.343 ms