explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fvL6 : EXF

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.000 60,797.335 ↑ 326,003.2 875 1

Group (cost=8,414,309,114.95..8,419,533,164.62 rows=285,252,800 width=55) (actual time=60,796.231..60,797.335 rows=875 loops=1)

  • Buffers: shared hit=58092 read=65896, temp read=16110 written=13450
2. 4.487 60,796.335 ↑ 232,222.3 1,554 1

Sort (cost=8,414,309,114.95..8,415,211,298.49 rows=360,873,413 width=55) (actual time=60,796.215..60,796.335 rows=1,554 loops=1)

  • Sort Key: c.id_facturedemandeur, c.tva, b.pays, ('COEXTIM'::character varying(12))
  • Sort Method: quicksort Memory: 170kB
  • Buffers: shared hit=58092 read=65896, temp read=16110 written=13450
3. 24.431 60,791.848 ↑ 232,222.3 1,554 1

Merge Join (cost=8,308,108,877.27..8,313,678,351.05 rows=360,873,413 width=55) (actual time=60,651.796..60,791.848 rows=1,554 loops=1)

  • Buffers: shared hit=58089 read=65896, temp read=16110 written=13450
4. 23.753 42,346.845 ↑ 6.0 49,305 1

Unique (cost=2,258,847.65..2,261,065.19 rows=295,672 width=18) (actual time=42,185.754..42,346.845 rows=49,305 loops=1)

  • Buffers: shared hit=48398 read=39708, temp read=9017 written=6363
5. 2,442.579 42,323.092 ↑ 3.2 91,613 1

Sort (cost=2,258,847.65..2,259,586.83 rows=295,672 width=18) (actual time=42,185.75..42,323.092 rows=91,613 loops=1)

  • Sort Key: facturec.numerofacture, ('COEXTIM'::character varying(12))
  • Sort Method: external merge Disk: 2592kB
  • Buffers: shared hit=48398 read=39708, temp read=9017 written=6363
6. 29.539 39,880.513 ↑ 3.2 92,452 1

Append (cost=0.41..2,225,914.97 rows=295,672 width=18) (actual time=2.484..39,880.513 rows=92,452 loops=1)

  • Buffers: shared hit=48398 read=39708, temp read=8692 written=6038
7. 11.012 11.012 ↑ 1.7 3 1

Index Only Scan using index_numerofacture_facturec on facturec facturec (cost=0.41..1,979.83 rows=5 width=11) (actual time=2.483..11.012 rows=3 loops=1)

  • Filter: ((numerofacture)::text ~~ 'FA/COEXTIM/%'::text)
  • Buffers: shared hit=1 read=242
8. 8.736 8.736 ↑ 5.0 1 1

Index Only Scan using index_numerofacture_facturec on facturec facturec_1 (cost=0.41..1,979.83 rows=5 width=11) (actual time=1.813..8.736 rows=1 loops=1)

  • Filter: ((numerofacture)::text ~~ 'FA/CVE/%'::text)
  • Buffers: shared hit=243
9. 152.459 15,645.112 ↑ 2.4 41,583 1

Group (cost=1,567,818.45..1,620,630.41 rows=98,554 width=18) (actual time=15,089.56..15,645.112 rows=41,583 loops=1)

  • Buffers: shared hit=38888 read=16423, temp read=5537 written=2883
10. 10,333.726 15,492.653 ↑ 29.5 238,976 1

Sort (cost=1,567,818.45..1,585,422.44 rows=7,041,594 width=18) (actual time=15,089.552..15,492.653 rows=238,976 loops=1)

  • Sort Key: f.numerofacture, m_1.societe
  • Sort Method: external merge Disk: 6680kB
  • Buffers: shared hit=38888 read=16423, temp read=5537 written=2883
11. 613.350 5,158.927 ↑ 29.5 238,976 1

Merge Join (cost=346,007.95..478,106.19 rows=7,041,594 width=18) (actual time=3,784.743..5,158.927 rows=238,976 loops=1)

  • Buffers: shared hit=38888 read=16423, temp read=4700 written=2046
12. 752.422 1,100.164 ↑ 4.8 159,120 1

Sort (cost=113,810.84..115,711.61 rows=760,307 width=11) (actual time=973.269..1,100.164 rows=159,120 loops=1)

  • Sort Key: ((e_1.id_expertise)::numeric)
  • Sort Method: external sort Disk: 4464kB
  • Buffers: shared hit=9222 read=3139, temp read=558 written=558
13. 110.922 347.742 ↑ 4.8 159,145 1

