explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BQKf

Settings
# exclusive inclusive rows x rows loops node
1. 1,237.435 87,402.736 ↓ 2.5 779,560 1

WindowAgg (cost=11,466,637.17..11,495,318.18 rows=310,065 width=1,876) (actual time=84,291.485..87,402.736 rows=779,560 loops=1)

2. 996.752 86,165.301 ↓ 2.5 779,560 1

WindowAgg (cost=11,466,637.17..11,479,039.77 rows=310,065 width=1,844) (actual time=84,291.473..86,165.301 rows=779,560 loops=1)

3. 4,694.640 85,168.549 ↓ 2.5 779,560 1

Sort (cost=11,466,637.17..11,467,412.33 rows=310,065 width=1,776) (actual time=84,291.452..85,168.549 rows=779,560 loops=1)

  • Sort Key: a.relatie_id, a.afgenomen_product_volgnummer, a.persoon_volgnummer, a.module_id, a.registratiedatum
  • Sort Method: external sort Disk: 1329704kB
4. 788.832 80,473.909 ↓ 2.5 779,560 1

WindowAgg (cost=11,279,310.25..11,295,588.66 rows=310,065 width=1,776) (actual time=78,160.082..80,473.909 rows=779,560 loops=1)

5. 637.195 79,685.077 ↓ 2.5 779,560 1

WindowAgg (cost=11,279,310.25..11,287,061.88 rows=310,065 width=1,779) (actual time=78,160.034..79,685.077 rows=779,560 loops=1)

6. 3,629.329 79,047.882 ↓ 2.5 779,560 1

Sort (cost=11,279,310.25..11,280,085.41 rows=310,065 width=1,771) (actual time=78,159.995..79,047.882 rows=779,560 loops=1)

  • Sort Key: a.relatie_id, a.afgenomen_product_volgnummer, a.registratiedatum
  • Sort Method: external sort Disk: 1326200kB
7. 150.504 75,418.553 ↓ 2.5 779,560 1

Subquery Scan on a (cost=11,092,484.90..11,111,362.40 rows=310,065 width=1,771) (actual time=70,045.229..75,418.553 rows=779,560 loops=1)

8. 659.278 75,268.049 ↓ 2.5 779,560 1

Unique (cost=11,092,484.90..11,108,261.75 rows=310,065 width=1,776) (actual time=70,045.227..75,268.049 rows=779,560 loops=1)

9. 29,879.823 74,608.771 ↓ 2.9 3,052,798 1

Sort (cost=11,092,484.90..11,095,114.37 rows=1,051,790 width=1,776) (actual time=70,045.225..74,608.771 rows=3,052,798 loops=1)

  • Sort Key: a_1.relatie_id, a_1.afgenomen_product_volgnummer, a_1.persoon_volgnummer, a_1.module_id, a_1.registratiedatum, a_1.registratiedatum_volgnummer DESC, a_1.persoon_ingangsdatum DESC
  • Sort Method: external merge Disk: 5222232kB
10. 2,313.684 44,728.948 ↓ 2.9 3,052,798 1

Nested Loop Left Join (cost=1.00..10,039,743.98 rows=1,051,790 width=1,776) (actual time=6.068..44,728.948 rows=3,052,798 loops=1)

  • Join Filter: ((a_1.relatie_id = a_1.relatie_id) AND (a_1.afgenomen_product_volgnummer = a_1.afgenomen_product_volgnummer))
11. 5,781.688 5,781.688 ↓ 2.9 3,052,798 1

Seq Scan on afgenomen_product_module_persoon_rol_premie_stand a_1 (cost=0.00..793,672.14 rows=1,051,790 width=1,748) (actual time=1.039..5,781.688 rows=3,052,798 loops=1)

  • Filter: (registratiedatum >= persoon_ingangsdatum)
  • Rows Removed by Filter: 104113
12. 6,105.596 36,633.576 ↑ 1.0 1 3,052,798

GroupAggregate (cost=1.00..8.75 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=3,052,798)

  • Group Key: p.relatie_id, p.afgenomen_product_volgnummer
13. 3,039.496 30,527.980 ↓ 6.7 20 3,052,798

Nested Loop (cost=1.00..8.73 rows=3 width=8) (actual time=0.005..0.010 rows=20 loops=3,052,798)

14. 3,052.798 9,158.394 ↑ 1.0 1 3,052,798

Nested Loop (cost=0.58..5.03 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=3,052,798)

15. 3,052.798 3,052.798 ↑ 1.0 1 3,052,798

Index Only Scan using afgenomen_product_pkey on afgenomen_product p (cost=0.29..2.51 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=3,052,798)

  • Index Cond: ((relatie_id = a_1.relatie_id) AND (afgenomen_product_volgnummer = a_1.afgenomen_product_volgnummer))
  • Heap Fetches: 180168
16. 3,052.798 3,052.798 ↑ 1.0 1 3,052,798

Index Scan using versie_afgenomen_product_pkey on versie_afgenomen_product v (cost=0.29..2.51 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=3,052,798)

  • Index Cond: (((relatie_id)::integer = a_1.relatie_id) AND ((afgenomen_product_volgnummer)::integer = a_1.afgenomen_product_volgnummer))
  • Filter: ((ongedaan_moment IS NULL) AND ((beeindigd_indicatie)::bpchar = 'N'::bpchar))
  • Rows Removed by Filter: 0
17. 18,330.090 18,330.090 ↓ 6.7 20 3,055,015

Index Scan using afgenomen_module_pkey on afgenomen_module m (cost=0.42..3.66 rows=3 width=8) (actual time=0.002..0.006 rows=20 loops=3,055,015)

  • Index Cond: (((relatie_id)::integer = a_1.relatie_id) AND ((afgenomen_product_volgnummer)::integer = a_1.afgenomen_product_volgnummer))
  • Filter: ((ongedaan_moment IS NULL) AND ((ingangsdatum)::date IS DISTINCT FROM (einddatum)::date) AND ((beeindigd_indicatie)::bpchar = 'N'::bpchar))
  • Rows Removed by Filter: 0