explain.depesz.com

PostgreSQL's explain analyze made readable

Result: teub

Settings
# exclusive inclusive rows x rows loops node
1. 0.299 12,911.999 ↑ 1.0 1 1

Aggregate (cost=847,718.28..847,718.29 rows=1 width=8) (actual time=12,911.999..12,911.999 rows=1 loops=1)

2. 0.120 12,911.700 ↑ 2.1 303 1

Nested Loop (cost=571,212.14..847,716.67 rows=645 width=8) (actual time=9,075.485..12,911.700 rows=303 loops=1)

3. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on authority_abstract_t abstractau2_ (cost=0.00..1.85 rows=1 width=8) (actual time=0.008..0.014 rows=1 loops=1)

  • Filter: (id = 20)
  • Rows Removed by Filter: 67
4. 0.286 12,911.566 ↑ 2.1 303 1

Nested Loop (cost=571,212.14..847,708.37 rows=645 width=16) (actual time=9,075.477..12,911.566 rows=303 loops=1)

5. 0.479 12,908.553 ↑ 2.5 303 1

Hash Join (cost=571,211.71..847,207.76 rows=746 width=8) (actual time=9,075.465..12,908.553 rows=303 loops=1)

  • Hash Cond: (p.definition_id = pdt.id)
6. 0.767 12,908.009 ↑ 2.5 303 1

Nested Loop Left Join (cost=571,201.14..847,186.93 rows=746 width=16) (actual time=9,075.379..12,908.009 rows=303 loops=1)

7. 2,702.126 12,904.212 ↑ 2.5 303 1

Hash Left Join (cost=571,200.71..846,730.75 rows=746 width=24) (actual time=9,075.368..12,904.212 rows=303 loops=1)

  • Hash Cond: (COALESCE(gac_desc.address_id, gac_norm.address_id) = ad.id)
  • Filter: (f_unaccent(upper(concat(ad.street_name_french, ' ', ad.street_number_from, NULLIF((' - '::text || (ad.street_number_to)::text), ' - '::text), NULLIF((' / '::text || (ad.box_number)::text), ' / '::text)))) ~~ '%ROUGE%'::text)
  • Rows Removed by Filter: 438141
8. 393.727 8,676.670 ↑ 1.1 438,444 1

Hash Left Join (cost=430,951.58..549,464.90 rows=466,251 width=40) (actual time=6,336.509..8,676.670 rows=438,444 loops=1)

  • Hash Cond: (c.id = board.case_id)
9. 758.147 8,070.559 ↑ 1.1 438,444 1

Hash Left Join (cost=411,591.14..515,121.83 rows=466,251 width=40) (actual time=6,121.963..8,070.559 rows=438,444 loops=1)

  • Hash Cond: (p.deadline_id = d1.id)
10. 845.800 5,173.655 ↑ 1.1 438,444 1

Hash Join (cost=240,487.60..313,744.27 rows=466,251 width=48) (actual time=3,982.593..5,173.655 rows=438,444 loops=1)

  • Hash Cond: (p.id = config.current_phase_id)
11. 346.339 346.339 ↓ 1.0 1,831,964 1

Seq Scan on phase_t p (cost=0.00..36,611.39 rows=1,831,939 width=24) (actual time=0.022..346.339 rows=1,831,964 loops=1)

12. 135.897 3,981.516 ↑ 1.1 438,444 1

