explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uYpw

Settings
# exclusive inclusive rows x rows loops node
1. 0.376 11,288.890 ↑ 1.0 1 1

Aggregate (cost=590,406.33..590,406.34 rows=1 width=8) (actual time=11,288.890..11,288.890 rows=1 loops=1)

2. 0.135 11,288.514 ↑ 2.1 303 1

Nested Loop (cost=323,083.83..590,404.72 rows=645 width=8) (actual time=6,161.360..11,288.514 rows=303 loops=1)

3. 0.026 0.026 ↑ 1.0 1 1

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

  • Filter: (id = 20)
  • Rows Removed by Filter: 67
4. 0.702 11,288.353 ↑ 2.1 303 1

Nested Loop (cost=323,083.83..590,396.42 rows=645 width=16) (actual time=6,161.339..11,288.353 rows=303 loops=1)

5. 0.878 10,991.014 ↑ 2.5 303 1

Hash Join (cost=323,083.41..589,895.80 rows=746 width=8) (actual time=6,160.980..10,991.014 rows=303 loops=1)

  • Hash Cond: (p.definition_id = pdt.id)
6. 0.864 10,989.995 ↑ 2.5 303 1

Nested Loop Left Join (cost=323,072.84..589,874.98 rows=746 width=16) (actual time=6,160.821..10,989.995 rows=303 loops=1)

7. 3,351.882 10,362.224 ↑ 2.5 303 1

Hash Right Join (cost=323,072.41..589,418.80 rows=746 width=24) (actual time=6,160.792..10,362.224 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. 893.784 893.784 ↑ 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.028..893.784 rows=2,549,886 loops=1)

9. 123.059 6,116.558 ↑ 1.1 438,444 1

Hash (cost=314,056.27..314,056.27 rows=466,251 width=32) (actual time=6,116.558..6,116.558 rows=438,444 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2385kB
10. 982.720 5,993.499 ↑ 1.1 438,444 1

Hash Join (cost=241,254.60..314,056.27 rows=466,251 width=32) (actual time=4,627.713..5,993.499 rows=438,444 loops=1)

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

12. 137.845 4,625.837 ↑ 1.1 438,444 1

Hash (cost=232,238.46..232,238.46 rows=466,251 width=32) (actual time=4,625.837..4,625.837 rows=438,444 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2228kB
13. 459.492 4,487.992 ↑ 1.1 438,444 1

Hash Right Join (cost=186,399.37..232,238.46 rows=466,251 width=32) (actual time=3,736.275..4,487.992 rows=438,444 loops=1)

  • Hash Cond: (gac_desc.geolocation_id = g.id)
14. 293.926 293.926 ↓ 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.027..293.926 rows=691,682 loops=1)

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

Hash (cost=177,383.24..177,383.24 rows=466,251 width=32) (actual time=3,734.573..3,734.574 rows=438,444 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2224kB
16. 236.703 3,578.578 ↑ 1.1 438,444 1

Hash Right Join (cost=132,234.74..177,383.24 rows=466,251 width=32) (actual time=3,131.608..3,578.578 rows=438,444 loops=1)

  • Hash Cond: (gac_norm.geolocation_id = g.id)
17. 346.963 346.963 ↑ 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=37.297..346.963 rows=18,057 loops=1)

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

Hash (cost=123,218.61..123,218.61 rows=466,251 width=32) (actual time=2,994.912..2,994.912 rows=438,444 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2224kB
19. 735.378 2,843.304 ↑ 1.1 438,444 1

Hash Right Join (cost=85,101.47..123,218.61 rows=466,251 width=32) (actual time=1,237.160..2,843.304 rows=438,444 loops=1)

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

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

21. 123.858 1,234.933 ↓ 1.0 438,444 1

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

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

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

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

24. 139.113 471.509 ↓ 1.0 438,444 1

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

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

Seq Scan on case_t c (cost=0.00..38,096.62 rows=437,754 width=40) (actual time=2.458..332.396 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. 626.907 626.907 ↑ 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=2.067..2.069 rows=1 loops=303)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
28. 0.086 0.086 ↓ 1.0 295 1

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

29. 296.637 296.637 ↑ 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.978..0.979 rows=1 loops=303)

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