Merge Join (cost=4,275.97..26,547.71 rows=760,307 width=11) (actual time=150.439..347.742 rows=159,145 loops=1)

  • Buffers: shared hit=9222 read=3139
14. 51.435 104.897 ↑ 1.0 24,638 1

Sort (cost=4,275.55..4,337.14 rows=24,638 width=11) (actual time=99.154..104.897 rows=24,638 loops=1)

  • Sort Key: ((m_1.id_mission)::numeric)
  • Sort Method: quicksort Memory: 2601kB
  • Buffers: shared read=2232
15. 53.462 53.462 ↑ 1.0 24,638 1

Seq Scan on mission m_1 (cost=0..2,478.38 rows=24,638 width=11) (actual time=0.082..53.462 rows=24,638 loops=1)

  • Buffers: shared read=2232
16. 54.914 131.923 ↑ 1.0 222,772 1

Materialize (cost=0.42..9,338.93 rows=222,772 width=9) (actual time=0.099..131.923 rows=222,772 loops=1)

  • Buffers: shared hit=9222 read=907
17. 77.009 77.009 ↑ 1.0 222,772 1

Index Only Scan using index_expertise_id_mission_expertise on expertise e_1 (cost=0.42..8,782 rows=222,772 width=9) (actual time=0.093..77.009 rows=222,772 loops=1)

  • Buffers: shared hit=9222 read=907
18. 165.717 3,445.413 ↑ 4.2 300,037 1

Materialize (cost=232,197.11..238,533.19 rows=1,267,216 width=17) (actual time=2,811.44..3,445.413 rows=300,037 loops=1)

  • Buffers: shared hit=29666 read=13284, temp read=4142 written=1488
19. 2,284.025 3,279.696 ↑ 4.2 300,037 1

Sort (cost=232,197.11..235,365.15 rows=1,267,216 width=17) (actual time=2,811.432..3,279.696 rows=300,037 loops=1)

  • Sort Key: h.id_expertise
  • Sort Method: external merge Disk: 8200kB
  • Buffers: shared hit=29666 read=13284, temp read=4142 written=1488
20. 290.068 995.671 ↑ 4.2 300,037 1

Merge Join (cost=8,021.25..77,753.3 rows=1,267,216 width=17) (actual time=258.435..995.671 rows=300,037 loops=1)

  • Buffers: shared hit=29666 read=13284, temp read=3116 written=462
21. 337.298 337.298 ↑ 1.0 300,049 1

Index Scan using histofac3 on histofac h (cost=0.42..46,055.94 rows=300,049 width=12) (actual time=0.071..337.298 rows=300,049 loops=1)

  • Buffers: shared hit=29666 read=9755
22. 307.719 368.305 ↓ 6.0 304,068 1

Sort (cost=8,020.83..8,148.17 rows=50,938 width=15) (actual time=258.299..368.305 rows=304,068 loops=1)

  • Sort Key: ((f.id_facturedemandeur)::numeric)
  • Sort Method: external sort Disk: 1848kB
  • Buffers: shared read=3529, temp read=1789 written=462
23. 60.586 60.586 ↑ 1.0 50,938 1

Seq Scan on facturec f (cost=0..4,038.38 rows=50,938 width=15) (actual time=0.544..60.586 rows=50,938 loops=1)

  • Buffers: shared read=3529
24. 15.649 1,184.256 ↑ 24.6 4,002 1

Group (cost=142,820.46..149,535.11 rows=98,554 width=18) (actual time=1,160.563..1,184.256 rows=4,002 loops=1)

  • Buffers: shared hit=4878 read=2401, temp read=206 written=206
25. 218.760 1,168.607 ↑ 89.5 10,007 1

Sort (cost=142,820.46..145,058.68 rows=895,287 width=18) (actual time=1,160.555..1,168.607 rows=10,007 loops=1)

  • Sort Key: f_1.numerofacture, m_2.societe
  • Sort Method: quicksort Memory: 1146kB
  • Buffers: shared hit=4878 read=2401, temp read=206 written=206
26. 58.713 949.847 ↑ 89.5 10,007 1

Merge Join (cost=19,901.94..35,951.43 rows=895,287 width=18) (actual time=823.684..949.847 rows=10,007 loops=1)

  • Buffers: shared hit=4878 read=2401, temp read=206 written=206
27. 517.404 596.236 ↑ 1.0 50,876 1

Sort (cost=8,020.83..8,148.17 rows=50,938 width=15) (actual time=531.644..596.236 rows=50,876 loops=1)

  • Sort Key: ((f_1.id_facturedemandeur)::numeric)
  • Sort Method: external merge Disk: 1640kB
  • Buffers: shared hit=3529, temp read=206 written=206
