explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DQw7

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 912.738 ↓ 0.0 0 1

Limit (cost=5,573.72..305,140.98 rows=25 width=8) (actual time=912.738..912.738 rows=0 loops=1)

2. 0.000 912.736 ↓ 0.0 0 1

Unique (cost=5,573.72..32,981,937.56 rows=2,752 width=8) (actual time=912.736..912.736 rows=0 loops=1)

3. 0.000 912.736 ↓ 0.0 0 1

Nested Loop (cost=5,573.72..32,981,930.68 rows=2,752 width=8) (actual time=912.736..912.736 rows=0 loops=1)

4. 191.601 912.736 ↓ 0.0 0 1

Nested Loop (cost=5,573.72..32,981,894.43 rows=2,752 width=16) (actual time=912.736..912.736 rows=0 loops=1)

  • Join Filter: (c.id = actors1_.case_id)
5. 101.954 101.954 ↑ 1.0 619,181 1

Index Only Scan using pk_case_actor_authority on case_actor_authority_t actors1_ (cost=0.43..54,969.02 rows=625,924 width=16) (actual time=0.026..101.954 rows=619,181 loops=1)

  • Index Cond: (authority_abstract_id = 21)
  • Heap Fetches: 8,288
6. 0.000 619.181 ↓ 0.0 0 619,181

Materialize (cost=5,573.29..3,849,633.73 rows=3,097 width=8) (actual time=0.001..0.001 rows=0 loops=619,181)

7. 0.000 732.583 ↓ 0.0 0 1

Nested Loop (cost=5,573.29..3,849,618.25 rows=3,097 width=8) (actual time=732.583..732.583 rows=0 loops=1)

  • Join Filter: (pdt.id = p.definition_id)
8. 0.002 732.583 ↓ 0.0 0 1

Nested Loop Left Join (cost=5,573.29..3,835,906.33 rows=3,097 width=16) (actual time=732.583..732.583 rows=0 loops=1)

9. 0.000 732.581 ↓ 0.0 0 1

Nested Loop Left Join (cost=5,572.86..3,833,969.49 rows=3,097 width=24) (actual time=732.581..732.581 rows=0 loops=1)

10. 0.000 732.581 ↓ 0.0 0 1

Nested Loop (cost=5,572.44..3,815,569.07 rows=3,097 width=16) (actual time=732.581..732.581 rows=0 loops=1)

11. 0.001 732.581 ↓ 0.0 0 1

Nested Loop (cost=5,572.01..3,813,701.54 rows=3,097 width=16) (actual time=732.581..732.581 rows=0 loops=1)

12. 17.537 732.580 ↓ 0.0 0 1

Bitmap Heap Scan on case_t c (cost=5,571.58..3,793,969.07 rows=3,369 width=40) (actual time=732.580..732.580 rows=0 loops=1)

  • Recheck Cond: (((case_type)::text = 'ENVIRONMENT'::text) AND ((reception_date IS NULL) OR ((reception_date > '2016-01-01 00:00:00'::timestamp without time zone) AND (reception_date < '2020-01-10 00:00:00'::timestamp without time zone))))
  • Filter: ((NOT deleted) AND (NOT a_new_version_exists) AND (upper(((SubPlan 1))::text) ~~ '%PE%'::text))
  • Rows Removed by Filter: 12,150
  • Heap Blocks: exact=6,078
13. 1.246 46.739 ↓ 0.0 0 1

BitmapAnd (cost=5,571.58..5,571.58 rows=17,826 width=0) (actual time=46.739..46.739 rows=0 loops=1)

14. 21.158 21.158 ↓ 1.1 81,341 1

Bitmap Index Scan on case_type_index (cost=0.00..2,163.22 rows=77,172 width=0) (actual time=21.158..21.158 rows=81,341 loops=1)

  • Index Cond: ((case_type)::text = 'ENVIRONMENT'::text)
15. 0.001 24.335 ↓ 0.0 0 1

BitmapOr (cost=3,407.28..3,407.28 rows=162,696 width=0) (actual time=24.335..24.335 rows=0 loops=1)

16. 4.160 4.160 ↓ 1.0 58,183 1

Bitmap Index Scan on case_reception_date_idx (cost=0.00..1,099.08 rows=56,887 width=0) (actual time=4.160..4.160 rows=58,183 loops=1)

  • Index Cond: (reception_date IS NULL)
17. 20.174 20.174 ↓ 1.1 118,458 1

Bitmap Index Scan on case_reception_date_idx (cost=0.00..2,306.52 rows=105,809 width=0) (actual time=20.174..20.174 rows=118,458 loops=1)

  • Index Cond: ((reception_date > '2016-01-01 00:00:00'::timestamp without time zone) AND (reception_date < '2020-01-10 00:00:00'::timestamp without time zone))
18.          

SubPlan (for Bitmap Heap Scan)

19. 0.000 668.304 ↓ 0.0 0 11,934

Limit (cost=0.43..210.96 rows=1 width=16) (actual time=0.056..0.056 rows=0 loops=11,934)

20. 668.304 668.304 ↓ 0.0 0 11,934

Index Scan using reference_case_idx on reference_t ref_region (cost=0.43..210.96 rows=1 width=16) (actual time=0.056..0.056 rows=0 loops=11,934)

  • Index Cond: (external_id = c.id)
  • Filter: ((managing_authority_id = 20) AND (reference_type_id = 1))
  • Rows Removed by Filter: 12
21. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_configuration_t on configuration_t config (cost=0.42..5.85 rows=1 width=16) (never executed)

  • Index Cond: (id = c.configuration_id)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_phase_t on phase_t p (cost=0.43..0.59 rows=1 width=24) (never executed)

  • Index Cond: (id = config.current_phase_id)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using geolocation__case_idx on geolocation_t g (cost=0.42..5.93 rows=1 width=24) (never executed)

  • Index Cond: (case_id = c.id)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using geolocation_localization_context_geolocation_id_idx on geolocation_localization_context_t glc (cost=0.43..0.62 rows=1 width=8) (never executed)

  • Index Cond: (g.id = geolocation_id)
  • Filter: ((context_type)::text = 'TARGET'::text)
25. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..8.43 rows=295 width=8) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Seq Scan on phase_definition_t pdt (cost=0.00..6.95 rows=295 width=8) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.85 rows=1 width=8) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Seq Scan on authority_abstract_t abstractau2_ (cost=0.00..1.85 rows=1 width=8) (never executed)

  • Filter: (id = 21)