explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LmWt

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 33.909 ↑ 2.3 6 1

Unique (cost=13,271.68..13,272.13 rows=14 width=100) (actual time=33.884..33.909 rows=6 loops=1)

2. 0.106 33.886 ↓ 2.4 33 1

Sort (cost=13,271.68..13,271.71 rows=14 width=100) (actual time=33.884..33.886 rows=33 loops=1)

  • Sort Key: vogel.id, vogel.naam_lat, vogel.rights, vogel.id_species_type, vogel.id_taxo, vogel.sort_in_family, vogel.id_species, vogel.ind_diergroep, vogel.id_determination_method, vogel.euring, vogel.ndff, (COALESCE(s.name, vogel.naam_lat))
  • Sort Method: quicksort Memory: 29kB
3. 0.029 33.780 ↓ 2.4 33 1

Nested Loop Left Join (cost=13,056.62..13,271.41 rows=14 width=100) (actual time=33.001..33.780 rows=33 loops=1)

4. 0.008 33.553 ↓ 2.4 33 1

Nested Loop (cost=13,048.16..13,152.59 rows=14 width=76) (actual time=32.943..33.553 rows=33 loops=1)

5. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on language l (cost=0.00..1.61 rows=1 width=4) (actual time=0.016..0.020 rows=1 loops=1)

  • Filter: (id = 1)
  • Rows Removed by Filter: 48
6. 0.007 33.525 ↓ 2.4 33 1

Nested Loop (cost=13,048.16..13,150.83 rows=14 width=72) (actual time=32.924..33.525 rows=33 loops=1)

7. 0.096 33.419 ↓ 2.4 33 1

Nested Loop Left Join (cost=13,047.74..13,143.40 rows=14 width=80) (actual time=32.887..33.419 rows=33 loops=1)

  • Filter: (((species_name.id_language = 1) AND (upper((species_name.unaccent_name)::text) ~~ '%B%'::text)) OR (upper((vogel.naam_lat)::text) ~~ '%B%'::text))
  • Rows Removed by Filter: 75
8. 0.017 32.875 ↑ 1.8 8 1

Nested Loop (cost=13,047.31..13,131.26 rows=14 width=80) (actual time=32.830..32.875 rows=8 loops=1)

9. 0.020 32.810 ↑ 1.8 8 1

HashAggregate (cost=13,046.89..13,047.03 rows=14 width=12) (actual time=32.806..32.810 rows=8 loops=1)

  • Group Key: u0.id
10. 0.036 32.790 ↑ 1.8 8 1

Nested Loop (cost=11,420.04..13,046.85 rows=14 width=12) (actual time=27.378..32.790 rows=8 loops=1)

  • Join Filter: (u0.id = u3.id_species)
11. 9.952 32.604 ↑ 9.7 30 1

Hash Join (cost=11,419.75..12,932.21 rows=292 width=8) (actual time=6.947..32.604 rows=30 loops=1)

  • Hash Cond: (u1.id_species = u0.id)
12. 16.290 16.290 ↑ 1.0 55,878 1

Seq Scan on species_local_info u1 (cost=0.00..1,365.78 rows=55,878 width=4) (actual time=0.015..16.290 rows=55,878 loops=1)

  • Filter: (id IS NOT NULL)
13. 0.711 6.362 ↑ 1.2 3,631 1

Hash (cost=11,367.36..11,367.36 rows=4,191 width=4) (actual time=6.362..6.362 rows=3,631 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 192kB
14. 5.183 5.651 ↑ 1.2 3,631 1

Bitmap Heap Scan on vogel u0 (cost=107.92..11,367.36 rows=4,191 width=4) (actual time=0.564..5.651 rows=3,631 loops=1)

  • Recheck Cond: (id_taxo = 266)
  • Filter: (id_species_type = 'S'::"char")
  • Rows Removed by Filter: 1463
  • Heap Blocks: exact=777
15. 0.468 0.468 ↑ 1.1 5,094 1

Bitmap Index Scan on taxo (cost=0.00..106.87 rows=5,660 width=0) (actual time=0.468..0.468 rows=5,094 loops=1)

  • Index Cond: (id_taxo = 266)
16. 0.150 0.150 ↓ 0.0 0 30

Index Scan using species_id_idx on species_local_info u3 (cost=0.29..0.38 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=30)

  • Index Cond: (id_species = u1.id_species)
  • Filter: (id_rarity = ANY ('{0,1,2}'::integer[]))
  • Rows Removed by Filter: 1
17. 0.048 0.048 ↑ 1.0 1 8

Index Scan using vogel_pkey on vogel (cost=0.42..6.02 rows=1 width=68) (actual time=0.006..0.006 rows=1 loops=8)

  • Index Cond: (id = u0.id)
18. 0.448 0.448 ↓ 1.4 14 8

Index Scan using species_name_lang_species on species_name (cost=0.42..0.64 rows=10 width=29) (actual time=0.013..0.056 rows=14 loops=8)

  • Index Cond: (vogel.id = id_species)
19. 0.099 0.099 ↑ 1.0 1 33

Index Only Scan using vogel_pkey on vogel v (cost=0.42..0.53 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=33)

  • Index Cond: (id = vogel.id)
  • Heap Fetches: 33
20. 0.000 0.198 ↑ 1.0 1 33

Subquery Scan on s (cost=8.46..8.48 rows=1 width=30) (actual time=0.006..0.006 rows=1 loops=33)

  • Filter: ((s.language_id = 1) AND (l.id = s.language_id) AND (v.id = s.species_id))
21. 0.033 0.198 ↑ 1.0 1 33

Limit (cost=8.46..8.46 rows=1 width=34) (actual time=0.005..0.006 rows=1 loops=33)

22. 0.066 0.165 ↑ 1.0 1 33

Sort (cost=8.46..8.46 rows=1 width=34) (actual time=0.005..0.005 rows=1 loops=33)

  • Sort Key: species_name_1.pref_order DESC
  • Sort Method: quicksort Memory: 25kB
23. 0.099 0.099 ↑ 1.0 1 33

Index Scan using species_name_language_unique on species_name species_name_1 (cost=0.42..8.45 rows=1 width=34) (actual time=0.003..0.003 rows=1 loops=33)

  • Index Cond: ((id_species = v.id) AND (id_language = l.id))
Planning time : 4.094 ms
Execution time : 34.259 ms