explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LgtH

Settings
# exclusive inclusive rows x rows loops node
1. 0.061 16,242.757 ↓ 11.1 278 1

Unique (cost=1,212,324.79..1,212,325.23 rows=25 width=68) (actual time=16,242.683..16,242.757 rows=278 loops=1)

2. 0.130 16,242.696 ↓ 11.1 278 1

Sort (cost=1,212,324.79..1,212,324.86 rows=25 width=68) (actual time=16,242.683..16,242.696 rows=278 loops=1)

  • Sort Key: commandeve0_.datecommandevente, commandeve0_.id, commandeve0_.numerocommandevente, cliententi2_1_.abrege, (min(listeligne1_.datelivraisonacceptee)), commandeve0_2_.reference
  • Sort Method: quicksort Memory: 64kB
3. 1.400 16,242.566 ↓ 11.1 278 1

HashAggregate (cost=1,212,323.96..1,212,324.21 rows=25 width=68) (actual time=16,242.506..16,242.566 rows=278 loops=1)

  • Group Key: commandeve0_.datecommandevente, commandeve0_.id, commandeve0_.numerocommandevente, cliententi2_1_.abrege, commandeve0_2_.reference
4. 1.611 16,241.166 ↓ 18.8 469 1

Nested Loop (cost=1,071,439.90..1,212,323.59 rows=25 width=68) (actual time=13,418.461..16,241.166 rows=469 loops=1)

  • Join Filter: ((listeligne1_.id)::text = (listeligne1_1_.id)::text)
5. 460.280 16,236.272 ↓ 18.8 469 1

Hash Join (cost=1,071,439.62..1,212,315.21 rows=25 width=134) (actual time=13,418.416..16,236.272 rows=469 loops=1)

  • Hash Cond: (((commandeve0_.id)::text = (commandeve0_1_.id)::text) AND ((cliententi2_.id)::text = (commandeve0_1_.id_client)::text) AND ((adresseent7_.id)::text = (commandeve0_1_.id_adresseemetteur)::text))
6. 2,358.721 15,774.683 ↓ 1.3 2,380,175 1

Hash Join (cost=1,071,265.96..1,192,004.22 rows=1,789,963 width=296) (actual time=13,413.107..15,774.683 rows=2,380,175 loops=1)

  • Hash Cond: ((listeligne1_2_.id_article)::text = (articleent3_.id)::text)
7. 0.717 9.192 ↓ 1.1 1,345 1

Hash Join (cost=472.66..961.57 rows=1,275 width=217) (actual time=4.034..9.192 rows=1,345 loops=1)

  • Hash Cond: ((listeligne1_.id_commandevente)::text = (commandeve0_.id)::text)
8. 1.754 5.779 ↑ 1.0 1,356 1

Hash Join (cost=96.82..567.87 rows=1,363 width=136) (actual time=1.329..5.779 rows=1,356 loops=1)

  • Hash Cond: ((listeligne1_2_.id)::text = (listeligne1_.id)::text)
9. 2.717 2.717 ↓ 1.0 7,093 1

Seq Scan on dm1_lignedocumentcommercial listeligne1_2_ (cost=0.00..430.84 rows=7,089 width=66) (actual time=0.006..2.717 rows=7,093 loops=1)

  • Filter: ((datefinvalidite IS NULL) AND (quantite_valeur > '0'::numeric))
  • Rows Removed by Filter: 903
10. 0.278 1.308 ↑ 1.0 1,482 1

