explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OjTM

Settings
# exclusive inclusive rows x rows loops node
1. 69.821 23,794,616.986 ↓ 330.0 11,880 1

Sort (cost=20,871,506.60..20,871,506.69 rows=36 width=110) (actual time=23,794,614.704..23,794,616.986 rows=11,880 loops=1)

  • Sort Key: mytable.sages, mytable.id_libelle
  • Sort Method: quicksort Memory: 1,360kB
2. 4.823 23,794,547.165 ↓ 330.0 11,880 1

Subquery Scan on mytable (cost=3,465,041.07..20,871,505.67 rows=36 width=110) (actual time=80,907.969..23,794,547.165 rows=11,880 loops=1)

3. 4.445 23,794,542.342 ↓ 330.0 11,880 1

Append (cost=3,465,041.07..20,871,505.31 rows=36 width=30) (actual time=80,907.967..23,794,542.342 rows=11,880 loops=1)

4.          

CTE creance_detail_pas_dossier

5. 126.349 69,802.214 ↓ 1.1 73,146 1

Nested Loop (cost=970,809.54..2,486,459.71 rows=68,881 width=242) (actual time=21,616.664..69,802.214 rows=73,146 loops=1)

6. 17,552.273 63,897.331 ↓ 1.1 73,146 1

Hash Join (cost=970,808.98..2,068,544.31 rows=68,881 width=200) (actual time=21,607.329..63,897.331 rows=73,146 loops=1)

  • Hash Cond: (dltpas_18.id_dossier = doss_18.id_dossier)
7. 24,761.221 24,761.221 ↑ 1.0 22,899,226 1

Seq Scan on creance_detail_pas dltpas_18 (cost=0.00..868,054.26 rows=22,899,226 width=200) (actual time=23.392..24,761.221 rows=22,899,226 loops=1)

8. 65.686 21,583.837 ↓ 1.3 68,651 1

