explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XndR : test1

Settings
# exclusive inclusive rows x rows loops node
1. 1.920 99,834.477 ↑ 11.0 3,908 1

Hash Join (cost=3,059,725.76..4,156,009.51 rows=42,937 width=434) (actual time=89,579.807..99,834.477 rows=3,908 loops=1)

  • Hash Cond: ((ta_rep_dw.codepm)::text = (tpmd.codepm)::text)
2. 1,314.445 10,259.011 ↓ 13.2 4,997 1

Merge Join (cost=0.98..1,094,780.89 rows=380 width=435) (actual time=6.230..10,259.011 rows=4,997 loops=1)

  • Merge Cond: ((ta_rep_dw.codepersonne)::text = (thxd.codepersonne)::text)
  • Join Filter: (COALESCE(NULLIF(thxd.nochronorep, '-1'::integer), ta_rep_dw.nochrono) = ta_rep_dw.nochrono)
  • Rows Removed by Join Filter: 5366
3. 2,964.516 8,897.911 ↓ 8.5 8,185,747 1

GroupAggregate (cost=0.56..1,069,055.09 rows=967,724 width=15) (actual time=0.034..8,897.911 rows=8,185,747 loops=1)

  • Group Key: ta_rep_dw.codepersonne, ta_rep_dw.nochrono, ta_rep_dw.codepm
4. 5,933.395 5,933.395 ↑ 1.0 9,512,736 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.023..5,933.395 rows=9,512,736 loops=1)

  • Heap Fetches: 9512736
5. 1.032 46.655 ↓ 7.3 10,371 1

Materialize (cost=0.42..12,113.30 rows=1,417 width=434) (actual time=0.051..46.655 rows=10,371 loops=1)

6. 45.623 45.623 ↑ 1.1 1,278 1

Index Scan using ix_ta_histo_xml_dw_codep_3 on ta_histo_xml_dw thxd (cost=0.42..12,109.75 rows=1,417 width=434) (actual time=0.049..45.623 rows=1,278 loops=1)

  • Filter: ((codeevt)::text = '4206'::text)
  • Rows Removed by Filter: 78058
7. 0.316 89,573.546 ↑ 38.2 591 1

Hash (cost=3,059,442.29..3,059,442.29 rows=22,599 width=10) (actual time=89,573.546..89,573.546 rows=591 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 289kB
8. 309.656 89,573.230 ↑ 38.2 591 1

Hash Join (cost=2,917,975.21..3,059,442.29 rows=22,599 width=10) (actual time=81,613.151..89,573.230 rows=591 loops=1)

  • Hash Cond: ((tpmd.codepprepperm)::text = (v.codepp)::text)
9. 1,263.345 26,013.614 ↑ 1.0 4,462,344 1

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

  • Group Key: tpmd.codepm, tpmd.codepprepperm
10. 23,227.687 24,750.269 ↑ 1.0 4,466,204 1

Sort (cost=826,106.93..837,406.22 rows=4,519,715 width=11) (actual time=18,363.126..24,750.269 rows=4,466,204 loops=1)

  • Sort Key: tpmd.codepm, tpmd.codepprepperm
  • Sort Method: external merge Disk: 96016kB
11. 1,522.582 1,522.582 ↑ 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.004..1,522.582 rows=4,466,204 loops=1)

12. 1.771 63,249.960 ↓ 9,640.0 9,640 1