28. 78.832 78.832 ↑ 1.0 50,938 1

Seq Scan on facturec f_1 (cost=0..4,038.38 rows=50,938 width=15) (actual time=0.085..78.832 rows=50,938 loops=1)

  • Buffers: shared hit=3529
29. 34.249 294.898 ↑ 7.2 10,007 1

Sort (cost=11,881.11..12,062.23 rows=72,448 width=14) (actual time=291.857..294.898 rows=10,007 loops=1)

  • Sort Key: h_1.id_facture
  • Sort Method: quicksort Memory: 854kB
  • Buffers: shared hit=1349 read=2401
30. 33.327 260.649 ↑ 7.2 10,007 1

Merge Join (cost=4,275.83..6,032.87 rows=72,448 width=14) (actual time=204.385..260.649 rows=10,007 loops=1)

  • Buffers: shared hit=1349 read=2401
31. 14.668 14.668 ↑ 1.0 10,007 1

Index Scan using histacom3 on histacom h_1 (cost=0.29..437.1 rows=10,477 width=13) (actual time=0.106..14.668 rows=10,007 loops=1)

  • Buffers: shared hit=1349 read=169
32. 119.483 212.654 ↓ 1.3 31,144 1

Sort (cost=4,275.55..4,337.14 rows=24,638 width=11) (actual time=204.229..212.654 rows=31,144 loops=1)

  • Sort Key: ((m_2.id_mission)::numeric)
  • Sort Method: quicksort Memory: 2601kB
  • Buffers: shared read=2232
33. 93.171 93.171 ↑ 1.0 24,638 1

Seq Scan on mission m_2 (cost=0..2,478.38 rows=24,638 width=11) (actual time=0.042..93.171 rows=24,638 loops=1)

  • Buffers: shared read=2232
34. 183.898 23,001.858 ↑ 2.1 46,863 1

Group (cost=430,402.4..445,876.45 rows=98,554 width=18) (actual time=22,187.729..23,001.858 rows=46,863 loops=1)

  • Buffers: shared hit=4388 read=20642, temp read=2949 written=2949
35. 13,718.874 22,817.960 ↑ 6.9 300,003 1

Sort (cost=430,402.4..435,560.42 rows=2,063,206 width=18) (actual time=22,187.722..22,817.96 rows=300,003 loops=1)

  • Sort Key: f_2.numerofacture, e_2.nomsociete
  • Sort Method: external merge Disk: 8416kB
  • Buffers: shared hit=4388 read=20642, temp read=2949 written=2949
36. 522.457 9,099.086 ↑ 6.9 300,003 1

Merge Join (cost=133,984.19..171,693.65 rows=2,063,206 width=18) (actual time=8,074.396..9,099.086 rows=300,003 loops=1)

  • Buffers: shared hit=4388 read=20642, temp read=1895 written=1895
37. 731.475 849.349 ↑ 1.0 50,938 1

Sort (cost=8,020.83..8,148.17 rows=50,938 width=15) (actual time=780.453..849.349 rows=50,938 loops=1)

  • Sort Key: ((f_2.id_facturedemandeur)::numeric)
  • Sort Method: external merge Disk: 1640kB
  • Buffers: shared hit=3529, temp read=206 written=206
38. 117.874 117.874 ↑ 1.0 50,938 1

Seq Scan on facturec f_2 (cost=0..4,038.38 rows=50,938 width=15) (actual time=0.109..117.874 rows=50,938 loops=1)

  • Buffers: shared hit=3529
39. 198.640 7,727.280 ↑ 1.6 300,003 1

Materialize (cost=125,963.36..128,405.97 rows=488,522 width=13) (actual time=7,293.92..7,727.28 rows=300,003 loops=1)

  • Buffers: shared hit=859 read=20642, temp read=1689 written=1689
40. 3,232.750 7,528.640 ↑ 1.6 300,003 1

Sort (cost=125,963.36..127,184.66 rows=488,522 width=13) (actual time=7,293.907..7,528.64 rows=300,003 loops=1)

  • Sort Key: h_2.id_facture
  • Sort Method: external sort Disk: 7248kB
  • Buffers: shared hit=859 read=20642, temp read=1689 written=1689
41. 563.337 4,295.890 ↑ 1.6 300,003 1

Merge Join (cost=44,459.1..71,451.76 rows=488,522 width=13) (actual time=2,997.553..4,295.89 rows=300,003 loops=1)

  • Buffers: shared hit=859 read=20642, temp read=783 written=783
