explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ib4Z

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 53,263.933 ↓ 8.5 17 1

Limit (cost=2,556,865.13..2,556,865.20 rows=2 width=150) (actual time=53,263.906..53,263.933 rows=17 loops=1)

2. 0.016 53,263.929 ↓ 8.5 17 1

Unique (cost=2,556,865.13..2,556,865.20 rows=2 width=150) (actual time=53,263.904..53,263.929 rows=17 loops=1)

3. 0.137 53,263.913 ↓ 8.5 17 1

Sort (cost=2,556,865.13..2,556,865.14 rows=2 width=150) (actual time=53,263.904..53,263.913 rows=17 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: 29kB
4. 0.143 53,263.776 ↓ 8.5 17 1

GroupAggregate (cost=2,556,865.04..2,556,865.12 rows=2 width=150) (actual time=53,263.710..53,263.776 rows=17 loops=1)

  • Group Key: vogel.id, species_name.unaccent_name, family.sort_id
5. 0.053 53,263.633 ↓ 14.0 28 1

Sort (cost=2,556,865.04..2,556,865.04 rows=2 width=78) (actual time=53,263.630..53,263.633 rows=28 loops=1)

  • Sort Key: vogel.id, species_name.unaccent_name, family.sort_id
  • Sort Method: quicksort Memory: 28kB
6. 1,042.079 53,263.580 ↓ 14.0 28 1

Hash Semi Join (cost=155,063.95..2,556,865.03 rows=2 width=78) (actual time=10,144.844..53,263.580 rows=28 loops=1)

  • Hash Cond: (waarneming.id = u0.id)
7. 992.458 52,208.830 ↓ 54.0 8,696,756 1

Gather (cost=137,979.55..2,539,357.66 rows=161,120 width=78) (actual time=4,339.187..52,208.830 rows=8,696,756 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 4,675.051 51,216.372 ↓ 43.2 2,898,919 3

Hash Join (cost=136,979.55..2,522,245.66 rows=67,133 width=78) (actual time=4,301.880..51,216.372 rows=2,898,919 loops=3)

  • Hash Cond: (waarneming.id_vogel = vogel.id)
9. 42,240.548 42,240.548 ↑ 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.603..42,240.548 rows=18,681,984 loops=3)

10. 1.443 4,300.773 ↑ 1.4 1,637 3

Hash (cost=136,950.88..136,950.88 rows=2,293 width=54) (actual time=4,300.773..4,300.773 rows=1,637 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 160kB
11. 667.035 4,299.330 ↑ 1.4 1,637 3

Hash Left Join (cost=76,452.14..136,950.88 rows=2,293 width=54) (actual time=1,418.176..4,299.330 rows=1,637 loops=3)

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

Hash Right Join (cost=76,058.49..133,896.78 rows=96,313 width=54) (actual time=1,399.619..3,625.767 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. 901.638 1,720.960 ↓ 1.5 1,190,980 3

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

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

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

15. 278.430 624.165 ↑ 1.0 796,581 3

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

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

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

17. 398.584 772.462 ↑ 1.0 796,581 3

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

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

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

19. 3.235 6.528 ↑ 1.0 11,629 3

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

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

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

21. 0.217 12.671 ↑ 1.0 726 1

Hash (cost=17,075.33..17,075.33 rows=726 width=8) (actual time=12.671..12.671 rows=726 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
22. 0.596 12.454 ↑ 1.0 726 1

Nested Loop Left Join (cost=9.87..17,075.33 rows=726 width=8) (actual time=0.182..12.454 rows=726 loops=1)

23. 0.421 7.502 ↑ 1.0 726 1

Nested Loop (cost=1.42..10,913.40 rows=726 width=16) (actual time=0.148..7.502 rows=726 loops=1)

24. 0.382 7.081 ↑ 1.0 726 1

Nested Loop (cost=1.42..10,902.71 rows=726 width=12) (actual time=0.130..7.081 rows=726 loops=1)

  • Join Filter: (u0.id_vogel = v.id)
25. 0.293 5.973 ↑ 1.0 726 1

Nested Loop (cost=0.99..8,031.98 rows=726 width=16) (actual time=0.119..5.973 rows=726 loops=1)

26. 4.228 4.228 ↑ 1.0 726 1

Index Scan using waarneming_pkey on waarneming u0 (cost=0.56..5,090.72 rows=726 width=12) (actual time=0.060..4.228 rows=726 loops=1)

  • Index Cond: (id = ANY ('{133240104,133240352,133251154,133348497,133348498,133608904,136533483,137024223,137024224,137024225,137024226,137024227,137024228,137081691,137431437,137440842,137440846,137440856,137440916,137440925,137440927,137440942,137440947,137441378,137441383,137441399,137441403,137573140,137573141,137573146,137573148,137573149,137573151,137573152,137573154,137573156,137573159,137573160,137573162,137573165,137573166,137573167,137573168,137573170,137573172,137573173,137573174,137573175,137573178,137573180,137573181,137573182,137573183,137573184,137573185,137573186,137573189,137573191,137573192,137573194,137573199,137573200,137573201,137573202,137573203,137573204,137573205,137573206,137573208,137573210,137573211,137573214,137575151,137575152,137575153,137575154,137575155,137575156,137575397,137575398,137575400,137575401,137575402,137575403,137575404,137575405,137575406,137575407,137575408,137575409,137575410,137575411,137575412,137575413,137575414,137575415,137575416,137575417,137575418,137575419,137575420,137644402,137644432,137644493,137644502,137644510,137644519,137646215,137646225,137646420,137646478,137646479,137646480,137646510,137646521,137646526,137646532,137646541,137739624,137739630,138157927,138158001,138158002,138158009,138158012,138158014,138158017,138158020,138517199,138517203,138517212,138517213,138517215,138517218,138517219,138517224,138664143,139049601,139049602,139173686,139173688,139173690,139173696,139173702,139173706,139173713,139173714,139173716,139173717,139173719,139173721,139173722,139173724,139173726,139634671,139634695,139634798,139634809,139634824,139634828,139634832,139634836,139634844,139634858,139634941,139634953,139634959,139635049,139635060,139635076,139635082,139635090,139635098,139635113,139635179,139635187,139635189,139635191,139635197,139635786,139651939,139651940,139651941,139651942,139651943,139651944,139651945,139651946,139651947,139651948,139651949,139651952,139651953,139651954,139651955,139651956,139651957,139655256,139887425,139900522,139900528,139900534,139900535,139900546,139900556,139900647,139900654,139900657,139900665,139900677,140199618,140199619,140199620,140199621,140199622,140199624,140343952,140343953,140343954,140343955,140343956,140343957,140343958,140343959,140343960,140343961,140351192,140351193,140457934,140494685,140494778,140494789,140495218,140495290,140495295,140495303,140495304,140495307,140495313,140887089,140887094,140887099,140887106,140887110,140887114,140887117,140887124,140887133,140887141,140887154,140887169,140887180,140887215,140887225,140917303,140917304,140917306,140917307,140917311,140917312,140917315,140917316,140917317,140917318,140917319,140917320,140917321,140921260,140967553,141131983,141131986,141131990,141131993,141131995,141131998,141132001,141132007,141132290,141132293,141132302,141132325,141132995,141267986,141347608,141347610,141347611,141347612,141347613,141347614,141347615,141347616,141397337,141397339,141397340,141397341,141397342,141397343,141397344,141397345,141397346,141397347,141397348,141397349,141397352,141469966,141469967,141469968,141469969,141469970,141469971,141501966,141501967,141501972,141501977,141501988,141501992,141501995,141501996,141501999,141502003,141502006,141502010,141502021,141502024,141502202,141502207,141502272,141632212,141632213,141632214,141632215,141632216,141632217,141659109,141659110,141659111,141659112,141659113,141659114,141785693,141842390,141842391,141842392,141842445,141858255,141858256,142020782,142160930,142160931,142302481,142302482,142302484,142302485,142397657,142397968,142399896,142413192,142615469,142615470,142615472,142615473,142615474,142615475,142615476,142615477,142615479,142615480,142615481,142615483,142615484,143149539,143157723,143366145,143366291,143366358,143366373,143366387,143366549,143366606,143366625,143366687,143430277,143430278,143430281,143617665,143617666,143617667,143617668,143617669,143617670,143617671,143617672,143617673,143617674,143617675,143617676,143617679,143617680,143617681,143617682,143617683,143617686,143617688,143617689,143617690,143617691,143683784,143683787,143683788,143684714,143831461,143831468,143831603,143831608,143831609,143831612,143831613,143831614,143831615,143831616,143831617,143831618,143922066,143940860,143940861,143940875,143940876,143940878,143940881,143940882,143940883,143940884,143940885,143940886,143940887,143940888,143940889,143940890,143940891,143940892,144050348,144050352,144050357,144192973,144192980,144192981,144192988,144192991,144192996,144193003,144193008,144193014,144193021,144193024,144193028,144193031,144193035,144193040,144193043,144193049,144193054,144193057,144193059,144193061,144193064,144193066,144193068,144193069,144193070,144193071,144193072,144193073,144193074,144193075,144193076,144341503,144341504,144341505,144341506,144341507,144341508,144341509,144341510,144341511,144341512,144341513,144341514,144341515,144341521,144341522,144341523,144341524,144341525,144341526,144341527,144341528,144352244,144408811,144408812,144408813,144408814,144408815,144408816,144408906,144428686,144429116,144643793,144856780,144917266,144917267,144917268,144917269,144917270,144917319,144917320,144917321,144917322,144917323,144917324,144917326,144917327,145037333,145037334,145037335,145037336,145037337,145037338,145037339,145037382,145037385,145037387,145037388,145037389,145037391,145053553,145169012,145169013,145169014,145169024,145169025,145255859,145255860,145264767,145264768,145275611,145275612,145391990,145418799,145418800,145418801,145431378,145431379,145436069,145436070,145454279,145454280,145518996,145533414,145533415,145533416,145533417,145533418,145533420,145533421,145533422,145533423,145533424,145533425,145533426,145533428,145533827,145533828,145533829,145533830,145555444,145555445,145555446,145563986,145564024,145564025,145564026,145564027,145564028,145564029,145564030,145564031,145564032,145564033,145564034,145564035,145564036,145564037,145564038,145564039,145564040,145564041,145564042,145567360,145567361,145567362,145567363,145608766,145608767,145768504,145768507,145768513,145810750,145810751,145810752,145810753,145810754,145810755,145810756,145810757,145810758,145810761,145810762,145810763,145810764,145810765,145810766,145810767,145810768,145810769,145810771,145810772,145810773,145810774,145810775,145810776,145810777,145810778,145810779,145810780,145972221,145972222,145972223,145972224,145972225,146014737,146014738,146014739,146014740,146014741,146014742,146067739,146212196,146399022,146399023,146399024,146450702,146450703,146450704,146450705,146450706,146450840,146450841,146450842,146450843,146450871,146450872,146450873,146450874,146450875,146450876,146450877,146461284,146461285,146466612,146483378,146483379,146483380,146483397,146486320,146486321,146486322,146486337,146757672,146757673,146894527,146894529,146894531,146894533,146896973,146896974,146896975,146896976,146932680,146932681,146932682,146932692,146932693,146955032,146955043,146955044,146955045,146955046,146955047,146955048,146960567,146968415,146968426,146968427,146968428,146968429,146968447,147061731,147061751,147061752,147061754,147169377,147169378,147169379,147169380,147169384,147169385,147169386,147169387,147266400,147266492,147266503,147524558,147524559,147524560,147524562,160687455}'::bigint[]))
27. 1.452 1.452 ↑ 1.0 1 726

Index Only Scan using vogel_pkey on vogel u1 (cost=0.42..4.05 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=726)

  • Index Cond: (id = u0.id_vogel)
  • Heap Fetches: 13
28. 0.726 0.726 ↑ 1.0 1 726

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=726)

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

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

30. 0.014 0.014 ↑ 1.0 1 1

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 48
31. 0.000 4.356 ↑ 1.0 1 726

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

  • Filter: ((s.language_id = 1) AND (l.id = s.language_id) AND (v.id = s.species_id))
32. 0.726 4.356 ↑ 1.0 1 726

Limit (cost=8.46..8.46 rows=1 width=44) (actual time=0.006..0.006 rows=1 loops=726)

33. 0.726 3.630 ↑ 1.0 1 726

Sort (cost=8.46..8.46 rows=1 width=44) (actual time=0.005..0.005 rows=1 loops=726)

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

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.004..0.004 rows=1 loops=726)

  • Index Cond: ((id_species = v.id) AND (id_language = l.id))
Planning time : 15.586 ms
Execution time : 53,269.901 ms