explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MYxz

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 49,597.888 ↓ 23.0 23 1

Limit (cost=2,529,451.29..2,529,451.32 rows=1 width=150) (actual time=49,597.870..49,597.888 rows=23 loops=1)

2. 0.015 49,597.877 ↓ 23.0 23 1

Unique (cost=2,529,451.29..2,529,451.32 rows=1 width=150) (actual time=49,597.861..49,597.877 rows=23 loops=1)

3. 0.152 49,597.862 ↓ 23.0 23 1

Sort (cost=2,529,451.29..2,529,451.29 rows=1 width=150) (actual time=49,597.860..49,597.862 rows=23 loops=1)

  • Sort Key: (min(waarneming.datum)) DESC, family.sort_id, vogel.sort_in_family, vogel.naam_lat, vogel.id, (count(waarneming.id)), (max(waarneming.id)), (max(waarneming.aantal)), (sum(waarneming.aantal)), (array_agg(DISTINCT waarneming.id_gebied)), (array_agg(DISTINCT waarneming.id_user)), species_name.unaccent_name
  • Sort Method: quicksort Memory: 31kB
4. 0.287 49,597.710 ↓ 23.0 23 1

GroupAggregate (cost=2,529,451.23..2,529,451.28 rows=1 width=150) (actual time=49,597.590..49,597.710 rows=23 loops=1)

  • Group Key: vogel.id, species_name.unaccent_name, family.sort_id
5. 0.044 49,597.423 ↓ 23.0 23 1

Sort (cost=2,529,451.23..2,529,451.24 rows=1 width=78) (actual time=49,597.420..49,597.423 rows=23 loops=1)

  • Sort Key: vogel.id, species_name.unaccent_name, family.sort_id
  • Sort Method: quicksort Memory: 28kB
6. 97.195 49,597.379 ↓ 23.0 23 1

Hash Semi Join (cost=141,392.92..2,529,451.22 rows=1 width=78) (actual time=17,861.424..49,597.379 rows=23 loops=1)

  • Hash Cond: (waarneming.id = u0.id)
7. 127.712 49,497.545 ↓ 20.5 664,749 1

