explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t4Hm

Settings
# exclusive inclusive rows x rows loops node
1. 1,240.275 88,408.327 ↓ 2.5 779,560 1

WindowAgg (cost=11,461,378.22..11,490,059.23 rows=310,065 width=1,876) (actual time=85,292.097..88,408.327 rows=779,560 loops=1)

2. 1,005.142 87,168.052 ↓ 2.5 779,560 1

WindowAgg (cost=11,461,378.22..11,473,780.82 rows=310,065 width=1,844) (actual time=85,292.079..87,168.052 rows=779,560 loops=1)

3. 4,682.366 86,162.910 ↓ 2.5 779,560 1

Sort (cost=11,461,378.22..11,462,153.38 rows=310,065 width=1,776) (actual time=85,279.589..86,162.910 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. 779.835 81,480.544 ↓ 2.5 779,560 1

WindowAgg (cost=11,274,051.30..11,290,329.71 rows=310,065 width=1,776) (actual time=79,184.246..81,480.544 rows=779,560 loops=1)

5. 631.687 80,700.709 ↓ 2.5 779,560 1

WindowAgg (cost=11,274,051.30..11,281,802.93 rows=310,065 width=1,779) (actual time=79,184.198..80,700.709 rows=779,560 loops=1)

6. 3,535.391 80,069.022 ↓ 2.5 779,560 1

Sort (cost=11,274,051.30..11,274,826.46 rows=310,065 width=1,771) (actual time=79,184.143..80,069.022 rows=779,560 loops=1)

  • Sort Key: a.relatie_id, a.afgenomen_product_volgnummer, a.registratiedatum
  • Sort Method: external sort Disk: 1326200kB
7. 149.275 76,533.631 ↓ 2.5 779,560 1

Subquery Scan on a (cost=11,087,225.95..11,106,103.45 rows=310,065 width=1,771) (actual time=71,164.303..76,533.631 rows=779,560 loops=1)

8. 653.238 76,384.356 ↓ 2.5 779,560 1

Unique (cost=11,087,225.95..11,103,002.80 rows=310,065 width=1,776) (actual time=71,164.299..76,384.356 rows=779,560 loops=1)

9. 30,257.422 75,731.118 ↓ 2.9 3,052,798 1

Sort (cost=11,087,225.95..11,089,855.42 rows=1,051,790 width=1,776) (actual time=71,164.297..75,731.118 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,887.917 45,473.696 ↓ 2.9 3,052,798 1

Nested Loop Left Join (cost=1.00..10,034,485.03 rows=1,051,790 width=1,776) (actual time=6.666..45,473.696 rows=3,052,798 loops=1)

11. 5,952.203 5,952.203 ↓ 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.072..5,952.203 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