explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4riR : CDG

Settings
# exclusive inclusive rows x rows loops node
1. 0.228 79,183.706 ↑ 34.1 75 1

Unique (cost=1,595,770.66..1,595,789.81 rows=2,554 width=16) (actual time=79,183.346..79,183.706 rows=75 loops=1)

  • Buffers: shared hit=135665618 read=15276
2. 2.800 79,183.478 ↓ 1.1 2,757 1

Sort (cost=1,595,770.66..1,595,777.04 rows=2,554 width=16) (actual time=79,183.343..79,183.478 rows=2,757 loops=1)

  • Sort Key: "a15contracten$installatie_locatie"."contracten$locatieid", ((SubPlan 1))
  • Sort Method: quicksort Memory: 226kB
  • Buffers: shared hit=135665618 read=15276
3. 11.782 79,180.678 ↓ 1.1 2,757 1

Gather (cost=3,598.61..1,595,626.12 rows=2,554 width=16) (actual time=53.43..79,180.678 rows=2,757 loops=1)

  • Buffers: shared hit=135665615 read=15276
4. 18.892 54.024 ↑ 1.1 1,379 2

Hash Join (cost=2,598.61..7,360.11 rows=1,502 width=16) (actual time=34.11..54.024 rows=1,379 loops=2)

  • Buffers: shared hit=164 read=1964
5. 29.072 29.072 ↑ 1.2 176,099 2

Seq Scan on contracten$installatie_contract a16contracten$installatie_contract (cost=0..3,975.75 rows=207,175 width=16) (actual time=0.592..29.072 rows=176,099 loops=2)

  • Buffers: shared hit=2 read=1902
6. 0.000 6.060 ↑ 1.1 1,379 2

Hash (cost=2,579.3..2,579.3 rows=1,545 width=16) (actual time=6.06..6.06 rows=1,379 loops=2)

  • Buffers: shared hit=118 read=62
7. 7.679 11.551 ↓ 1.8 2,757 1

Bitmap Heap Scan on contracten$installatie_locatie a15contracten$installatie_locatie (cost=352.82..2,579.3 rows=1,545 width=16) (actual time=4.114..11.551 rows=2,757 loops=1)

  • Heap Blocks: exact=41
  • Buffers: shared hit=118 read=62
8. 3.872 3.872 ↓ 1.0 2,757 1

Bitmap Index Scan on "idx_contracten$installatie_locatie" (cost=0..352.17 rows=2,626 width=0) (actual time=3.872..3.872 rows=2,757 loops=1)

  • Index Cond: ("contracten$locatieid" = ANY ('{10414574138460642,10414574138460642,10414574138471244,10414574138471244,10414574138471245,10414574138471245,10414574138471246,10414574138471247,10414574138471247,10414574138471248,10414574138471248,10414574138471249,10414574138471249,10414574138471250,10414574138471250,10414574138471223,10414574138471223,10414574138471224,10414574138471224,10414574138471225,10414574138471225,10414574138471226,10414574138471226,10414574138471227,10414574138471227,10414574138471228,10414574138471228,10414574138471229,10414574138471229,10414574138471230,10414574138471230,10414574138471231,10414574138471231,10414574138471232,10414574138471233,10414574138471233,10414574138471234,10414574138471235,10414574138471236,10414574138471236,10414574138471237,10414574138471237,10414574138471238,10414574138471238,10414574138471239,10414574138471240,10414574138471241,10414574138471242,10414574138471242,10414574138471243,10414574138471243,10414574138471217,10414574138471217,10414574138471218,10414574138471218,10414574138471219,10414574138471219,10414574138471219,10414574138471220,10414574138471220,10414574138471221,10414574138471221,10414574138471222,10414574138471222,10414574138471486,10414574138471487,10414574138471508,10414574138471508,10414574138476080,10414574138489608,10414574138489608,10414574138490976,10414574138490976,10414574138490976,10414574138480073,10414574138471216,10414574138471216}'::bigint[]))
  • Buffers: shared hit=118 read=21
9.          

SubPlan (for Gather)

10. 8.271 79,114.872 ↑ 1.0 1 2,757

Aggregate (cost=621.37..621.38 rows=1 width=8) (actual time=28.696..28.696 rows=1 loops=2,757)

  • Buffers: shared hit=135665451 read=13312
11. 64.730 79,106.601 ↓ 3.0 3 2,757

Nested Loop (cost=6.93..621.37 rows=1 width=0) (actual time=28.493..28.693 rows=3 loops=2,757)

  • Buffers: shared hit=135665451 read=13312
12. 9.774 79,034.919 ↓ 3.0 3 2,757

Nested Loop (cost=6.93..620.23 rows=1 width=16) (actual time=28.477..28.667 rows=3 loops=2,757)

  • Filter: (("REG".nummer = 0) OR ("REG".id IS NULL))
  • Buffers: shared hit=135658500 read=13311
13. 2,256.222 78,767.490 ↓ 19.0 19 2,757

Nested Loop (cost=6.09..617.17 rows=1 width=24) (actual time=25.617..28.57 rows=19 loops=2,757)

  • Buffers: shared hit=135267734 read=13199
14. 2,493.620 63,675.672 ↓ 2,328.0 2,328 2,757

Nested Loop (cost=5.66..616.33 rows=1 width=40) (actual time=0.341..23.096 rows=2,328 loops=2,757)

  • Buffers: shared hit=109570268 read=5341
15. 2,456.487 48,247.500 ↓ 782.0 2,346 2,757

Nested Loop (cost=5.24..614.61 rows=3 width=32) (actual time=0.328..17.5 rows=2,346 loops=2,757)

  • Buffers: shared hit=83703761 read=3547
