explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eSfr

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.005 881.305 ↑ 1.0 30 1

Limit (cost=39,121.84..39,121.92 rows=30 width=694) (actual time=881.300..881.305 rows=30 loops=1)

2. 176.895 881.300 ↑ 9,564.7 30 1

Sort (cost=39,121.84..39,839.19 rows=286,940 width=694) (actual time=881.298..881.300 rows=30 loops=1)

  • Sort Key: articleden.articleden_lib, article.article_id
  • Sort Method: top-N heapsort Memory: 28kB
3. 128.795 704.405 ↓ 1.3 366,112 1

Hash Left Join (cost=14,317.44..30,647.23 rows=286,940 width=694) (actual time=164.821..704.405 rows=366,112 loops=1)

  • Hash Cond: (article.article_id = tarifvente.tarifvente_article_id)
  • Join Filter: ((tarifvente.tarifvente_profiltarif_id = profiltarif.profiltarif_id) OR ((tarifvente.tarifvente_profiltarif_id IS NULL) AND (profiltarif.profiltarif_id IS NULL) AND (tarifvente.tarifvente_dossier_id = article.article_dossier_id)))
4. 150.123 562.146 ↓ 1.3 366,112 1

Nested Loop (cost=12,419.74..27,673.46 rows=286,940 width=630) (actual time=151.337..562.146 rows=366,112 loops=1)

5. 28.405 412.023 ↓ 1.3 91,528 1

Hash Left Join (cost=12,415.81..24,082.69 rows=71,735 width=110) (actual time=151.270..412.023 rows=91,528 loops=1)

  • Hash Cond: ((article.article_dossier_id = _produit_classif4__.classif_dossier_id) AND (produit.produit_classif4_id = _produit_classif4__.classif_id))
6. 28.621 383.072 ↓ 1.3 91,528 1

Hash Left Join (cost=12,337.18..22,910.13 rows=71,735 width=94) (actual time=150.708..383.072 rows=91,528 loops=1)

  • Hash Cond: ((article.article_dossier_id = _produit_classif3__.classif_dossier_id) AND (produit.produit_classif3_id = _produit_classif3__.classif_id))
7. 85.734 353.804 ↓ 1.3 91,528 1

Hash Left Join (cost=12,258.55..21,737.58 rows=71,735 width=78) (actual time=150.030..353.804 rows=91,528 loops=1)

  • Hash Cond: ((article.article_dossier_id = articleden.articleden_dossier_id) AND (article.article_id = articleden.articleden_article_id))
8. 71.858 185.108 ↓ 1.3 91,528 1

Hash Join (cost=5,735.31..12,193.40 rows=71,735 width=39) (actual time=66.761..185.108 rows=91,528 loops=1)

  • Hash Cond: (article.article_produit_id = produit.produit_id)
9. 47.335 47.335 ↓ 1.0 91,528 1

Seq Scan on article (cost=0.00..3,457.76 rows=90,244 width=35) (actual time=0.014..47.335 rows=91,528 loops=1)

  • Filter: (article_dossier_id = 185)
  • Rows Removed by Filter: 22319
10. 25.283 65.915 ↑ 1.0 91,198 1