Hash (cost=2,091,868.26..2,091,868.26 rows=1 width=516) (actual time=63,249.960..63,249.960 rows=9,640 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 646kB
13. 1.003 63,248.189 ↓ 9,640.0 9,640 1

Subquery Scan on v (cost=17.07..2,091,868.26 rows=1 width=516) (actual time=63,246.422..63,248.189 rows=9,640 loops=1)

14. 2,987.432 63,247.186 ↓ 9,640.0 9,640 1

HashSetOp Except (cost=17.07..2,091,868.25 rows=1 width=75) (actual time=63,246.422..63,247.186 rows=9,640 loops=1)

15. 387.520 60,259.754 ↑ 1.0 6,605,213 1

Append (cost=17.07..1,860,459.73 rows=6,611,672 width=75) (actual time=189.124..60,259.754 rows=6,605,213 loops=1)

16. 1.386 193.629 ↓ 12,671.0 12,671 1

Subquery Scan on *SELECT* 1 (cost=17.07..17.09 rows=1 width=75) (actual time=189.122..193.629 rows=12,671 loops=1)

17. 15.735 192.243 ↓ 12,671.0 12,671 1

HashAggregate (cost=17.07..17.08 rows=1 width=75) (actual time=189.122..192.243 rows=12,671 loops=1)

  • Group Key: ta_pp_dw.codepp, ta_pp_dw.prenoms, ta_pp_dw.nomusage, ta_pp_dw.pseudo, ta_pp_dw.nompatro, ta_pp_dw.dtnaiss, ta_pp_dw.anneenaiss, ta_pp_dw.moisnaiss, ta_pp_dw.communenaiss, ta_pp_dw.dptnaiss, ta_pp_dw.lieunaiss, ta_pp_dw.arrnaiss, ta_pp_dw.codepaysnaiss, ta_pp_dw.codenationalite
18. 7.582 176.508 ↓ 13,199.0 13,199 1

Nested Loop Semi Join (cost=0.99..17.04 rows=1 width=75) (actual time=0.060..176.508 rows=13,199 loops=1)

19. 7.070 7.070 ↓ 17,984.0 17,984 1

Index Scan using ix_ta_pp_dw_btr_2 on ta_pp_dw (cost=0.43..8.45 rows=1 width=75) (actual time=0.031..7.070 rows=17,984 loops=1)

  • Index Cond: (btraite = 0)
20. 161.856 161.856 ↑ 1.0 1 17,984

Index Only Scan using ix_ta_pp_dw_cle_3 on ta_pp_dw tpd2 (cost=0.56..8.58 rows=1 width=23) (actual time=0.009..0.009 rows=1 loops=17,984)

  • Index Cond: (codepp = (ta_pp_dw.codepp)::text)
  • Filter: (btraite = ANY ('{1,2}'::integer[]))
  • Rows Removed by Filter: 1
  • Heap Fetches: 37063
21. 1,354.157 59,678.605 ↑ 1.0 6,592,542 1

Subquery Scan on *SELECT* 2 (cost=1,546,388.27..1,860,442.64 rows=6,611,671 width=75) (actual time=44,401.469..59,678.605 rows=6,592,542 loops=1)

22. 2,056.574 58,324.448 ↑ 1.0 6,592,542 1

Unique (cost=1,546,388.27..1,794,325.93 rows=6,611,671 width=75) (actual time=44,401.467..58,324.448 rows=6,592,542 loops=1)

23. 54,031.868 56,267.874 ↑ 1.0 6,593,687 1

Sort (cost=1,546,388.27..1,562,917.45 rows=6,611,671 width=75) (actual time=44,401.466..56,267.874 rows=6,593,687 loops=1)

  • Sort Key: ta_pp_dw_1.codepp, ta_pp_dw_1.prenoms, ta_pp_dw_1.nomusage, ta_pp_dw_1.pseudo, ta_pp_dw_1.nompatro, ta_pp_dw_1.dtnaiss, ta_pp_dw_1.anneenaiss, ta_pp_dw_1.moisnaiss, ta_pp_dw_1.communenaiss, ta_pp_dw_1.dptnaiss, ta_pp_dw_1.lieunaiss, ta_pp_dw_1.arrnaiss, ta_pp_dw_1.codepaysnaiss, ta_pp_dw_1.codenationalite
  • Sort Method: external merge Disk: 582312kB
24. 2,236.006 2,236.006 ↑ 1.0 6,593,687 1

Seq Scan on ta_pp_dw ta_pp_dw_1 (cost=0.00..209,832.89 rows=6,611,671 width=75) (actual time=0.007..2,236.006 rows=6,593,687 loops=1)

  • Filter: (btraite = ANY ('{1,2}'::integer[]))
  • Rows Removed by Filter: 17984