explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gDHV

Settings
# exclusive inclusive rows x rows loops node
1. 0.369 10,218.602 ↑ 1.0 1 1

Aggregate (cost=577,336.44..577,336.45 rows=1 width=8) (actual time=10,218.602..10,218.602 rows=1 loops=1)

2. 0.146 10,218.233 ↑ 2.0 303 1

Nested Loop (cost=318,074.38..577,334.93 rows=606 width=8) (actual time=5,456.247..10,218.233 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.688 10,218.073 ↑ 2.0 303 1

Nested Loop (cost=318,074.38..577,327.02 rows=606 width=16) (actual time=5,456.238..10,218.073 rows=303 loops=1)

5. 0.698 10,047.099 ↑ 2.3 303 1

Hash Join (cost=318,073.95..576,857.27 rows=700 width=8) (actual time=5,455.943..10,047.099 rows=303 loops=1)

  • Hash Cond: (p.definition_id = pdt.id)
6. 0.716 10,046.335 ↑ 2.3 303 1

Nested Loop Left Join (cost=318,063.38..576,837.08 rows=700 width=16) (actual time=5,455.867..10,046.335 rows=303 loops=1)

7. 3,470.381 9,672.323 ↑ 2.3 303 1

Hash Right Join (cost=318,062.96..576,403.83 rows=700 width=24) (actual time=5,455.843..9,672.323 rows=303 loops=1)

  • Hash Cond: (ad.id = g.target_principal_address_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. 793.314 793.314 ↑ 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.025..793.314 rows=2,549,886 loops=1)

9. 148.699 5,408.628 ↓ 1.0 438,444 1

Hash (cost=309,598.03..309,598.03 rows=437,754 width=32) (actual time=5,408.628..5,408.628 rows=438,444 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 16 (originally 8) Memory Usage: 3585kB
10. 1,019.718 5,259.929 ↓ 1.0 438,444 1

Hash Join (cost=237,276.33..309,598.03 rows=437,754 width=32) (actual time=3,815.575..5,259.929 rows=438,444 loops=1)

  • Hash Cond: (p.id = config.current_phase_id)
11. 425.522 425.522 ↓ 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.023..425.522 rows=1,831,964 loops=1)

12. 231.027 3,814.689 ↓ 1.0 438,444 1

Hash (cost=228,811.40..228,811.40 rows=437,754 width=32) (actual time=3,814.689..3,814.689 rows=438,444 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3935kB
13. 496.742 3,583.662 ↓ 1.0 438,444 1

Hash Right Join (cost=183,167.20..228,811.40 rows=437,754 width=32) (actual time=2,744.537..3,583.662 rows=438,444 loops=1)

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

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

  • Filter: (is_principal AND ((context_type)::text = 'DESCRIPTIVE_TARGET'::text))
  • Rows Removed by Filter: 830688
15. 194.077 2,743.693 ↓ 1.0 438,444 1

Hash (cost=174,702.27..174,702.27 rows=437,754 width=32) (actual time=2,743.693..2,743.693 rows=438,444 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3949kB
16. 272.245 2,549.616 ↓ 1.0 438,444 1

Hash Right Join (cost=129,748.69..174,702.27 rows=437,754 width=32) (actual time=2,127.292..2,549.616 rows=438,444 loops=1)

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

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

  • Filter: (is_principal AND ((context_type)::text = 'TARGET'::text))
  • Rows Removed by Filter: 1504313
18. 143.921 1,871.534 ↓ 1.0 438,444 1

Hash (cost=121,283.76..121,283.76 rows=437,754 width=32) (actual time=1,871.534..1,871.534 rows=438,444 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3949kB
19. 487.358 1,727.613 ↓ 1.0 438,444 1

Hash Right Join (cost=85,101.47..121,283.76 rows=437,754 width=32) (actual time=1,086.949..1,727.613 rows=438,444 loops=1)

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

Seq Scan on geolocation_t g (cost=0.00..18,555.28 rows=718,328 width=24) (actual time=0.012..154.124 rows=718,328 loops=1)

21. 103.129 1,086.131 ↓ 1.0 438,444 1

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

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

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

  • Hash Cond: (config.id = c.configuration_id)
23. 118.583 118.583 ↑ 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.007..118.583 rows=660,885 loops=1)

24. 151.417 480.134 ↓ 1.0 438,444 1

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

  • Buckets: 65536 Batches: 16 Memory Usage: 2438kB
25. 328.717 328.717 ↓ 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.052..328.717 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. 373.296 373.296 ↑ 1.0 1 303

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

  • Index Cond: (g.id = geolocation_id)
  • Filter: ((context_type)::text = 'TARGET'::text)
  • Rows Removed by Filter: 1
27. 0.030 0.066 ↓ 1.0 295 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
28. 0.036 0.036 ↓ 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.036 rows=295 loops=1)

29. 170.286 170.286 ↑ 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.561..0.562 rows=1 loops=303)

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