42. 2,505.014 3,278.839 ↑ 1.0 222,654 1

Sort (cost=44,458.68..45,015.61 rows=222,772 width=11) (actual time=2,997.288..3,278.839 rows=222,654 loops=1)

  • Sort Key: ((e_2.id_expertise)::numeric)
  • Sort Method: external merge Disk: 6256kB
  • Buffers: shared hit=859 read=17776, temp read=783 written=783
43. 773.825 773.825 ↑ 1.0 222,772 1

Seq Scan on expertise e_2 (cost=0..20,862.72 rows=222,772 width=11) (actual time=0.082..773.825 rows=222,772 loops=1)

  • Filter: (nomsociete IS NOT NULL)
  • Buffers: shared hit=859 read=17776
44. 191.188 453.714 ↑ 1.0 300,049 1

Materialize (cost=0.42..17,523.28 rows=300,049 width=12) (actual time=0.207..453.714 rows=300,049 loops=1)

  • Buffers: shared read=2866
45. 262.526 262.526 ↑ 1.0 300,049 1

Index Only Scan using histofac1 on histofac h_2 (cost=0.42..16,773.16 rows=300,049 width=12) (actual time=0.196..262.526 rows=300,049 loops=1)

  • Buffers: shared read=2866
46. 0.281 18,420.572 ↑ 38,728.2 1,554 1

Materialize (cost=8,305,850,029.62..8,306,150,947.91 rows=60,183,657 width=24) (actual time=18,420.131..18,420.572 rows=1,554 loops=1)

  • Buffers: shared hit=9691 read=26188, temp read=7093 written=7087
47. 7.111 18,420.291 ↑ 38,728.2 1,554 1

Sort (cost=8,305,850,029.62..8,306,000,488.76 rows=60,183,657 width=24) (actual time=18,420.122..18,420.291 rows=1,554 loops=1)

  • Sort Key: c.numerofacture
  • Sort Method: quicksort Memory: 170kB
  • Buffers: shared hit=9691 read=26188, temp read=7093 written=7087
48. 707.820 18,413.180 ↑ 38,728.2 1,554 1

Merge Join (cost=18,501,872.21..8,294,370,728.47 rows=60,183,657 width=24) (actual time=17,574.941..18,413.18 rows=1,554 loops=1)

  • Filter: ((upper((m.libelle)::text) ~~ '%EXP%CENTRA%'::text) OR (upper((b.adresse1)::text) ~~ '%EXP%CENTRA%'::text))
  • Buffers: shared hit=9691 read=26188, temp read=7093 written=7087
49. 3,057.504 8,502.616 ↑ 3.4 222,654 1

Sort (cost=187,435.78..189,338.31 rows=761,010 width=52) (actual time=8,373.994..8,502.616 rows=222,654 loops=1)

  • Sort Key: ((e.id_expertise)::numeric)
  • Sort Method: external merge Disk: 13944kB
  • Buffers: shared hit=3157 read=26185, temp read=4506 written=4500
50. 469.787 5,445.112 ↑ 3.4 222,772 1

Merge Join (cost=73,022.5..87,082.4 rows=761,010 width=52) (actual time=4,251.451..5,445.112 rows=222,772 loops=1)

  • Buffers: shared hit=3157 read=26185, temp read=2762 written=2756
51. 237.090 310.018 ↑ 1.0 24,638 1

Sort (cost=4,275.55..4,337.14 rows=24,638 width=32) (actual time=293.464..310.018 rows=24,638 loops=1)

  • Sort Key: ((m.id_mission)::numeric)
  • Sort Method: quicksort Memory: 2979kB
  • Buffers: shared hit=2232
52. 72.928 72.928 ↑ 1.0 24,638 1

Seq Scan on mission m (cost=0..2,478.38 rows=24,638 width=32) (actual time=0.043..72.928 rows=24,638 loops=1)

  • Buffers: shared hit=2232
53. 130.459 4,665.307 ↑ 1.0 222,772 1

Materialize (cost=68,746.95..69,861.84 rows=222,978 width=29) (actual time=3,957.947..4,665.307 rows=222,772 loops=1)

  • Buffers: shared hit=925 read=26185, temp read=2762 written=2756
54. 2,214.311 4,534.848 ↑ 1.0 222,772 1

Sort (cost=68,746.95..69,304.39 rows=222,978 width=29) (actual time=3,957.936..4,534.848 rows=222,772 loops=1)

  • Sort Key: e.id_mission
  • Sort Method: external merge Disk: 8736kB
  • Buffers: shared hit=925 read=26185, temp read=2762 written=2756
