explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LtiB : Optimization for: plan #eSfr

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 39.004 404.073 ↑ 1.0 1 1

Aggregate (cost=20,135.12..20,135.13 rows=1 width=8) (actual time=404.073..404.073 rows=1 loops=1)

2. 75.964 365.069 ↓ 1.3 366,112 1

Hash Left Join (cost=7,750.76..19,417.77 rows=286,940 width=0) (actual time=81.388..365.069 rows=366,112 loops=1)

  • Hash Cond: ((article.article_dossier_id = tarifvente.tarifvente_dossier_id) AND (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)))
3. 110.764 274.365 ↓ 1.3 366,112 1

Nested Loop (cost=5,739.24..15,254.16 rows=286,940 width=12) (actual time=66.618..274.365 rows=366,112 loops=1)

4. 54.312 163.601 ↓ 1.3 91,528 1

Hash Join (cost=5,735.31..11,663.40 rows=71,735 width=8) (actual time=66.553..163.601 rows=91,528 loops=1)

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

Seq Scan on article (cost=0.00..3,457.76 rows=90,244 width=12) (actual time=0.015..43.357 rows=91,528 loops=1)

  • Filter: (article_dossier_id = 185)
  • Rows Removed by Filter: 22319
6. 25.389 65.932 ↑ 1.0 91,198 1

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

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

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

  • Filter: (produit_dossier_id = 185)
  • Rows Removed by Filter: 23317
8. 0.000 0.000 ↑ 1.0 4 91,528

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

9. 0.002 0.061 ↑ 1.0 4 1

Subquery Scan on profiltarif (cost=3.93..4.01 rows=4 width=4) (actual time=0.056..0.061 rows=4 loops=1)

10. 0.021 0.059 ↑ 1.0 4 1

HashAggregate (cost=3.93..3.97 rows=4 width=592) (actual time=0.055..0.059 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
11. 0.001 0.038 ↑ 1.0 4 1

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

12. 0.015 0.015 ↑ 1.0 3 1

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

  • Filter: (profiltarif_dossier_id = 185)
  • Rows Removed by Filter: 6
13. 0.002 0.022 ↑ 1.0 1 1

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

14. 0.001 0.020 ↑ 1.0 1 1

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

15. 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)

16. 0.007 0.012 ↓ 0.0 0 1

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

17. 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
18. 0.000 14.740 ↓ 0.0 0 1

Hash (cost=2,011.50..2,011.50 rows=1 width=12) (actual time=14.740..14.740 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
19. 14.740 14.740 ↓ 0.0 0 1

Seq Scan on tarifvente (cost=0.00..2,011.50 rows=1 width=12) (actual time=14.740..14.740 rows=0 loops=1)

  • Filter: (('2019-09-11 14:30:22'::timestamp without time zone >= tarifvente_debut) AND ('2019-09-11 14:30:22'::timestamp without time zone <= tarifvente_fin) AND (tarifvente_dossier_id = 185) AND (tarifvente_client_id = 1091161))
  • Rows Removed by Filter: 44226
Planning time : 1.448 ms
Execution time : 404.229 ms