explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vu7L : test1

Settings
# exclusive inclusive rows x rows loops node
1. 1,026.176 40,116.351 ↑ 1,177.5 141,024 1

Merge Join (cost=1,946,883.04..4,562,026.88 rows=166,054,329 width=434) (actual time=30,839.527..40,116.351 rows=141,024 loops=1)

  • Merge Cond: ((ta_rep_dw.codepm)::text = (tpmd.codepm)::text)
2. 161.488 12,414.467 ↓ 10.6 77,527 1

Sort (cost=1,120,776.11..1,120,794.48 rows=7,348 width=435) (actual time=12,392.444..12,414.467 rows=77,527 loops=1)

  • Sort Key: ta_rep_dw.codepm
  • Sort Method: external sort Disk: 8760kB
3. 2,058.044 12,252.979 ↓ 10.6 77,527 1

Merge Join (cost=0.98..1,120,304.25 rows=7,348 width=435) (actual time=0.043..12,252.979 rows=77,527 loops=1)

  • Merge Cond: ((thxd.codepersonne)::text = (ta_rep_dw.codepersonne)::text)
  • Join Filter: (COALESCE(NULLIF(thxd.nochronorep, '-1'::integer), ta_rep_dw.nochrono) = ta_rep_dw.nochrono)
  • Rows Removed by Join Filter: 11
4. 63.113 63.113 ↓ 1.0 28,630 1

Index Scan using ix_ta_histo_xml_dw_codep_3 on ta_histo_xml_dw thxd (cost=0.42..12,109.75 rows=27,411 width=434) (actual time=0.015..63.113 rows=28,630 loops=1)

  • Filter: (btraite = 0)
  • Rows Removed by Filter: 50706
5. 1,224.209 10,131.822 ↓ 8.5 8,190,188 1

Materialize (cost=0.56..1,081,151.64 rows=967,724 width=15) (actual time=0.025..10,131.822 rows=8,190,188 loops=1)

6. 3,095.064 8,907.613 ↓ 8.5 8,188,667 1

GroupAggregate (cost=0.56..1,069,055.09 rows=967,724 width=15) (actual time=0.023..8,907.613 rows=8,188,667 loops=1)

  • Group Key: ta_rep_dw.codepersonne, ta_rep_dw.nochrono, ta_rep_dw.codepm
7. 5,812.549 5,812.549 ↑ 1.0 9,515,849 1

Index Only Scan using ix_ta_rep_dw_clepm on ta_rep_dw (cost=0.56..986,798.62 rows=9,677,231 width=15) (actual time=0.013..5,812.549 rows=9,515,849 loops=1)

  • Heap Fetches: 9515849
8. 588.889 26,675.708 ↓ 1.0 4,594,363 1

Materialize (cost=826,106.93..961,698.38 rows=4,519,715 width=10) (actual time=18,447.079..26,675.708 rows=4,594,363 loops=1)

9. 1,269.376 26,086.819 ↑ 1.0 4,462,128 1

GroupAggregate (cost=826,106.93..905,201.95 rows=4,519,715 width=11) (actual time=18,447.075..26,086.819 rows=4,462,128 loops=1)

  • Group Key: tpmd.codepm, tpmd.codepprepperm
10. 23,284.394 24,817.443 ↑ 1.0 4,465,989 1

Sort (cost=826,106.93..837,406.22 rows=4,519,715 width=11) (actual time=18,447.038..24,817.443 rows=4,465,989 loops=1)

  • Sort Key: tpmd.codepm, tpmd.codepprepperm
  • Sort Method: external merge Disk: 96016kB
11. 1,533.049 1,533.049 ↑ 1.0 4,466,204 1

Seq Scan on ta_pm_dw tpmd (cost=0.00..172,019.15 rows=4,519,715 width=11) (actual time=0.007..1,533.049 rows=4,466,204 loops=1)