55. 965.831 2,320.537 ↑ 1.0 222,772 1

Hash Join (cost=24,735.37..43,601.71 rows=222,978 width=29) (actual time=1,092.371..2,320.537 rows=222,772 loops=1)

  • Buffers: shared hit=925 read=26185, temp read=1666 written=1660
56. 263.641 263.641 ↑ 1.0 222,794 1

Seq Scan on bienimmobi b (cost=0..10,704.78 rows=222,978 width=24) (actual time=0.079..263.641 rows=222,794 loops=1)

  • Buffers: shared read=8475
57. 359.629 1,091.065 ↑ 1.0 222,772 1

Hash (cost=20,862.72..20,862.72 rows=222,772 width=15) (actual time=1,091.065..1,091.065 rows=222,772 loops=1)

  • Buffers: shared hit=925 read=17710, temp written=745
58. 731.436 731.436 ↑ 1.0 222,772 1

Seq Scan on expertise e (cost=0..20,862.72 rows=222,772 width=15) (actual time=0.08..731.436 rows=222,772 loops=1)

  • Buffers: shared hit=925 read=17710
59. 55.928 9,202.744 ↑ 255.1 310,067 1

Materialize (cost=18,314,436.43..18,709,875.76 rows=79,087,867 width=34) (actual time=8,989.329..9,202.744 rows=310,067 loops=1)

  • Buffers: shared hit=6534 read=3, temp read=2587 written=2587
60. 2,825.870 9,146.816 ↑ 255.1 310,067 1

Sort (cost=18,314,436.43..18,512,156.09 rows=79,087,867 width=34) (actual time=8,989.316..9,146.816 rows=310,067 loops=1)

  • Sort Key: histofac.id_expertise
  • Sort Method: external merge Disk: 11512kB
  • Buffers: shared hit=6534 read=3, temp read=2587 written=2587
61. 294.896 6,320.946 ↑ 255.1 310,067 1

Merge Join (cost=61,782.25..1,451,636.16 rows=79,087,867 width=34) (actual time=5,341.363..6,320.946 rows=310,067 loops=1)

  • Buffers: shared hit=6534 read=3, temp read=1147 written=1147
62. 471.900 561.472 ↑ 1.0 50,938 1

Sort (cost=8,020.83..8,148.17 rows=50,938 width=20) (actual time=531.103..561.472 rows=50,938 loops=1)

  • Sort Key: ((c.id_facturedemandeur)::numeric)
  • Sort Method: external merge Disk: 1888kB
  • Buffers: shared hit=3529, temp read=237 written=237
63. 89.572 89.572 ↑ 1.0 50,938 1

Seq Scan on facturec c (cost=0..4,038.38 rows=50,938 width=20) (actual time=0.11..89.572 rows=50,938 loops=1)

  • Buffers: shared hit=3529
64. 102.546 5,464.578 ↑ 1.0 310,048 1

Materialize (cost=53,761.42..59,971.94 rows=310,526 width=28) (actual time=4,810.229..5,464.578 rows=310,048 loops=1)

  • Buffers: shared hit=3005 read=3, temp read=910 written=910
65. 236.233 5,362.032 ↑ 1.0 310,048 1

Unique (cost=53,761.42..56,090.36 rows=310,526 width=12) (actual time=4,810.218..5,362.032 rows=310,048 loops=1)

  • Buffers: shared hit=3005 read=3, temp read=910 written=910
66. 4,713.488 5,125.799 ↑ 1.0 310,056 1

Sort (cost=53,761.42..54,537.73 rows=310,526 width=12) (actual time=4,810.214..5,125.799 rows=310,056 loops=1)

  • Sort Key: histofac.id_facture, histofac.id_expertise
  • Sort Method: external merge Disk: 7272kB
  • Buffers: shared hit=3005 read=3, temp read=910 written=910
67. 140.796 412.311 ↑ 1.0 310,056 1

Append (cost=0.42..20,125.19 rows=310,526 width=12) (actual time=0.075..412.311 rows=310,056 loops=1)

  • Buffers: shared hit=3005 read=3
68. 265.135 265.135 ↑ 1.0 300,049 1

Index Only Scan using histofac1 on histofac histofac (cost=0.42..16,773.16 rows=300,049 width=12) (actual time=0.072..265.135 rows=300,049 loops=1)

  • Buffers: shared hit=2866
69. 6.380 6.380 ↑ 1.0 10,007 1

Seq Scan on histacom histacom (cost=0..246.77 rows=10,477 width=13) (actual time=0.095..6.38 rows=10,007 loops=1)

  • Buffers: shared hit=139 read=3