Hash (cost=77.62..77.62 rows=1,536 width=70) (actual time=1.308..1.308 rows=1,482 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 164kB
11. 1.030 1.030 ↑ 1.0 1,482 1

Seq Scan on dm2_lignecommandevente listeligne1_ (cost=0.00..77.62 rows=1,536 width=70) (actual time=0.005..1.030 rows=1,482 loops=1)

  • Filter: ((NOT etatproduction) AND statutrevue AND ((id)::text !~~ 'DefaultRecord_%'::text) AND ((etatlivraison)::text <> '2'::text))
  • Rows Removed by Filter: 728
12. 0.261 2.696 ↓ 1.0 1,259 1

Hash (cost=360.52..360.52 rows=1,226 width=81) (actual time=2.696..2.696 rows=1,259 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 159kB
13. 0.989 2.435 ↓ 1.0 1,259 1

Hash Join (cost=66.70..360.52 rows=1,226 width=81) (actual time=0.640..2.435 rows=1,259 loops=1)

  • Hash Cond: ((commandeve0_2_.id)::text = (commandeve0_.id)::text)
14. 0.815 0.815 ↓ 1.0 4,726 1

Seq Scan on dm1_documentcommercial commandeve0_2_ (cost=0.00..263.88 rows=4,715 width=36) (actual time=0.002..0.815 rows=4,726 loops=1)

  • Filter: (datefinvalidite IS NULL)
  • Rows Removed by Filter: 273
15. 0.213 0.631 ↓ 1.0 1,298 1

Hash (cost=50.48..50.48 rows=1,297 width=45) (actual time=0.631..0.631 rows=1,298 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 118kB
16. 0.418 0.418 ↓ 1.0 1,298 1

Seq Scan on dm2_commandevente commandeve0_ (cost=0.00..50.48 rows=1,297 width=45) (actual time=0.004..0.418 rows=1,298 loops=1)

  • Filter: (((id)::text !~~ 'DefaultRecord_%'::text) AND (datecommandevente <= '2019-07-03'::date))
  • Rows Removed by Filter: 9
17. 1,626.590 13,406.770 ↑ 1.0 4,420,325 1

Hash (cost=920,583.14..920,583.14 rows=4,550,733 width=144) (actual time=13,406.770..13,406.770 rows=4,420,325 loops=1)

  • Buckets: 262144 (originally 262144) Batches: 64 (originally 32) Memory Usage: 30721kB
18. 3,418.459 11,780.180 ↑ 1.0 4,420,325 1

Hash Semi Join (cost=16.41..920,583.14 rows=4,550,733 width=144) (actual time=58.210..11,780.180 rows=4,420,325 loops=1)

  • Hash Cond: ((adresseent7_.id_etablissement)::text = (etablissem10_.id)::text)
19. 5,884.178 8,361.672 ↑ 1.0 56,397,250 1

Nested Loop (cost=10.96..719,188.12 rows=57,433,398 width=156) (actual time=0.083..8,361.672 rows=56,397,250 loops=1)

20. 10.804 16.994 ↑ 1.1 64,750 1

Nested Loop (cost=10.96..934.49 rows=70,818 width=124) (actual time=0.074..16.994 rows=64,750 loops=1)

21. 0.640 0.640 ↑ 1.1 370 1

Seq Scan on dm1_adresse adresseent7_ (cost=0.00..14.07 rows=407 width=44) (actual time=0.001..0.640 rows=370 loops=1)

22. 5.291 5.550 ↓ 1.0 175 370

Materialize (cost=10.96..35.62 rows=174 width=80) (actual time=0.000..0.015 rows=175 loops=370)

23. 0.084 0.259 ↓ 1.0 175 1

Hash Join (cost=10.96..34.75 rows=174 width=80) (actual time=0.067..0.259 rows=175 loops=1)

  • Hash Cond: ((cliententi2_1_.id)::text = (cliententi2_.id)::text)
24. 0.116 0.116 ↑ 1.0 307 1

Seq Scan on dm1_tiers cliententi2_1_ (cost=0.00..20.90 rows=308 width=48) (actual time=0.005..0.116 rows=307 loops=1)

  • Filter: ((statutcomptable)::text <> '2'::text)
  • Rows Removed by Filter: 6
25. 0.026 0.059 ↓ 1.0 177 1

Hash (cost=8.76..8.76 rows=176 width=32) (actual time=0.059..0.059 rows=177 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
26. 0.033 0.033 ↓ 1.0 177 1

Seq Scan on dm2_client cliententi2_ (cost=0.00..8.76 rows=176 width=32) (actual time=0.003..0.033 rows=177 loops=1)

27. 2,459.040 2,460.500 ↓ 1.1 871 64,750

Materialize (cost=0.00..338.19 rows=811 width=32) (actual time=0.000..0.038 rows=871 loops=64,750)

28. 1.460 1.460 ↓ 1.1 871 1

Seq Scan on dm1_article articleent3_ (cost=0.00..334.13 rows=811 width=32) (actual time=0.007..1.460 rows=871 loops=1)

  • Filter: (((typearticle)::text <> '4'::text) AND ((origine)::text = '2'::text) AND ((strategieproduction)::text = '2'::text))
  • Rows Removed by Filter: 1311
29. 0.003 0.049 ↑ 1.0 3 1

Hash (cost=5.41..5.41 rows=3 width=22) (actual time=0.049..0.049 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.004 0.046 ↑ 1.0 3 1

HashAggregate (cost=5.35..5.38 rows=3 width=22) (actual time=0.046..0.046 rows=3 loops=1)

  • Group Key: etablissem10_.id
31. 0.005 0.042 ↑ 1.0 3 1

Hash Join (cost=1.07..5.34 rows=3 width=22) (actual time=0.033..0.042 rows=3 loops=1)

  • Hash Cond: ((listeetabl9_.id_etablissement)::text = (etablissem10_.id)::text)
32. 0.009 0.033 ↑ 1.0 3 1

Nested Loop (cost=0.00..4.23 rows=3 width=19) (actual time=0.024..0.033 rows=3 loops=1)

33. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on dm1_utilisateur utilisateu8_ (cost=0.00..2.46 rows=1 width=30) (actual time=0.012..0.012 rows=1 loops=1)

  • Filter: (((id)::text = '402881973205e5cc013205f2cdbf0036'::text) AND (dtype = 'N'::bpchar))
  • Rows Removed by Filter: 30
34. 0.012 0.012 ↑ 1.0 3 1

Seq Scan on dm1_utilisateur_etablissement listeetabl9_ (cost=0.00..1.74 rows=3 width=49) (actual time=0.004..0.012 rows=3 loops=1)

  • Filter: ((id_utilisateur)::text = '402881973205e5cc013205f2cdbf0036'::text)
  • Rows Removed by Filter: 56
35. 0.002 0.004 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=22) (actual time=0.004..0.004 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.002 0.002 ↑ 1.0 3 1

Seq Scan on dm1_etablissement etablissem10_ (cost=0.00..1.03 rows=3 width=22) (actual time=0.001..0.002 rows=3 loops=1)

37. 0.727 1.309 ↓ 1.0 3,044 1

Hash (cost=120.42..120.42 rows=3,042 width=99) (actual time=1.309..1.309 rows=3,044 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 422kB
38. 0.582 0.582 ↓ 1.0 3,052 1

Seq Scan on dm2_documentcommercialvente commandeve0_1_ (cost=0.00..120.42 rows=3,042 width=99) (actual time=0.003..0.582 rows=3,052 loops=1)

39. 3.283 3.283 ↑ 1.0 1 469

Index Only Scan using dm2_lignedocumentcommercialvente_pkey on dm2_lignedocumentcommercialvente listeligne1_1_ (cost=0.28..0.32 rows=1 width=33) (actual time=0.007..0.007 rows=1 loops=469)

  • Index Cond: (id = (listeligne1_2_.id)::text)
  • Heap Fetches: 25
Planning time : 10.043 ms
Execution time : 16,243.081 ms