explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SF5I

Settings
# exclusive inclusive rows x rows loops node
1. 1,227.653 93,491.727 ↓ 2.5 779,560 1

WindowAgg (cost=11,506,885.36..11,535,566.37 rows=310,065 width=1,876) (actual time=90,413.972..93,491.727 rows=779,560 loops=1)

2. 984.278 92,264.074 ↓ 2.5 779,560 1

WindowAgg (cost=11,506,885.36..11,519,287.96 rows=310,065 width=1,844) (actual time=90,413.961..92,264.074 rows=779,560 loops=1)

3. 4,544.282 91,279.796 ↓ 2.5 779,560 1

Sort (cost=11,506,885.36..11,507,660.52 rows=310,065 width=1,776) (actual time=90,413.941..91,279.796 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: 1329696kB
4. 763.651 86,735.514 ↓ 2.5 779,560 1

WindowAgg (cost=11,320,333.60..11,335,836.85 rows=310,065 width=1,776) (actual time=84,513.935..86,735.514 rows=779,560 loops=1)

5. 609.404 85,971.863 ↓ 2.5 779,560 1

WindowAgg (cost=11,320,333.60..11,327,310.06 rows=310,065 width=1,775) (actual time=84,513.886..85,971.863 rows=779,560 loops=1)

6. 3,493.560 85,362.459 ↓ 2.5 779,560 1

Sort (cost=11,320,333.60..11,321,108.76 rows=310,065 width=1,771) (actual time=84,513.848..85,362.459 rows=779,560 loops=1)

  • Sort Key: a.relatie_id, a.afgenomen_product_volgnummer, a.registratiedatum
  • Sort Method: external sort Disk: 1326192kB
7. 148.554 81,868.899 ↓ 2.5 779,560 1

Subquery Scan on a (cost=11,133,508.25..11,152,385.75 rows=310,065 width=1,771) (actual time=76,540.795..81,868.899 rows=779,560 loops=1)

8. 670.701 81,720.345 ↓ 2.5 779,560 1

Unique (cost=11,133,508.25..11,149,285.10 rows=310,065 width=1,780) (actual time=76,540.792..81,720.345 rows=779,560 loops=1)

9. 30,003.531 81,049.644 ↓ 2.9 3,052,798 1

Sort (cost=11,133,508.25..11,136,137.72 rows=1,051,790 width=1,780) (actual time=76,540.789..81,049.644 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: 5222456kB
10. 8,306.941 51,046.113 ↓ 2.9 3,052,798 1

Seq Scan on afgenomen_product_module_persoon_rol_premie_stand a_1 (cost=0.00..10,076,556.63 rows=1,051,790 width=1,780) (actual time=6.156..51,046.113 rows=3,052,798 loops=1)

  • Filter: (registratiedatum >= persoon_ingangsdatum)
  • Rows Removed by Filter: 104113
11.          

SubPlan (for Seq Scan)

12. 0.000 42,739.172 ↑ 1.0 1 3,052,798

Subquery Scan on e (cost=1.00..8.81 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=3,052,798)

13. 9,158.394 42,739.172 ↑ 1.0 1 3,052,798

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

  • Group Key: p.relatie_id, p.afgenomen_product_volgnummer
14. 6,092.294 33,580.778 ↓ 6.7 20 3,052,798

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

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

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

16. 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
17. 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=17) (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
18. 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=12) (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