16. 11.028 11.028 ↓ 3.0 3 2,757

Seq Scan on aannemen$filterconfiguratie_weergevenmeldingtype c4aannemen$filterconfiguratie_weergevenmeldingtype (cost=0..1.23 rows=1 width=8) (actual time=0.001..0.004 rows=3 loops=2,757)

  • Filter: ("aannemen$filterconfiguratieid" = '65865144550293605'::bigint)
  • Buffers: shared hit=2756 read=1
17. 0.000 45,779.985 ↓ 45.2 2,350 8,271

Nested Loop (cost=5.24..612.73 rows=52 width=24) (actual time=0.183..5.535 rows=2,350 loops=8,271)

  • Buffers: shared hit=83701005 read=3546
18. 6,368.670 7,377.732 ↓ 45.4 2,363 8,271

Bitmap Heap Scan on aannemen$melding_contract c2aannemen$melding_contract (cost=4.82..177.46 rows=52 width=8) (actual time=0.177..0.892 rows=2,363 loops=8,271)

  • Heap Blocks: exact=5372265
  • Buffers: shared hit=5471733 read=1197
19. 1,009.062 1,009.062 ↓ 45.4 2,363 8,271

Bitmap Index Scan on "idx_aannemen$melding_contract" (cost=0..4.81 rows=52 width=0) (actual time=0.122..0.122 rows=2,363 loops=8,271)

  • Index Cond: ("contracten$contractid" = "a16contracten$installatie_contract"."contracten$contractid")
  • Buffers: shared hit=100615 read=50
20. 39,092.160 39,092.160 ↑ 1.0 1 19,546,080

Index Only Scan using "aannemen$melding_meldingtype_pkey" on aannemen$melding_meldingtype a6aannemen$melding_meldingtype (cost=0.42..8.36 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=19,546,080)

  • Index Cond: ("aannemen$meldingid" = "c2aannemen$melding_contract"."aannemen$meldingid")
  • Heap Fetches: 19440621
  • Buffers: shared hit=78229272 read=2349
21. 12,934.552 12,934.552 ↑ 1.0 1 6,467,276

Index Only Scan using "aannemen$melding_locatie_pkey" on aannemen$melding_locatie c1aannemen$melding_locatie (cost=0.42..0.56 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=6,467,276)

  • Index Cond: ("aannemen$meldingid" = "a6aannemen$melding_meldingtype"."aannemen$meldingid")
  • Filter: ("contracten$locatieid" = ANY ('{10414574138460642,10414574138460642,10414574138471244,10414574138471244,10414574138471245,10414574138471245,10414574138471246,10414574138471247,10414574138471247,10414574138471248,10414574138471248,10414574138471249,10414574138471249,10414574138471250,10414574138471250,10414574138471223,10414574138471223,10414574138471224,10414574138471224,10414574138471225,10414574138471225,10414574138471226,10414574138471226,10414574138471227,10414574138471227,10414574138471228,10414574138471228,10414574138471229,10414574138471229,10414574138471230,10414574138471230,10414574138471231,10414574138471231,10414574138471232,10414574138471233,10414574138471233,10414574138471234,10414574138471235,10414574138471236,10414574138471236,10414574138471237,10414574138471237,10414574138471238,10414574138471238,10414574138471239,10414574138471240,10414574138471241,10414574138471242,10414574138471242,10414574138471243,10414574138471243,10414574138471217,10414574138471217,10414574138471218,10414574138471218,10414574138471219,10414574138471219,10414574138471219,10414574138471220,10414574138471220,10414574138471221,10414574138471221,10414574138471222,10414574138471222,10414574138471486,10414574138471487,10414574138471508,10414574138471508,10414574138476080,10414574138489608,10414574138489608,10414574138490976,10414574138490976,10414574138490976,10414574138480073,10414574138471216,10414574138471216}'::bigint[]))
  • Heap Fetches: 6417835
  • Buffers: shared hit=25866507 read=1794
22. 12,835.596 12,835.596 ↓ 0.0 0 6,417,798

Index Scan using "aannemen$melding_pkey" on aannemen$melding MELDING (cost=0.42..0.83 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=6,417,798)

  • Index Cond: (id = "a6aannemen$melding_meldingtype"."aannemen$meldingid")
  • Filter: (melddatum >= '2019-11-21 14:32:33.496'::timestamp without time zone)
  • Buffers: shared hit=25697466 read=7858
23. 60.079 257.655 ↑ 1.0 1 51,531

Nested Loop (cost=0.84..3.05 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=51,531)

  • Buffers: shared hit=390766 read=112
24. 103.062 103.062 ↑ 1.0 1 51,531

Index Only Scan using "aannemen$melding_registratie_pkey1" on aannemen$melding_registratie a5aannemen$melding_registratie (cost=0.42..1.69 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=51,531)

  • Index Cond: ("aannemen$meldingid" = "MELDING".id)
  • Heap Fetches: 47257
  • Buffers: shared hit=201819 read=31
25. 94.514 94.514 ↑ 1.0 1 47,257

Index Scan using "contracten$registratie_pkey" on contracten$registratie REG (cost=0.42..1.37 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=47,257)

  • Index Cond: (id = "a5aannemen$melding_registratie"."contracten$registratieid")
  • Buffers: shared hit=188947 read=81
26. 6.952 6.952 ↑ 2.0 3 6,952

Seq Scan on aannemen$meldingtype j3aannemen$meldingtype (cost=0..1.06 rows=6 width=8) (actual time=0.001..0.001 rows=3 loops=6,952)

  • Buffers: shared hit=6951 read=1
Planning time : 63.987 ms
Execution time : 79,185.022 ms