Gather (cost=137,956.66..2,525,929.82 rows=32,431 width=78) (actual time=4,160.982..49,497.545 rows=664,749 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 3,657.464 49,369.833 ↓ 16.4 221,583 3

Hash Join (cost=136,956.66..2,521,686.72 rows=13,513 width=78) (actual time=4,132.548..49,369.833 rows=221,583 loops=3)

  • Hash Cond: (waarneming.id_vogel = vogel.id)
9. 41,580.537 41,580.537 ↑ 1.2 18,681,984 3

Parallel Seq Scan on waarneming (cost=0.00..2,297,134.77 rows=23,322,677 width=28) (actual time=0.188..41,580.537 rows=18,681,984 loops=3)

10. 0.157 4,131.832 ↑ 4.1 114 3

Hash (cost=136,950.88..136,950.88 rows=462 width=54) (actual time=4,131.832..4,131.832 rows=114 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
11. 748.644 4,131.675 ↑ 4.1 114 3

Hash Left Join (cost=76,452.14..136,950.88 rows=462 width=54) (actual time=1,439.037..4,131.675 rows=114 loops=3)

  • Hash Cond: (vogel.id_taxo = family.id)
  • Filter: ((upper((vogel.naam_lat)::text) ~~ '%AKKER%'::text) OR (upper((family.oms_en)::text) ~~ '%AKKER%'::text) OR (upper((species_name.unaccent_name)::text) ~~ '%AKKER%'::text))
  • Rows Removed by Filter: 846738
12. 1,084.593 3,377.038 ↓ 8.8 846,852 3

Hash Right Join (cost=76,058.49..133,896.78 rows=96,313 width=54) (actual time=1,290.720..3,377.038 rows=846,852 loops=3)

  • Hash Cond: (t5.id_species = vogel.id)
  • Filter: ((species_name.id_language = 1) OR (species_name.id_language IS NULL))
  • Rows Removed by Filter: 469400
13. 790.016 1,556.993 ↓ 1.5 1,190,980 3

Hash Right Join (cost=36,473.24..65,146.47 rows=796,633 width=29) (actual time=553.511..1,556.993 rows=1,190,980 loops=3)

  • Hash Cond: (species_name.id_species = t5.id)
14. 214.683 214.683 ↑ 1.0 538,132 3

Seq Scan on species_name (cost=0.00..11,409.32 rows=538,132 width=29) (actual time=0.066..214.683 rows=538,132 loops=3)

15. 235.717 552.294 ↑ 1.0 796,581 3

Hash (cost=23,403.33..23,403.33 rows=796,633 width=8) (actual time=552.294..552.294 rows=796,581 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 2980kB
16. 316.577 316.577 ↑ 1.0 796,581 3

Seq Scan on vogel t5 (cost=0.00..23,403.33 rows=796,633 width=8) (actual time=0.009..316.577 rows=796,581 loops=3)

17. 365.356 735.452 ↑ 1.0 796,581 3

Hash (cost=23,403.33..23,403.33 rows=796,633 width=33) (actual time=735.452..735.452 rows=796,581 loops=3)

  • Buckets: 65536 Batches: 16 Memory Usage: 3767kB
18. 370.096 370.096 ↑ 1.0 796,581 3

Seq Scan on vogel (cost=0.00..23,403.33 rows=796,633 width=33) (actual time=0.030..370.096 rows=796,581 loops=3)

19. 2.863 5.993 ↑ 1.0 11,629 3

Hash (cost=248.29..248.29 rows=11,629 width=22) (actual time=5.993..5.993 rows=11,629 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 748kB
20. 3.130 3.130 ↑ 1.0 11,629 3

Seq Scan on family (cost=0.00..248.29 rows=11,629 width=22) (actual time=0.046..3.130 rows=11,629 loops=3)

21. 0.044 2.639 ↑ 1.0 144 1

Hash (cost=3,434.47..3,434.47 rows=144 width=8) (actual time=2.639..2.639 rows=144 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
22. 0.027 2.595 ↑ 1.0 144 1

Nested Loop Left Join (cost=9.87..3,434.47 rows=144 width=8) (actual time=0.204..2.595 rows=144 loops=1)

23. 0.101 1.416 ↑ 1.0 144 1

Nested Loop (cost=1.42..2,212.27 rows=144 width=16) (actual time=0.149..1.416 rows=144 loops=1)

24. 0.144 1.315 ↑ 1.0 144 1

Nested Loop (cost=1.42..2,208.85 rows=144 width=12) (actual time=0.129..1.315 rows=144 loops=1)

  • Join Filter: (u0.id_vogel = v.id)
25. 0.029 1.027 ↑ 1.0 144 1

Nested Loop (cost=0.99..1,639.45 rows=144 width=16) (actual time=0.117..1.027 rows=144 loops=1)

26. 0.566 0.566 ↑ 1.0 144 1

Index Scan using waarneming_pkey on waarneming u0 (cost=0.56..1,011.73 rows=144 width=12) (actual time=0.053..0.566 rows=144 loops=1)

  • Index Cond: (id = ANY ('{158424456,158330463,158330467,158330481,158330494,158330495,158330499,158330537,158424441,158330531,158330532,158424453,158424440,158330500,158424442,158330487,158330526,158424435,158330557,158330561,158424436,158330533,158424416,158330504,158424424,158330491,158330497,158330508,158424443,158330549,158424411,158330556,158424454,158330501,158330505,158330517,158424446,158330547,158424455,158330466,158330480,158330544,158330546,158424419,158330498,158424427,158330511,158424423,158330482,158330529,158424417,158330469,158330472,158330485,158330486,158330490,158330496,158330518,158330528,158330541,158330548,158330550,158424448,158330506,158330558,158424452,158330523,158424449,158330514,158330554,158424445,158330530,158330551,158424451,158330555,158330479,158330492,158424439,158330488,158330502,158424426,158330553,158424438,158330527,158330559,158330476,158330489,158330507,158330520,158330540,158424450,158330543,158424412,158330552,158424418,158330477,158330478,158330483,158424434,158330535,158424414,158330510,158330516,158424444,158330484,158330509,158424428,158330522,158424447,158330519,158330539,158330542,158424433,158330534,158424415,158330536,158330562,158330521,158424421,158330524,158424420,158330503,158424425,158330468,158424431,158424432,158330474,158424437,158330515,158424422,158330473,158424429,158330538,158424413,158330470,158330471,158330493,158330512,158424430,158330464,158330465,158330513,158330545,158330560}'::bigint[]))
27. 0.432 0.432 ↑ 1.0 1 144

Index Only Scan using vogel_pkey on vogel u1 (cost=0.42..4.36 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=144)

  • Index Cond: (id = u0.id_vogel)
  • Heap Fetches: 0
28. 0.144 0.144 ↑ 1.0 1 144

Index Only Scan using vogel_pkey on vogel v (cost=0.42..3.94 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=144)

  • Index Cond: (id = u1.id)
  • Heap Fetches: 0
29. 0.000 0.000 ↑ 1.0 1 144

Materialize (cost=0.00..1.62 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=144)

30. 0.013 0.013 ↑ 1.0 1 1

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 48
31. 0.144 1.152 ↑ 1.0 1 144

Subquery Scan on s (cost=8.46..8.48 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=144)

  • Filter: ((s.language_id = 1) AND (l.id = s.language_id) AND (v.id = s.species_id))
32. 0.000 1.008 ↑ 1.0 1 144

Limit (cost=8.46..8.46 rows=1 width=44) (actual time=0.007..0.007 rows=1 loops=144)

33. 0.288 1.008 ↑ 1.0 1 144

Sort (cost=8.46..8.46 rows=1 width=44) (actual time=0.007..0.007 rows=1 loops=144)

  • Sort Key: species_name_1.pref_order DESC
  • Sort Method: quicksort Memory: 25kB
34. 0.720 0.720 ↑ 1.0 1 144

Index Scan using species_name_language_unique on species_name species_name_1 (cost=0.42..8.45 rows=1 width=44) (actual time=0.005..0.005 rows=1 loops=144)

  • Index Cond: ((id_species = v.id) AND (id_language = l.id))
Planning time : 8.215 ms
Execution time : 49,606.470 ms