explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PinR

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 146,788.994 ↑ 1.0 10 1

Limit (cost=2,042,782.93..2,042,783.61 rows=10 width=1,063) (actual time=146,788.952..146,788.994 rows=10 loops=1)

2.          

CTE user_comment_

3. 367.836 1,817.969 ↑ 1.0 601,167 1

WindowAgg (cost=98,417.41..110,440.75 rows=601,167 width=50) (actual time=1,180.562..1,817.969 rows=601,167 loops=1)

4. 990.761 1,450.133 ↑ 1.0 601,167 1

Sort (cost=98,417.41..99,920.32 rows=601,167 width=50) (actual time=1,180.469..1,450.133 rows=601,167 loops=1)

  • Sort Key: user_comment.id_client, user_comment.id_user_comment
  • Sort Method: external merge Disk: 35448kB
5. 459.372 459.372 ↑ 1.0 601,167 1

Seq Scan on user_comment (cost=0.00..20,164.67 rows=601,167 width=50) (actual time=5.229..459.372 rows=601,167 loops=1)

6. 0.039 146,788.983 ↑ 93,553.1 10 1

Unique (cost=1,932,342.18..1,995,490.53 rows=935,531 width=1,063) (actual time=146,788.945..146,788.983 rows=10 loops=1)

7. 12,816.099 146,788.944 ↑ 93,553.1 10 1