Hash (cost=970,154.20..970,154.20 rows=52,382 width=16) (actual time=21,583.837..21,583.837 rows=68,651 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 3,219kB
9. 21,518.151 21,518.151 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_18 (cost=0.56..970,154.20 rows=52,382 width=16) (actual time=0.052..21,518.151 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
10. 5,778.534 5,778.534 ↑ 1.0 1 73,146

Index Scan using idx_fk_cdsr_id_versement_ordre_paiement_pas on creance_detail_statut_recent dltpasst (cost=0.56..6.06 rows=1 width=42) (actual time=0.079..0.079 rows=1 loops=73,146)

  • Index Cond: (id_creance_detail_pas = dltpas_18.id_creance_detail_pas)
11. 0.519 81,125.992 ↓ 330.0 660 1

Subquery Scan on *SELECT* 1 (cost=978,581.36..979,105.22 rows=2 width=51) (actual time=80,907.966..81,125.992 rows=660 loops=1)

12. 187.158 81,125.473 ↓ 330.0 660 1

GroupAggregate (cost=978,581.36..979,105.20 rows=2 width=51) (actual time=80,907.954..81,125.473 rows=660 loops=1)

  • Group Key: doss.sages
13. 261.554 80,938.315 ↓ 2.1 112,380 1

Sort (cost=978,581.36..978,712.32 rows=52,382 width=51) (actual time=80,906.898..80,938.315 rows=112,380 loops=1)

  • Sort Key: doss.sages
  • Sort Method: quicksort Memory: 10,626kB
14. 579.397 80,676.761 ↓ 2.1 112,380 1

Hash Left Join (cost=1,899.14..974,475.46 rows=52,382 width=51) (actual time=70,176.872..80,676.761 rows=112,380 loops=1)

  • Hash Cond: (doss.id_dossier = dltpas.id_dossier)
15. 9,935.995 9,935.995 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=15.477..9,935.995 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
16. 55.300 70,161.369 ↓ 210.2 73,146 1

Hash (cost=1,894.23..1,894.23 rows=348 width=56) (actual time=70,161.369..70,161.369 rows=73,146 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 4,999kB
17. 70,106.069 70,106.069 ↓ 210.2 73,146 1

CTE Scan on creance_detail_pas_dossier dltpas (cost=0.00..1,894.23 rows=348 width=56) (actual time=21,616.696..70,106.069 rows=73,146 loops=1)

  • Filter: ((mode_echange = '02'::bpchar) OR ((mode_echange = '01'::bpchar) AND (origine_depot = ANY ('{01,02}'::bpchar[]))))
18. 79.111 3,201.750 ↓ 330.0 660 1

HashAggregate (cost=976,623.47..976,623.49 rows=2 width=29) (actual time=3,201.437..3,201.750 rows=660 loops=1)

  • Group Key: doss_1.sages
19. 419.587 3,122.639 ↓ 2.1 112,380 1

Hash Left Join (cost=1,559.00..976,361.56 rows=52,382 width=29) (actual time=62.814..3,122.639 rows=112,380 loops=1)

  • Hash Cond: (doss_1.id_dossier = dltpas_1.id_dossier)
20. 2,641.011 2,641.011 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_1 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=0.745..2,641.011 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
21. 25.692 62.041 ↓ 106.2 73,146 1

Hash (cost=1,549.82..1,549.82 rows=689 width=34) (actual time=62.041..62.041 rows=73,146 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 3,759kB
22. 36.349 36.349 ↓ 106.2 73,146 1

CTE Scan on creance_detail_pas_dossier dltpas_1 (cost=0.00..1,549.82 rows=689 width=34) (actual time=0.007..36.349 rows=73,146 loops=1)

  • Filter: (mode_echange = ANY ('{01,02}'::bpchar[]))
23. 0.340 2,852.267 ↓ 330.0 660 1

Subquery Scan on *SELECT* 3 (cost=978,171.43..978,564.33 rows=2 width=27) (actual time=2,816.072..2,852.267 rows=660 loops=1)

24. 21.569 2,851.927 ↓ 330.0 660 1

GroupAggregate (cost=978,171.43..978,564.31 rows=2 width=27) (actual time=2,816.065..2,851.927 rows=660 loops=1)

  • Group Key: doss_2.sages
25. 152.670 2,830.358 ↓ 1.3 68,854 1

Sort (cost=978,171.43..978,302.38 rows=52,382 width=27) (actual time=2,815.958..2,830.358 rows=68,854 loops=1)

  • Sort Key: doss_2.sages
  • Sort Method: quicksort Memory: 6,324kB
26. 52.602 2,677.688 ↓ 1.3 68,854 1

Hash Left Join (cost=1,554.68..974,065.52 rows=52,382 width=27) (actual time=15.989..2,677.688 rows=68,854 loops=1)

  • Hash Cond: (doss_2.id_dossier = dltpas_2.id_dossier)
27. 2,609.185 2,609.185 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_2 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=0.071..2,609.185 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
28. 0.353 15.901 ↓ 2.2 751 1

Hash (cost=1,549.82..1,549.82 rows=344 width=32) (actual time=15.901..15.901 rows=751 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
29. 15.548 15.548 ↓ 2.2 751 1

CTE Scan on creance_detail_pas_dossier dltpas_2 (cost=0.00..1,549.82 rows=344 width=32) (actual time=0.190..15.548 rows=751 loops=1)

  • Filter: (mode_echange = '02'::bpchar)
  • Rows Removed by Filter: 72,395
30. 41.878 2,589.469 ↓ 330.0 660 1

HashAggregate (cost=974,327.43..974,327.46 rows=2 width=29) (actual time=2,589.191..2,589.469 rows=660 loops=1)

  • Group Key: doss_3.sages
31. 45.113 2,547.591 ↓ 1.3 68,854 1

Hash Left Join (cost=1,554.68..974,065.52 rows=52,382 width=29) (actual time=17.428..2,547.591 rows=68,854 loops=1)

  • Hash Cond: (doss_3.id_dossier = dltpas_3.id_dossier)
32. 2,485.129 2,485.129 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_3 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=0.059..2,485.129 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
33. 0.343 17.349 ↓ 2.2 751 1

Hash (cost=1,549.82..1,549.82 rows=344 width=34) (actual time=17.349..17.349 rows=751 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 38kB
34. 17.006 17.006 ↓ 2.2 751 1

CTE Scan on creance_detail_pas_dossier dltpas_3 (cost=0.00..1,549.82 rows=344 width=34) (actual time=0.167..17.006 rows=751 loops=1)

  • Filter: (mode_echange = '02'::bpchar)
  • Rows Removed by Filter: 72,395
35. 339.161 1,279,623.495 ↓ 330.0 660 1

HashAggregate (cost=974,495.31..974,495.33 rows=2 width=29) (actual time=1,279,623.209..1,279,623.495 rows=660 loops=1)

  • Group Key: doss_4.sages
36. 299.375 1,279,284.334 ↓ 1.3 68,651 1

Nested Loop Left Join (cost=0.56..974,233.40 rows=52,382 width=29) (actual time=15.340..1,279,284.334 rows=68,651 loops=1)

  • Join Filter: (doss_4.id_dossier = dltpas_4.id_dossier)
37. 72,786.889 72,786.889 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_4 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=0.079..72,786.889 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
38. 1,206,198.070 1,206,198.070 ↓ 0.0 0 68,651

CTE Scan on creance_detail_pas_dossier dltpas_4 (cost=0.00..1,722.03 rows=2 width=34) (actual time=17.570..17.570 rows=0 loops=68,651)

  • Filter: ((mode_echange = '02'::bpchar) AND (statut = 30))
  • Rows Removed by Filter: 73,146
39. 324.780 1,185,384.977 ↓ 330.0 660 1

HashAggregate (cost=974,839.71..974,839.74 rows=2 width=29) (actual time=1,185,384.447..1,185,384.977 rows=660 loops=1)

  • Group Key: doss_5.sages
40. 265.023 1,185,060.197 ↓ 1.3 68,651 1

Nested Loop Left Join (cost=0.56..974,577.80 rows=52,382 width=29) (actual time=16.404..1,185,060.197 rows=68,651 loops=1)

  • Join Filter: (doss_5.id_dossier = dltpas_5.id_dossier)
41. 69,285.075 69,285.075 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_5 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=1.889..69,285.075 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
42. 1,115,510.099 1,115,510.099 ↓ 0.0 0 68,651

CTE Scan on creance_detail_pas_dossier dltpas_5 (cost=0.00..2,066.43 rows=2 width=34) (actual time=16.249..16.249 rows=0 loops=68,651)

  • Filter: ((mode_echange = '02'::bpchar) AND (statut = 30) AND ((detail ->> 'NatureDeclaration'::text) <> '14'::text))
  • Rows Removed by Filter: 73,146
43. 316.092 1,184,833.687 ↓ 330.0 660 1

HashAggregate (cost=973,661.13..973,661.15 rows=2 width=29) (actual time=1,184,833.409..1,184,833.687 rows=660 loops=1)

  • Group Key: doss_6.sages
44. 264.458 1,184,517.595 ↓ 1.3 68,651 1

Nested Loop Left Join (cost=0.56..973,399.22 rows=52,382 width=29) (actual time=47.359..1,184,517.595 rows=68,651 loops=1)

  • Join Filter: (doss_6.id_dossier = dltpas_6.id_dossier)
45. 73,342.655 73,342.655 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_6 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=27.029..73,342.655 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
46. 1,110,910.482 1,110,910.482 ↓ 0.0 0 68,651

CTE Scan on creance_detail_pas_dossier dltpas_6 (cost=0.00..2,066.43 rows=1 width=34) (actual time=16.182..16.182 rows=0 loops=68,651)

  • Filter: ((mode_echange = '02'::bpchar) AND (statut = 30) AND ((detail ->> 'NatureDeclaration'::text) = '14'::text))
  • Rows Removed by Filter: 73,146
47. 303.853 1,338,614.840 ↓ 330.0 660 1

HashAggregate (cost=974,495.31..974,495.33 rows=2 width=29) (actual time=1,338,614.502..1,338,614.840 rows=660 loops=1)

  • Group Key: doss_7.sages
48. 19,375.490 1,338,310.987 ↓ 1.3 68,854 1

Nested Loop Left Join (cost=0.56..974,233.40 rows=52,382 width=29) (actual time=16.172..1,338,310.987 rows=68,854 loops=1)

  • Join Filter: (doss_7.id_dossier = dltpas_7.id_dossier)
  • Rows Removed by Join Filter: 51,556,150
49. 83,766.705 83,766.705 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_7 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=1.982..83,766.705 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
50. 1,235,168.792 1,235,168.792 ↓ 375.5 751 68,651

CTE Scan on creance_detail_pas_dossier dltpas_7 (cost=0.00..1,722.03 rows=2 width=34) (actual time=0.198..17.992 rows=751 loops=68,651)

  • Filter: ((mode_echange = '02'::bpchar) AND (statut = 20))
  • Rows Removed by Filter: 72,395
51. 317.167 1,247,228.369 ↓ 330.0 660 1

HashAggregate (cost=974,839.71..974,839.74 rows=2 width=29) (actual time=1,247,227.995..1,247,228.369 rows=660 loops=1)

  • Group Key: doss_8.sages
52. 303.427 1,246,911.202 ↓ 1.3 68,651 1

Nested Loop Left Join (cost=0.56..974,577.80 rows=52,382 width=29) (actual time=33.003..1,246,911.202 rows=68,651 loops=1)

  • Join Filter: (doss_8.id_dossier = dltpas_8.id_dossier)
53. 77,206.641 77,206.641 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_8 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=19.251..77,206.641 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
54. 1,169,401.134 1,169,401.134 ↓ 0.0 0 68,651

CTE Scan on creance_detail_pas_dossier dltpas_8 (cost=0.00..2,066.43 rows=2 width=34) (actual time=17.034..17.034 rows=0 loops=68,651)

  • Filter: ((mode_echange = '02'::bpchar) AND (statut = 20) AND ((detail ->> 'NatureDeclaration'::text) <> '14'::text))
  • Rows Removed by Filter: 73,146
55. 311.379 1,156,447.212 ↓ 330.0 660 1

HashAggregate (cost=973,661.13..973,661.15 rows=2 width=29) (actual time=1,156,446.937..1,156,447.212 rows=660 loops=1)

  • Group Key: doss_9.sages
56. 226.147 1,156,135.833 ↓ 1.3 68,651 1

Nested Loop Left Join (cost=0.56..973,399.22 rows=52,382 width=29) (actual time=69.584..1,156,135.833 rows=68,651 loops=1)

  • Join Filter: (doss_9.id_dossier = dltpas_9.id_dossier)
57. 65,731.806 65,731.806 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_9 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=36.786..65,731.806 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
58. 1,090,177.880 1,090,177.880 ↓ 0.0 0 68,651

CTE Scan on creance_detail_pas_dossier dltpas_9 (cost=0.00..2,066.43 rows=1 width=34) (actual time=15.880..15.880 rows=0 loops=68,651)

  • Filter: ((mode_echange = '02'::bpchar) AND (statut = 20) AND ((detail ->> 'NatureDeclaration'::text) = '14'::text))
  • Rows Removed by Filter: 73,146
59. 0.408 4,236,760.913 ↓ 330.0 660 1

Subquery Scan on *SELECT* 11 (cost=978,339.30..978,732.21 rows=2 width=27) (actual time=4,236,671.519..4,236,760.913 rows=660 loops=1)

60. 62.372 4,236,760.505 ↓ 330.0 660 1

GroupAggregate (cost=978,339.30..978,732.19 rows=2 width=27) (actual time=4,236,671.505..4,236,760.505 rows=660 loops=1)

  • Group Key: doss_10.sages
61. 531.067 4,236,698.133 ↓ 2.1 112,058 1

Sort (cost=978,339.30..978,470.26 rows=52,382 width=27) (actual time=4,236,671.423..4,236,698.133 rows=112,058 loops=1)

  • Sort Key: doss_10.sages
  • Sort Method: quicksort Memory: 10,571kB
62. 1,376,725.925 4,236,167.066 ↓ 2.1 112,058 1

Nested Loop Left Join (cost=0.56..974,233.40 rows=52,382 width=27) (actual time=56.321..4,236,167.066 rows=112,058 loops=1)

  • Join Filter: (doss_10.id_dossier = dltpas_10.id_dossier)
  • Rows Removed by Join Filter: 4,933,875,500
63. 112,302.725 112,302.725 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_10 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=0.635..112,302.725 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
64. 2,747,138.416 2,747,138.416 ↓ 35,935.0 71,870 68,651

CTE Scan on creance_detail_pas_dossier dltpas_10 (cost=0.00..1,722.03 rows=2 width=32) (actual time=0.002..40.016 rows=71,870 loops=68,651)

  • Filter: ((mode_echange = '01'::bpchar) AND (origine_depot = '01'::bpchar))
  • Rows Removed by Filter: 1,276
65. 0.380 1,701,973.770 ↓ 330.0 660 1

Subquery Scan on *SELECT* 12 (cost=978,339.30..978,732.21 rows=2 width=27) (actual time=1,701,935.777..1,701,973.770 rows=660 loops=1)

66. 21.971 1,701,973.390 ↓ 330.0 660 1

GroupAggregate (cost=978,339.30..978,732.19 rows=2 width=27) (actual time=1,701,935.764..1,701,973.390 rows=660 loops=1)

  • Group Key: doss_11.sages
67. 403.955 1,701,951.419 ↓ 1.3 68,726 1

Sort (cost=978,339.30..978,470.26 rows=52,382 width=27) (actual time=1,701,935.524..1,701,951.419 rows=68,726 loops=1)

  • Sort Key: doss_11.sages
  • Sort Method: quicksort Memory: 6,310kB
68. 14,454.512 1,701,547.464 ↓ 1.3 68,726 1

Nested Loop Left Join (cost=0.56..974,233.40 rows=52,382 width=27) (actual time=63.543..1,701,547.464 rows=68,726 loops=1)

  • Join Filter: (doss_11.id_dossier = dltpas_11.id_dossier)
  • Rows Removed by Join Filter: 36,041,250
69. 91,986.967 91,986.967 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_11 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=33.253..91,986.967 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
70. 1,595,105.985 1,595,105.985 ↓ 262.5 525 68,651

CTE Scan on creance_detail_pas_dossier dltpas_11 (cost=0.00..1,722.03 rows=2 width=32) (actual time=0.046..23.235 rows=525 loops=68,651)

  • Filter: ((mode_echange = '01'::bpchar) AND (origine_depot = '02'::bpchar))
  • Rows Removed by Filter: 72,621
71. 0.493 4,264,454.444 ↓ 330.0 660 1

Subquery Scan on *SELECT* 13 (cost=979,517.89..979,910.79 rows=2 width=39) (actual time=4,264,263.150..4,264,454.444 rows=660 loops=1)

72. 165.959 4,264,453.951 ↓ 330.0 660 1

GroupAggregate (cost=979,517.89..979,910.77 rows=2 width=39) (actual time=4,264,263.134..4,264,453.951 rows=660 loops=1)

  • Group Key: doss_12.sages
73. 489.529 4,264,287.992 ↓ 2.1 112,170 1

Sort (cost=979,517.89..979,648.84 rows=52,382 width=39) (actual time=4,264,257.216..4,264,287.992 rows=112,170 loops=1)

  • Sort Key: doss_12.sages
  • Sort Method: quicksort Memory: 10,593kB
74. 1,375,871.078 4,263,798.463 ↓ 2.1 112,170 1

Nested Loop Left Join (cost=0.56..975,411.98 rows=52,382 width=39) (actual time=105.463..4,263,798.463 rows=112,170 loops=1)

  • Join Filter: (doss_12.id_dossier = dltpas_12.id_dossier)
  • Rows Removed by Join Filter: 4,969,916,750
75. 105,296.402 105,296.402 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_12 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=29.699..105,296.402 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
76. 2,782,630.983 2,782,630.983 ↓ 24,131.7 72,395 68,651

CTE Scan on creance_detail_pas_dossier dltpas_12 (cost=0.00..1,722.03 rows=3 width=44) (actual time=0.002..40.533 rows=72,395 loops=68,651)

  • Filter: ((origine_depot = ANY ('{01,02}'::bpchar[])) AND (mode_echange = '01'::bpchar))
  • Rows Removed by Filter: 751
77. 105.802 25,788.392 ↓ 330.0 660 1

HashAggregate (cost=974,327.43..974,327.46 rows=2 width=29) (actual time=25,788.077..25,788.392 rows=660 loops=1)

  • Group Key: doss_13.sages
78. 565.330 25,682.590 ↓ 2.1 112,170 1

Hash Left Join (cost=1,554.68..974,065.52 rows=52,382 width=29) (actual time=64.716..25,682.590 rows=112,170 loops=1)

  • Hash Cond: (doss_13.id_dossier = dltpas_13.id_dossier)
79. 25,059.678 25,059.678 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_13 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=7.108..25,059.678 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
80. 24.461 57.582 ↓ 210.5 72,395 1

Hash (cost=1,549.82..1,549.82 rows=344 width=34) (actual time=57.582..57.582 rows=72,395 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 3,721kB
81. 33.121 33.121 ↓ 210.5 72,395 1

CTE Scan on creance_detail_pas_dossier dltpas_13 (cost=0.00..1,549.82 rows=344 width=34) (actual time=0.007..33.121 rows=72,395 loops=1)

  • Filter: (mode_echange = '01'::bpchar)
  • Rows Removed by Filter: 751
82. 331.110 1,749,045.841 ↓ 330.0 660 1

HashAggregate (cost=974,495.31..974,495.33 rows=2 width=29) (actual time=1,749,045.558..1,749,045.841 rows=660 loops=1)

  • Group Key: doss_14.sages
83. 307.677 1,748,714.731 ↓ 1.3 68,651 1

Nested Loop Left Join (cost=0.56..974,233.40 rows=52,382 width=29) (actual time=27.414..1,748,714.731 rows=68,651 loops=1)

  • Join Filter: (doss_14.id_dossier = dltpas_14.id_dossier)
84. 57,121.018 57,121.018 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_14 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=5.915..57,121.018 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
85. 1,691,286.036 1,691,286.036 ↓ 0.0 0 68,651

CTE Scan on creance_detail_pas_dossier dltpas_14 (cost=0.00..1,722.03 rows=2 width=34) (actual time=24.636..24.636 rows=0 loops=68,651)

  • Filter: ((mode_echange = '01'::bpchar) AND (statut = 30))
  • Rows Removed by Filter: 73,146
86. 397.777 4,257,164.629 ↓ 330.0 660 1

HashAggregate (cost=974,495.31..974,495.33 rows=2 width=29) (actual time=4,257,164.204..4,257,164.629 rows=660 loops=1)

  • Group Key: doss_15.sages
87. 1,355,040.247 4,256,766.852 ↓ 2.1 112,170 1

Nested Loop Left Join (cost=0.56..974,233.40 rows=52,382 width=29) (actual time=57.216..4,256,766.852 rows=112,170 loops=1)

  • Join Filter: (doss_15.id_dossier = dltpas_15.id_dossier)
  • Rows Removed by Join Filter: 4,969,916,750
88. 92,527.685 92,527.685 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_15 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=7.535..92,527.685 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
89. 2,809,198.920 2,809,198.920 ↓ 36,197.5 72,395 68,651

CTE Scan on creance_detail_pas_dossier dltpas_15 (cost=0.00..1,722.03 rows=2 width=34) (actual time=0.002..40.920 rows=72,395 loops=68,651)

  • Filter: ((mode_echange = '01'::bpchar) AND (statut = 20))
  • Rows Removed by Filter: 751
90. 0.381 25,810.086 ↓ 330.0 660 1

Subquery Scan on *SELECT* 17 (cost=976,979.93..977,372.84 rows=2 width=27) (actual time=25,725.527..25,810.086 rows=660 loops=1)

91. 55.272 25,809.705 ↓ 330.0 660 1

GroupAggregate (cost=976,979.93..977,372.82 rows=2 width=27) (actual time=25,725.516..25,809.705 rows=660 loops=1)

  • Group Key: doss_16.sages
92. 225.860 25,754.433 ↓ 2.1 112,380 1

Sort (cost=976,979.93..977,110.89 rows=52,382 width=27) (actual time=25,725.441..25,754.433 rows=112,380 loops=1)

  • Sort Key: doss_16.sages
  • Sort Method: quicksort Memory: 10,626kB
93. 83.958 25,528.573 ↓ 2.1 112,380 1

Hash Right Join (cost=970,808.98..972,874.03 rows=52,382 width=27) (actual time=25,413.902..25,528.573 rows=112,380 loops=1)

  • Hash Cond: (dltpas_16.id_dossier = doss_16.id_dossier)
94. 30.796 30.796 ↓ 1.1 73,146 1

CTE Scan on creance_detail_pas_dossier dltpas_16 (cost=0.00..1,377.62 rows=68,537 width=32) (actual time=0.006..30.796 rows=73,146 loops=1)

  • Filter: (id_creance_agregat IS NOT NULL)
95. 80.724 25,413.819 ↓ 1.3 68,651 1

Hash (cost=970,154.20..970,154.20 rows=52,382 width=27) (actual time=25,413.819..25,413.819 rows=68,651 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 3,956kB
96. 25,333.095 25,333.095 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_16 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=19.169..25,333.095 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
97. 49.235 51,637.764 ↓ 330.0 660 1

HashAggregate (cost=1,792,366.24..1,792,366.27 rows=2 width=29) (actual time=51,637.546..51,637.764 rows=660 loops=1)

  • Group Key: doss_17.sages
98. 218.794 51,588.529 ↓ 1.6 105,021 1

Hash Right Join (cost=1,790,403.36..1,792,043.54 rows=64,540 width=29) (actual time=51,326.356..51,588.529 rows=105,021 loops=1)

  • Hash Cond: (dltpas_17.id_creance_etb = creanceetb.id_creance_etb)
  • Join Filter: (((dltpas_17.detail ->> 'Horodatage'::text))::date > creance.dld_declaree)
  • Rows Removed by Join Filter: 67,310
99. 43.671 43.671 ↓ 1.1 73,146 1

CTE Scan on creance_detail_pas_dossier dltpas_17 (cost=0.00..1,377.62 rows=68,881 width=66) (actual time=0.002..43.671 rows=73,146 loops=1)

100. 44.916 51,326.064 ↓ 1.6 101,076 1

Hash (cost=1,789,596.61..1,789,596.61 rows=64,540 width=35) (actual time=51,326.064..51,326.064 rows=101,076 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 5,996kB
101. 13,626.102 51,281.148 ↓ 1.6 101,076 1

Hash Right Join (cost=1,327,142.35..1,789,596.61 rows=64,540 width=35) (actual time=26,652.534..51,281.148 rows=101,076 loops=1)

  • Hash Cond: (creanceetb.id_creance = creance.id_creance)
102. 11,017.966 11,017.966 ↑ 1.0 20,912,062 1

Seq Scan on creance_etb creanceetb (cost=0.00..383,388.62 rows=20,912,062 width=32) (actual time=15.431..11,017.966 rows=20,912,062 loops=1)

103. 78.957 26,637.080 ↓ 1.3 68,749 1

Hash (cost=1,326,487.58..1,326,487.58 rows=52,382 width=35) (actual time=26,637.080..26,637.080 rows=68,749 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 3,723kB
104. 155.417 26,558.123 ↓ 1.3 68,749 1

Nested Loop Left Join (cost=1.12..1,326,487.58 rows=52,382 width=35) (actual time=48.504..26,558.123 rows=68,749 loops=1)

105. 9,514.560 9,514.560 ↓ 1.3 68,651 1

Index Scan using idx_dossier_1 on dossier doss_17 (cost=0.56..970,154.20 rows=52,382 width=27) (actual time=1.501..9,514.560 rows=68,651 loops=1)

  • Index Cond: (((obf)::text = 'PAS'::text) AND (date_deb_per <= '2018-12-31 22:59:59.999'::timestamp without time zone) AND (date_fin_per >= '2018-11-30 23:00:00'::timestamp without time zone))
  • Filter: (statut = 20::smallint)
  • Rows Removed by Filter: 7
106. 16,888.146 16,888.146 ↓ 0.0 0 68,651

Index Scan using idx_fk_creance_id_dossier on creance (cost=0.56..6.79 rows=1 width=40) (actual time=0.246..0.246 rows=0 loops=68,651)

  • Index Cond: (doss_17.id_dossier = id_dossier)
Planning time : 156.596 ms
Execution time : 23,794,647.181 ms