Hash (cost=231,016.46..231,016.46 rows=466,251 width=40) (actual time=3,981.516..3,981.516 rows=438,444 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2228kB
13. 513.732 3,845.619 ↑ 1.1 438,444 1

Hash Right Join (cost=184,547.37..231,016.46 rows=466,251 width=40) (actual time=3,010.121..3,845.619 rows=438,444 loops=1)

  • Hash Cond: (gac_desc.geolocation_id = g.id)
14. 325.666 325.666 ↓ 2.1 691,682 1

Seq Scan on geolocation_address_context_t gac_desc (cost=0.00..36,957.62 rows=322,385 width=16) (actual time=0.021..325.666 rows=691,682 loops=1)

  • Filter: (is_principal AND ((context_type)::text = 'DESCRIPTIVE_TARGET'::text))
  • Rows Removed by Filter: 830688
15. 118.716 3,006.221 ↑ 1.1 438,444 1

Hash (cost=175,531.24..175,531.24 rows=466,251 width=32) (actual time=3,006.221..3,006.221 rows=438,444 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2015kB
16. 193.797 2,887.505 ↑ 1.1 438,444 1

Hash Right Join (cost=130,284.74..175,531.24 rows=466,251 width=32) (actual time=2,617.217..2,887.505 rows=438,444 loops=1)

  • Hash Cond: (gac_norm.geolocation_id = g.id)
17. 223.429 223.429 ↑ 15.7 18,057 1

Seq Scan on geolocation_address_context_t gac_norm (cost=0.00..36,957.62 rows=283,314 width=16) (actual time=39.416..223.429 rows=18,057 loops=1)

  • Filter: (is_principal AND ((context_type)::text = 'TARGET'::text))
  • Rows Removed by Filter: 1504313
18. 130.444 2,470.279 ↑ 1.1 438,444 1

Hash (cost=121,724.61..121,724.61 rows=466,251 width=24) (actual time=2,470.279..2,470.279 rows=438,444 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3522kB
19. 555.611 2,339.835 ↑ 1.1 438,444 1

Hash Right Join (cost=85,101.47..121,724.61 rows=466,251 width=24) (actual time=1,629.438..2,339.835 rows=438,444 loops=1)

  • Hash Cond: (g.case_id = c.id)
20. 155.980 155.980 ↑ 1.1 718,328 1

Seq Scan on geolocation_t g (cost=0.00..19,764.34 rows=765,134 width=16) (actual time=0.023..155.980 rows=718,328 loops=1)

21. 153.961 1,628.244 ↓ 1.0 438,444 1

Hash (cost=77,491.55..77,491.55 rows=437,754 width=16) (actual time=1,628.244..1,628.244 rows=438,444 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3601kB
22. 663.508 1,474.283 ↓ 1.0 438,444 1

Hash Join (cost=46,988.55..77,491.55 rows=437,754 width=16) (actual time=668.514..1,474.283 rows=438,444 loops=1)

  • Hash Cond: (config.id = c.configuration_id)
23. 142.759 142.759 ↑ 1.0 660,885 1

Seq Scan on configuration_t config (cost=0.00..14,120.65 rows=660,965 width=16) (actual time=0.008..142.759 rows=660,885 loops=1)

24. 309.874 668.016 ↓ 1.0 438,444 1

Hash (cost=38,096.62..38,096.62 rows=437,754 width=40) (actual time=668.016..668.016 rows=438,444 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2438kB
25. 358.142 358.142 ↓ 1.0 438,444 1

Seq Scan on case_t c (cost=0.00..38,096.62 rows=437,754 width=40) (actual time=0.080..358.142 rows=438,444 loops=1)

  • Filter: ((NOT deleted) AND (NOT a_new_version_exists) AND ((case_type)::text = 'URBANISM'::text))
  • Rows Removed by Filter: 279885
26. 1,293.598 2,138.757 ↑ 1.0 4,598,767 1

Hash (cost=95,617.46..95,617.46 rows=4,601,046 width=8) (actual time=2,138.757..2,138.757 rows=4,598,767 loops=1)

  • Buckets: 131072 Batches: 64 Memory Usage: 3836kB
27. 845.159 845.159 ↑ 1.0 4,598,767 1

Seq Scan on deadline_acm_t d1 (cost=0.00..95,617.46 rows=4,601,046 width=8) (actual time=0.035..845.159 rows=4,598,767 loops=1)

28. 108.596 212.384 ↓ 1.0 522,960 1

Hash (cost=10,269.53..10,269.53 rows=522,953 width=16) (actual time=212.384..212.384 rows=522,960 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 2379kB
29. 103.788 103.788 ↓ 1.0 522,960 1

Seq Scan on case_status_board_t board (cost=0.00..10,269.53 rows=522,953 width=16) (actual time=0.013..103.788 rows=522,960 loops=1)

30. 783.000 1,525.416 ↑ 1.0 2,549,886 1

Hash (cost=90,943.17..90,943.17 rows=2,549,917 width=29) (actual time=1,525.416..1,525.416 rows=2,549,886 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2877kB
31. 742.416 742.416 ↑ 1.0 2,549,886 1

Seq Scan on address_t ad (cost=0.00..90,943.17 rows=2,549,917 width=29) (actual time=0.014..742.416 rows=2,549,886 loops=1)

32. 3.030 3.030 ↑ 1.0 1 303

Index Scan using geolocation_localization_context_geolocation_id_idx on geolocation_localization_context_t glc (cost=0.43..0.60 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=303)

  • Index Cond: (g.id = geolocation_id)
  • Filter: ((context_type)::text = 'TARGET'::text)
  • Rows Removed by Filter: 1
33. 0.031 0.065 ↓ 1.0 295 1

Hash (cost=6.92..6.92 rows=292 width=8) (actual time=0.065..0.065 rows=295 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
34. 0.034 0.034 ↓ 1.0 295 1

Seq Scan on phase_definition_t pdt (cost=0.00..6.92 rows=292 width=8) (actual time=0.005..0.034 rows=295 loops=1)

35. 2.727 2.727 ↑ 1.0 1 303

Index Only Scan using pk_case_actor_authority on case_actor_authority_t actors1_ (cost=0.43..0.66 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=303)

  • Index Cond: ((case_id = c.id) AND (authority_abstract_id = 20))
  • Heap Fetches: 303
Planning time : 25.393 ms