Sort (cost=1,932,342.18..1,934,681.01 rows=935,531 width=1,063) (actual time=146,788.942..146,788.944 rows=10 loops=1)

  • Sort Key: v.id_vente, v.id_client, uc.summary, c.reference, c.external_client_number, v.external_reference, ((((((((ams.numero)::text || ', '::text) || (ams.adresse)::text) || ' '::text) || (ams.code_postal)::text) || ' '::text) || (ams.ville)::text)), (CASE WHEN ((va79.valeur)::text = 'VOO'::text) THEN (v.reference)::text WHEN ((va79.valeur)::text = 'TELENET'::text) THEN (ams.location_id)::text ELSE NULL::text END), (COALESCE(plg.nom_commercial, p.produit)), (COALESCE(plg.id_langue, 'en'::character varying)), ((((c.nom)::text || ' '::text) || (c.prenom)::text)), v.id_produit, v.id_statut_vente, (CASE WHEN ((vi.cablo_date IS NOT NULL) AND (vi.cablo_date <> ''::text)) THEN ("substring"(vi.cablo_date, 1, 10))::date ELSE NULL::date END), (("substring"(vi.orange_date, 0, 11))::date), sv.statut_fr, sv.statut_en, sv.statut_nl, sv.statut_de, u.login, u.id_entite, c.langue, ((v.date_creation)::date), va206.valeur, va79.valeur, (CASE WHEN (v.id_vente_lie IS NOT NULL) THEN 'Move Or (...)
  • Sort Method: external merge Disk: 266672kB
8. 1,936.851 133,972.845 ↓ 1.1 1,053,819 1

Hash Left Join (cost=584,603.28..969,801.91 rows=935,531 width=1,063) (actual time=68,040.569..133,972.845 rows=1,053,819 loops=1)

  • Hash Cond: (v.id_vente = vi.id_vente)
9. 642.152 129,414.716 ↓ 1.1 1,053,819 1

Hash Left Join (cost=569,331.26..903,024.69 rows=935,531 width=999) (actual time=65,418.888..129,414.716 rows=1,053,819 loops=1)

  • Hash Cond: (v.id_utilisateur_creation = u.id_utilisateur)
10. 4,140.445 128,766.264 ↓ 1.1 1,053,819 1

Hash Right Join (cost=569,147.34..885,299.56 rows=935,531 width=990) (actual time=65,412.411..128,766.264 rows=1,053,819 loops=1)

  • Hash Cond: (v_1.id_vente = v.id_vente)
11. 973.871 113,574.923 ↓ 1.0 426,460 1

Hash Right Join (cost=223,294.39..407,854.85 rows=426,459 width=38) (actual time=54,351.232..113,574.923 rows=426,460 loops=1)

  • Hash Cond: (va206.id_vente = v_1.id_vente)
12. 58,281.298 58,416.943 ↓ 1.0 405,538 1

Bitmap Heap Scan on vente_attribut va206 (cost=7,288.73..176,517.72 rows=389,199 width=21) (actual time=166.139..58,416.943 rows=405,538 loops=1)

  • Recheck Cond: (id_attribut = 206)
  • Rows Removed by Index Recheck: 7016038
  • Heap Blocks: exact=82008 lossy=66093
13. 135.645 135.645 ↓ 1.0 405,538 1

Bitmap Index Scan on ix_relationship41 (cost=0.00..7,191.43 rows=389,199 width=0) (actual time=135.645..135.645 rows=405,538 loops=1)

  • Index Cond: (id_attribut = 206)
14. 489.642 54,184.109 ↓ 1.0 426,460 1

Hash (cost=208,175.92..208,175.92 rows=426,459 width=21) (actual time=54,184.109..54,184.109 rows=426,460 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 4420kB
15. 1,203.797 53,694.467 ↓ 1.0 426,460 1

Hash Right Join (cost=24,714.56..208,175.92 rows=426,459 width=21) (actual time=420.132..53,694.467 rows=426,460 loops=1)

  • Hash Cond: (va79.id_vente = v_1.id_vente)
16. 52,097.911 52,242.915 ↓ 1.1 411,850 1

Bitmap Heap Scan on vente_attribut va79 (cost=7,179.24..176,334.85 rows=383,329 width=21) (actual time=172.177..52,242.915 rows=411,850 loops=1)

  • Recheck Cond: (id_attribut = 79)
  • Rows Removed by Index Recheck: 7012263
  • Heap Blocks: exact=81761 lossy=66095
17. 145.004 145.004 ↓ 1.1 411,850 1

Bitmap Index Scan on ix_relationship41 (cost=0.00..7,083.40 rows=383,329 width=0) (actual time=145.004..145.004 rows=411,850 loops=1)

  • Index Cond: (id_attribut = 79)
18. 132.432 247.755 ↑ 1.0 426,459 1

Hash (cost=10,538.59..10,538.59 rows=426,459 width=4) (actual time=247.755..247.755 rows=426,459 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 7530kB
19. 115.323 115.323 ↑ 1.0 426,459 1

Seq Scan on vente v_1 (cost=0.00..10,538.59 rows=426,459 width=4) (actual time=0.026..115.323 rows=426,459 loops=1)

20. 1,617.804 11,050.896 ↓ 1.1 1,053,818 1

Hash (cost=221,784.82..221,784.82 rows=935,531 width=956) (actual time=11,050.896..11,050.896 rows=1,053,818 loops=1)

  • Buckets: 2048 Batches: 128 Memory Usage: 2431kB
21. 349.861 9,433.092 ↓ 1.1 1,053,818 1

Hash Left Join (cost=176,770.14..221,784.82 rows=935,531 width=956) (actual time=7,019.456..9,433.092 rows=1,053,818 loops=1)

  • Hash Cond: (v.id_produit = plg.id_produit)
22. 160.017 9,082.833 ↓ 1.0 424,282 1

Hash Left Join (cost=176,767.86..209,776.70 rows=424,085 width=924) (actual time=7,018.937..9,082.833 rows=424,282 loops=1)

  • Hash Cond: (v.id_produit = p.id_produit)
23. 183.252 8,920.520 ↓ 1.0 424,282 1

Hash Join (cost=176,766.43..205,066.51 rows=424,085 width=408) (actual time=7,016.471..8,920.520 rows=424,282 loops=1)

  • Hash Cond: (v.id_statut_vente = sv.id_statut_vente)
24. 651.952 8,737.029 ↓ 1.0 424,282 1

Hash Right Join (cost=176,759.97..199,228.88 rows=424,085 width=357) (actual time=7,016.087..8,737.029 rows=424,282 loops=1)

  • Hash Cond: (uc.id_client = c.id_client)
25. 2,249.839 2,249.839 ↓ 49.6 148,960 1

CTE Scan on user_comment_ uc (cost=0.00..13,526.26 rows=3,006 width=222) (actual time=1,180.660..2,249.839 rows=148,960 loops=1)

  • Filter: (num = 1)
  • Rows Removed by Filter: 452207
26. 481.108 5,835.238 ↓ 1.0 424,282 1

Hash (cost=162,760.91..162,760.91 rows=424,085 width=143) (actual time=5,835.238..5,835.238 rows=424,282 loops=1)

  • Buckets: 8192 Batches: 8 Memory Usage: 9248kB
27. 926.559 5,354.130 ↓ 1.0 424,282 1

Hash Left Join (cost=92,897.77..162,760.91 rows=424,085 width=143) (actual time=2,692.576..5,354.130 rows=424,282 loops=1)

  • Hash Cond: (sa.id_address_manager = ams.id_adresse_manager)
28. 816.998 3,503.357 ↓ 1.0 424,282 1

Hash Join (cost=62,639.99..106,033.53 rows=424,085 width=96) (actual time=1,767.998..3,503.357 rows=424,282 loops=1)

  • Hash Cond: (v.id_client = c.id_client)
29. 741.454 1,546.333 ↓ 1.0 424,282 1

Hash Right Join (cost=21,699.95..44,663.79 rows=424,085 width=52) (actual time=627.709..1,546.333 rows=424,282 loops=1)

  • Hash Cond: (sa.id_sale = v.id_vente)
30. 179.025 179.025 ↑ 1.0 426,386 1

Seq Scan on sale_address sa (cost=0.00..7,399.86 rows=426,386 width=8) (actual time=1.127..179.025 rows=426,386 loops=1)

31. 229.631 625.854 ↓ 1.0 424,282 1

Hash (cost=12,670.88..12,670.88 rows=424,085 width=48) (actual time=625.854..625.854 rows=424,282 loops=1)

  • Buckets: 16384 Batches: 4 Memory Usage: 8534kB
32. 396.223 396.223 ↓ 1.0 424,282 1

Seq Scan on vente v (cost=0.00..12,670.88 rows=424,085 width=48) (actual time=0.016..396.223 rows=424,282 loops=1)

  • Filter: (id_statut_vente <> ALL ('{-21,-22,-23,-24}'::integer[]))
  • Rows Removed by Filter: 2177
33. 257.960 1,140.026 ↑ 1.0 416,802 1

Hash (cost=32,066.02..32,066.02 rows=416,802 width=44) (actual time=1,140.026..1,140.026 rows=416,802 loops=1)

  • Buckets: 16384 Batches: 4 Memory Usage: 8231kB
34. 882.066 882.066 ↑ 1.0 416,802 1

Seq Scan on client c (cost=0.00..32,066.02 rows=416,802 width=44) (actual time=3.931..882.066 rows=416,802 loops=1)

35. 355.601 924.214 ↑ 1.0 623,768 1

Hash (cost=16,368.68..16,368.68 rows=623,768 width=55) (actual time=924.214..924.214 rows=623,768 loops=1)

  • Buckets: 16384 Batches: 8 Memory Usage: 6194kB
36. 568.613 568.613 ↑ 1.0 623,768 1

Seq Scan on adresse_manager ams (cost=0.00..16,368.68 rows=623,768 width=55) (actual time=0.011..568.613 rows=623,768 loops=1)

37. 0.143 0.239 ↑ 1.0 154 1

Hash (cost=4.54..4.54 rows=154 width=55) (actual time=0.239..0.239 rows=154 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
38. 0.096 0.096 ↑ 1.0 154 1

Seq Scan on statut_vente sv (cost=0.00..4.54 rows=154 width=55) (actual time=0.007..0.096 rows=154 loops=1)

39. 0.027 2.296 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=520) (actual time=2.296..2.296 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
40. 2.269 2.269 ↑ 1.0 19 1

Seq Scan on produit p (cost=0.00..1.19 rows=19 width=520) (actual time=2.264..2.269 rows=19 loops=1)

41. 0.034 0.398 ↑ 1.0 57 1

Hash (cost=1.57..1.57 rows=57 width=36) (actual time=0.398..0.398 rows=57 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
42. 0.364 0.364 ↑ 1.0 57 1

Seq Scan on produit_lg plg (cost=0.00..1.57 rows=57 width=36) (actual time=0.355..0.364 rows=57 loops=1)

43. 1.885 6.300 ↑ 1.0 4,663 1

Hash (cost=125.63..125.63 rows=4,663 width=17) (actual time=6.300..6.300 rows=4,663 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 221kB
44. 4.415 4.415 ↑ 1.0 4,663 1

Seq Scan on utilisateur u (cost=0.00..125.63 rows=4,663 width=17) (actual time=0.614..4.415 rows=4,663 loops=1)

45. 0.001 2,621.278 ↓ 0.0 0 1

Hash (cost=15,242.95..15,242.95 rows=2,325 width=68) (actual time=2,621.278..2,621.278 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
46. 0.007 2,621.277 ↓ 0.0 0 1

Subquery Scan on vi (cost=15,208.08..15,242.95 rows=2,325 width=68) (actual time=2,621.277..2,621.277 rows=0 loops=1)

47. 0.002 2,621.270 ↓ 0.0 0 1

Unique (cost=15,208.08..15,219.70 rows=2,325 width=43) (actual time=2,621.270..2,621.270 rows=0 loops=1)

48. 0.076 2,621.268 ↓ 0.0 0 1

Sort (cost=15,208.08..15,213.89 rows=2,325 width=43) (actual time=2,621.268..2,621.268 rows=0 loops=1)

  • Sort Key: v_1_1.id_vente
  • Sort Method: quicksort Memory: 25kB
49. 4.548 2,621.192 ↓ 0.0 0 1

Nested Loop (cost=40.26..15,078.07 rows=2,325 width=43) (actual time=2,621.192..2,621.192 rows=0 loops=1)

50. 2.928 2,449.198 ↓ 3.3 2,887 1

Nested Loop (cost=39.83..13,997.90 rows=871 width=38) (actual time=58.356..2,449.198 rows=2,887 loops=1)

51. 10.235 1,281.889 ↓ 2.1 4,029 1

Nested Loop (cost=39.41..11,667.64 rows=1,879 width=21) (actual time=47.215..1,281.889 rows=4,029 loops=1)

52. 1,065.427 1,078.262 ↓ 2.1 4,029 1

Bitmap Heap Scan on vente_installation move (cost=38.99..6,641.39 rows=1,879 width=17) (actual time=17.844..1,078.262 rows=4,029 loops=1)

  • Recheck Cond: (id_installation = 2)
  • Heap Blocks: exact=3624
53. 12.835 12.835 ↓ 2.1 4,029 1

Bitmap Index Scan on ix_relationship47 (cost=0.00..38.52 rows=1,879 width=0) (actual time=12.835..12.835 rows=4,029 loops=1)

  • Index Cond: (id_installation = 2)
54. 193.392 193.392 ↑ 1.0 1 4,029

Index Only Scan using vente_pkey on vente v_1_1 (cost=0.42..2.66 rows=1 width=4) (actual time=0.048..0.048 rows=1 loops=4,029)

  • Index Cond: (id_vente = move.id_vente)
  • Heap Fetches: 0
55. 1,164.381 1,164.381 ↑ 1.0 1 4,029

Index Scan using ix_relationship48 on vente_installation cablo (cost=0.42..1.23 rows=1 width=17) (actual time=0.288..0.289 rows=1 loops=4,029)

  • Index Cond: (id_vente = v_1_1.id_vente)
  • Filter: (id_installation = ANY ('{7,13}'::integer[]))
  • Rows Removed by Filter: 1
56. 167.446 167.446 ↓ 0.0 0 2,887

Index Scan using ix_relationship48 on vente_installation orange (cost=0.42..1.23 rows=1 width=17) (actual time=0.058..0.058 rows=0 loops=2,887)

  • Index Cond: (id_vente = v_1_1.id_vente)
  • Filter: (id_installation = 8)
  • Rows Removed by Filter: 2