Hash (cost=4,136.94..4,136.94 rows=91,950 width=16) (actual time=65.915..65.915 rows=91,198 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2804kB
11. 40.632 40.632 ↑ 1.0 91,198 1

Seq Scan on produit (cost=0.00..4,136.94 rows=91,950 width=16) (actual time=0.010..40.632 rows=91,198 loops=1)

  • Filter: (produit_dossier_id = 185)
  • Rows Removed by Filter: 23317
12. 31.870 82.962 ↓ 1.0 91,528 1

Hash (cost=4,345.46..4,345.46 rows=91,518 width=47) (actual time=82.962..82.962 rows=91,528 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3153kB
13. 51.092 51.092 ↓ 1.0 91,528 1

Seq Scan on articleden (cost=0.00..4,345.46 rows=91,518 width=47) (actual time=0.017..51.092 rows=91,528 loops=1)

  • Filter: (articleden_dossier_id = 185)
  • Rows Removed by Filter: 2974
14. 0.217 0.647 ↑ 1.0 741 1

Hash (cost=66.99..66.99 rows=776 width=24) (actual time=0.647..0.647 rows=741 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
15. 0.330 0.430 ↑ 1.0 741 1

Bitmap Heap Scan on classif _produit_classif3__ (cost=22.29..66.99 rows=776 width=24) (actual time=0.113..0.430 rows=741 loops=1)

  • Recheck Cond: (classif_dossier_id = 185)
  • Heap Blocks: exact=22
16. 0.100 0.100 ↓ 1.4 1,092 1

Bitmap Index Scan on classif_dossier_id_sk (cost=0.00..22.10 rows=776 width=0) (actual time=0.100..0.100 rows=1,092 loops=1)

  • Index Cond: (classif_dossier_id = 185)
17. 0.206 0.546 ↑ 1.0 741 1

Hash (cost=66.99..66.99 rows=776 width=24) (actual time=0.546..0.546 rows=741 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
18. 0.265 0.340 ↑ 1.0 741 1

Bitmap Heap Scan on classif _produit_classif4__ (cost=22.29..66.99 rows=776 width=24) (actual time=0.080..0.340 rows=741 loops=1)

  • Recheck Cond: (classif_dossier_id = 185)
  • Heap Blocks: exact=22
19. 0.075 0.075 ↓ 1.4 1,092 1

Bitmap Index Scan on classif_dossier_id_sk (cost=0.00..22.10 rows=776 width=0) (actual time=0.075..0.075 rows=1,092 loops=1)

  • Index Cond: (classif_dossier_id = 185)
20. 0.000 0.000 ↑ 1.0 4 91,528

Materialize (cost=3.93..4.03 rows=4 width=520) (actual time=0.000..0.000 rows=4 loops=91,528)

21. 0.003 0.064 ↑ 1.0 4 1

Subquery Scan on profiltarif (cost=3.93..4.01 rows=4 width=520) (actual time=0.058..0.064 rows=4 loops=1)

22. 0.021 0.061 ↑ 1.0 4 1

HashAggregate (cost=3.93..3.97 rows=4 width=592) (actual time=0.057..0.061 rows=4 loops=1)

  • Group Key: profiltarif_1.profiltarif_id, profiltarif_1.profiltarif_code, profiltarif_1.profiltarif_lib, profiltarif_1.profiltarif_datec, profiltarif_1.profiltarif_datem, profiltarif_1.profiltarif_utic_id, profiltarif_1.profiltarif_utim_id, profiltarif_1.profiltarif_dossier_id, profiltarif_1.profiltarif_pays_id, profiltarif_1.profiltarif_rev, profiltarif_1.profiltarif_idechange, profiltarif_1.profiltarif_devise_id, profiltarif_1.profiltarif_profiltarifachat_id
23. 0.002 0.040 ↑ 1.0 4 1

Append (cost=0.00..3.80 rows=4 width=592) (actual time=0.013..0.040 rows=4 loops=1)

24. 0.014 0.014 ↑ 1.0 3 1

Seq Scan on profiltarif profiltarif_1 (cost=0.00..1.06 rows=3 width=66) (actual time=0.012..0.014 rows=3 loops=1)

  • Filter: (profiltarif_dossier_id = 185)
  • Rows Removed by Filter: 6
25. 0.001 0.024 ↑ 1.0 1 1

Limit (cost=0.00..2.70 rows=1 width=66) (actual time=0.024..0.024 rows=1 loops=1)

26. 0.002 0.023 ↑ 1.0 1 1

Nested Loop Anti Join (cost=0.00..2.70 rows=1 width=66) (actual time=0.023..0.023 rows=1 loops=1)

27. 0.007 0.007 ↑ 28.0 1 1

Seq Scan on dossier (cost=0.00..1.28 rows=28 width=0) (actual time=0.007..0.007 rows=1 loops=1)

28. 0.009 0.014 ↓ 0.0 0 1

Materialize (cost=0.00..1.07 rows=1 width=66) (actual time=0.014..0.014 rows=0 loops=1)

29. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on profiltarif profiltarif_2 (cost=0.00..1.06 rows=1 width=66) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: (profiltarif_id = 0)
  • Rows Removed by Filter: 9
30. 0.001 13.464 ↓ 0.0 0 1

Hash (cost=1,897.69..1,897.69 rows=1 width=76) (actual time=13.464..13.464 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
31. 13.463 13.463 ↓ 0.0 0 1

Seq Scan on tarifvente (cost=0.00..1,897.69 rows=1 width=76) (actual time=13.463..13.463 rows=0 loops=1)

  • Filter: (('2019-09-11 14:16:25'::timestamp without time zone >= tarifvente_debut) AND ('2019-09-11 14:16:25'::timestamp without time zone <= tarifvente_fin) AND (tarifvente_client_id = 1091161))
  • Rows Removed by Filter: 44226
Planning time : 5.081 ms
Execution time : 881.736 ms