explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gW5n

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 1,033.430 ↑ 1.0 1 1

Limit (cost=505,389.38..505,389.38 rows=1 width=359) (actual time=1,033.430..1,033.430 rows=1 loops=1)

  • ELSE NULL::integer END <> 4) OR ((indexedlok0_.mittestrassetyp)::text = 'BEZIRK'::text))) OR (((indexedlok0_.sonstgebietnamenorm1)::text ~~ 'altglienicke/%'::text) AND ((CASE WHEN (indexedlok0_2_.id IS NOT NULL) THEN 2 WHEN (indexedlok0_3_.id IS NOT NULL) THEN 4 WHEN (indexedlok0_4_.id IS NOT NULL) THEN 6 WHEN (indexedlok0_5_.idIS NOT NULL) THEN 7 WHEN (indexedlok0_.id IS NOT NULL) THEN 1 ELSE NULL::integer END <> 4) OR ((indexedlok0_.mittestrassetyp)::text = 'SONST'::text))) OR (((indexedlok0_.sonstgebietnamenorm1)::text ~~ '%/altglienicke/%'::text) AND ((CASE WHEN (indexedlok0_2_.id IS NOT NULL) THEN 2 WHEN (indexedlok0_3_.id IS NOT NULL) THEN 4 WHEN (indexedlok0_4_.id IS NOT NULL) THEN 6 WHEN (indexedlok0_5_.id IS NOT NULL) THEN 7 WHEN (indexedlok0_.id IS NOT NULL) THEN 1 ELSE NULL::integer END <> 4) OR ((indexedlok0_.mittestrassetyp)::text = 'SONST'::text))) OR ((((indexedlok0_.strassennamenorm1)::text ~~ 'altglienicke/%'::text) OR ((indexedlok0_.strassennamenorm1)::text ~~ '%/altglienicke/%'::text)) AND (CASE WHEN (indexedlok0_2_.id IS NOT NULL) THEN 2 WHEN (indexedlok0_3_.id IS NOT NULL) THEN 4 WHEN (indexedlok0_4_.id IS NOT NULL) THEN6 WHEN (indexedlok0_5_.id IS NOT NULL) THEN 7 WHEN (indexedlok0_.id IS NOT NULL) THEN 1 ELSE NULL::integer END <> 4)))
  • time=0.001..0.001 rows=0 loops=602898)
2. 0.072 1,033.429 ↑ 1.0 1 1

Sort (cost=505,389.38..505,389.38 rows=1 width=359) (actual time=1,033.429..1,033.429 rows=1 loops=1)

  • Sort Key: indexedlok0_.umland, indexedlok0_.discriminator, indexedlok0_.name, indexedlok0_.hauptadresse DESC, indexedlok0_.sonstigesmsgebietname, indexedlok0_.bezirknameklar, indexedlok0_.ortsteilnameklar, indexedlok0_.mittestrassetyp, indexedlok0_.id
  • Sort Method: quicksort Memory: 25kB
3. 0.029 1,033.357 ↑ 1.0 1 1

Nested Loop Left Join (cost=75.04..505,389.37 rows=1 width=359) (actual time=857.288..1,033.357 rows=1 loops=1)

  • Filter: ((((indexedlok0_.ortsteilnamenorm1)::text ~~ 'altglienicke/%'::text) AND ((CASE WHEN (indexedlok0_2_.id IS NOT NULL) THEN 2 WHEN (indexedlok0_3_.id IS NOT NULL) THEN 4 WHEN (indexedlok0_4_.id IS NOT NULL) THEN 6 WHEN (indexedlok0_5_.id IS NOT NULL) THEN 7 WHEN (indexedlok0_.id IS NOT NULL) THEN 1 ELSE NULL::integer END <> 4) OR ((indexedlok0_.mittestrassetyp)::text = 'ORTSTEIL'::text))) OR (((indexedlok0_.ortsteilnamenorm1)::text ~~ '%/altglienicke/%'::text) AND ((CASE WHEN (indexedlok0_2_.id IS NOT NULL) THEN 2 WHEN (indexedlok0_3_.id IS NOT NULL) THEN 4 WHEN (indexedlok0_4_.id IS NOT NULL) THEN 6 WHEN (indexedlok0_5_.id IS NOT NULL) THEN 7 WHEN (indexedlok0_.id IS NOT NULL) THEN 1 ELSE NULL::integer END <> 4) OR ((indexedlok0_.mittestrassetyp)::text = 'ORTSTEIL'::text))) OR (((indexedlok0_.bezirknamenorm1)::text ~~ 'altglienicke/%'::text) AND ((CASE WHEN (indexedlok0_2_.id IS NOT NULL) THEN 2 WHEN (indexedlok0_3_.id IS NOT NULL) THEN 4 WHEN (indexedlok0_4_.id IS NOT NULL) THEN 6 WHEN (indexedlok0_5_.id IS NOT NULL) THEN 7 WHEN (indexedlok0_.id IS NOT NULL) THEN 1 ELSE NULL::integer END <> 4) OR ((indexedlok0_.mittestrassetyp)::text = 'BEZIRK'::text))) OR (((indexedlok0_.bezirknamenorm1)::text ~~ '%/altglienicke/%'::text) AND ((CASE WHEN (indexedlok0_2_.id IS NOT NULL) THEN 2 WHEN (indexedlok0_3_.id IS NOT NULL) THEN 4 WHEN (indexedlok0_4_.id IS NOT NULL) THEN 6 WHEN (indexedlok0_5_.id IS NOT NULL) THEN 7 WHEN (indexedlok0_.id IS NOT NULL) THEN 1
  • Rows Removed by Filter: 2
4. 6.378 1,033.295 ↓ 3.0 3 1

Nested Loop Left Join (cost=74.75..505,381.00 rows=1 width=475) (actual time=803.865..1,033.295 rows=3 loops=1)

  • Join Filter: (indexedlok0_.id = indexedlok0_4_.id)
  • Rows Removed by Join Filter: 83595
5. 0.010 1,005.500 ↓ 3.0 3 1

Nested Loop Left Join (cost=74.75..501,688.03 rows=1 width=467) (actual time=789.307..1,005.500 rows=3 loops=1)

6. 0.678 1,005.424 ↓ 3.0 3 1

Nested Loop Left Join (cost=74.46..501,679.72 rows=1 width=459) (actual time=789.253..1,005.424 rows=3 loops=1)

  • Join Filter: (indexedlok0_.id = indexedlok0_2_.id)
  • Rows Removed by Join Filter: 9486
7. 728.697 1,003.141 ↓ 3.0 3 1

Nested Loop (cost=74.46..501,478.58 rows=1 width=451) (actual time=788.231..1,003.141 rows=3 loops=1)

  • -> Index Scan using fk_indexitaet_idid_hb2ao on ls_indexedlokalitaet indexedlok0_ (cost=0.42..17.13 rows=1 width=451) (actual
8. 154.698 274.444 ↓ 21.6 602,898 1

Hash Join (cost=74.04..22,399.30 rows=27,970 width=8) (actual time=0.567..274.444 rows=602,898 loops=1)

  • Hash Cond: (indexedlok0_1_.ortsteil_id = gebiet1_.id)
  • Index Cond: (id = indexedlok0_1_.id)
  • Filter: (suchbar AND (discriminator = ANY ('{1,0}'::integer[])) AND (((strassennamenorm1)::text ~~ 'kleinermohnweg/%'::text) OR ((strassennamenorm1)::text ~~ '%/kleinermohnweg/%'::text)) AND (((plzstring)::text ~~ '12524%'::text) OR ((plzstring)::text ~~ '% 12524%'::text)) AND (((ortsteilnamenorm1)::text ~~ 'altglienicke/%'::text) OR ((ortsteilnamenorm1)::text ~~ '%/altglienicke/%'::text) OR ((bezirknamenorm1)::text ~~ 'altglienicke/%'::text) OR ((bezirknamenorm1)::text ~~ '%/altglienicke/%'::text) OR ((sonstgebietnamenorm1)::text ~~ 'altglienicke/%'::text) OR ((sonstgebietnamenorm1)::text ~~ '%/altglienicke/%'::text) OR ((strassennamenorm1)::text ~~ 'altglienicke/%'::text) OR ((strassennamenorm1)::text ~~ '%/altglienicke/%'::text)) AND ((NOT zeitregelabh) OR (alternatives:SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 0
9. 119.206 119.206 ↓ 1.0 761,711 1

Seq Scan on ls_lokalitaet indexedlok0_1_ (cost=0.00..20,323.09 rows=761,709 width=16) (actual time=0.010..119.206 rows=761,711 loops=1)

10. 0.018 0.540 ↑ 1.0 116 1

Hash (cost=72.59..72.59 rows=116 width=8) (actual time=0.540..0.540 rows=116 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
11. 0.522 0.522 ↑ 1.0 116 1

Seq Scan on ls_gebiet gebiet1_ (cost=0.00..72.59 rows=116 width=8) (actual time=0.011..0.522 rows=116 loops=1)

  • Filter: (NOT umland)
  • Rows Removed by Filter: 3043
12.          

SubPlan (for Hash Join)

13. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_ls_stdaten_zeitregelid on ls_stammdatengueltigkeit lsstammdat2_ (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: (zeitregelid = 1100200561)
  • Filter: ((fachobjektid = indexedlok0_.id) AND ((fachklasse)::text = 'IndexedLokalitaet'::text))
14. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_ls_stdaten_zeitregelid on ls_stammdatengueltigkeit lsstammdat2__1 (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (zeitregelid = 1100200561)
  • Filter: ((fachklasse)::text = 'IndexedLokalitaet'::text)
15. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_ls_stdaten_zeitregelid on ls_stammdatengueltigkeit lsstammdat3_ (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: (zeitregelid = 1100300381)
  • Filter: ((fachobjektid = indexedlok0_.id) AND ((fachklasse)::text = 'IndexedLokalitaet'::text))
16. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_ls_stdaten_zeitregelid on ls_stammdatengueltigkeit lsstammdat3__1 (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (zeitregelid = 1100300381)
  • Filter: ((fachklasse)::text = 'IndexedLokalitaet'::text)
17. 1.605 1.605 ↑ 1.0 3,162 3

Seq Scan on ls_mitte_im_gebiet indexedlok0_2_ (cost=0.00..161.62 rows=3,162 width=8) (actual time=0.005..0.535 rows=3,162 loops=3)

18. 0.066 0.066 ↑ 1.0 1 3

Index Only Scan using fk_mitterasse_idid_79a07 on ls_mitte_der_strasse indexedlok0_3_ (cost=0.29..8.31 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=3)

  • Index Cond: (id = indexedlok0_.id)
  • Heap Fetches: 3
19. 21.417 21.417 ↑ 1.0 27,865 3

Seq Scan on ls_objektadresse indexedlok0_4_ (cost=0.00..3,344.65 rows=27,865 width=8) (actual time=0.004..7.139 rows=27,865 loops=3)

20. 0.033 0.033 ↓ 0.0 0 3

Index Only Scan using fk_kreuzuzung_idid_1bhfs on ls_kreuzung indexedlok0_5_ (cost=0.29..8.31 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=3)

  • Index Cond: (id = indexedlok0_.id)
  • Heap Fetches: 0