explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xe4R : Optimization for: Optimization for: plan #XGnu; plan #oPiH

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.415 3,836.838 ↑ 61.1 17 1

GroupAggregate (cost=1,687,751.06..1,687,782.20 rows=1,038 width=38) (actual time=3,832.376..3,836.838 rows=17 loops=1)

  • Group Key: vcarticle.vcarticle_tag
2. 6.208 3,833.423 ↓ 18.8 19,538 1

Sort (cost=1,687,751.06..1,687,753.66 rows=1,038 width=38) (actual time=3,832.361..3,833.423 rows=19,538 loops=1)

  • Sort Key: vcarticle.vcarticle_tag
  • Sort Method: quicksort Memory: 2295kB
3. 6.605 3,827.215 ↓ 18.8 19,538 1

Hash Join (cost=245,038.34..1,687,699.06 rows=1,038 width=38) (actual time=474.585..3,827.215 rows=19,538 loops=1)

  • Hash Cond: (vcentete.vcentete_adhetablissement_id = adhetablissement.adhetablissement_id)
4. 7.918 3,820.505 ↓ 8.1 25,138 1

Nested Loop (cost=245,022.19..1,687,660.86 rows=3,114 width=46) (actual time=474.475..3,820.505 rows=25,138 loops=1)

5. 507.060 3,737.173 ↓ 1.5 25,138 1

Hash Join (cost=245,021.75..1,667,676.52 rows=16,860 width=46) (actual time=474.459..3,737.173 rows=25,138 loops=1)

  • Hash Cond: (vcligne.vcligne_vcarticle_id = vcarticle.vcarticle_id)
6. 2,775.411 3,132.116 ↓ 1.0 3,144,694 1

Bitmap Heap Scan on vcligne (cost=86,534.85..1,497,572.43 rows=3,052,956 width=36) (actual time=375.834..3,132.116 rows=3,144,694 loops=1)

  • Recheck Cond: ((vcligne_dossier_id = 15) AND (vcligne_date >= '2018-10-04 00:00:00'::timestamp without time zone) AND (vcligne_date <= '2019-10-04 23:59:59'::timestamp without time zone))
  • Rows Removed by Index Recheck: 1823048
  • Filter: ((vcligne_typelotcalc)::text = ANY ('{"Article simple","Article lot détail","Article assemblé détail"}'::text[]))
  • Rows Removed by Filter: 397
  • Heap Blocks: exact=95680 lossy=331038
7. 356.705 356.705 ↓ 1.0 3,166,046 1

Bitmap Index Scan on vcligne_date_sk (cost=0.00..85,771.61 rows=3,055,604 width=0) (actual time=356.705..356.705 rows=3,166,046 loops=1)

  • Index Cond: ((vcligne_dossier_id = 15) AND (vcligne_date >= '2018-10-04 00:00:00'::timestamp without time zone) AND (vcligne_date <= '2019-10-04 23:59:59'::timestamp without time zone))
8. 15.722 97.997 ↓ 1.1 95,074 1

Hash (cost=157,371.62..157,371.62 rows=89,223 width=22) (actual time=97.997..97.997 rows=95,074 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5697kB
9. 82.275 82.275 ↓ 1.1 95,074 1

Index Scan using vcarticle_tag_sk on vcarticle (cost=0.56..157,371.62 rows=89,223 width=22) (actual time=0.021..82.275 rows=95,074 loops=1)

  • Index Cond: ((vcarticle_dossier_id = 15) AND ((vcarticle_tag)::text = ANY ('{INCONNU,TRANS,FMAGIM-18900,FMAGIM-18903,FRIVIE-QPL370,FBRONC-GS01,FMAGIM-18904,FMARKB-1673,FSEMA-72512,FCARAM-1201RDN,FCOUZO-SALPSH029T00,FBASTI-963026,FAOC-80024,FSTAUB-1202023,FNIJI-CS167-BU,FJELLY-FLA2LF,FBAMIX-MX150060,RECHAP}'::text[])))
10. 75.414 75.414 ↑ 1.0 1 25,138

Index Scan using vcentete_pk on vcentete (cost=0.43..1.18 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=25,138)

  • Index Cond: (vcentete_id = vcligne.vcligne_vcentete_id)
  • Filter: ((vcentete_date >= '2018-10-04 00:00:00'::timestamp without time zone) AND (vcentete_date <= '2019-10-04 23:59:59'::timestamp without time zone) AND (vcentete_dossier_id = 15))
11. 0.013 0.105 ↓ 1.0 109 1

Hash (cost=14.81..14.81 rows=107 width=8) (actual time=0.105..0.105 rows=109 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
12. 0.092 0.092 ↓ 1.0 109 1

Seq Scan on adhetablissement (cost=0.00..14.81 rows=107 width=8) (actual time=0.009..0.092 rows=109 loops=1)

  • Filter: ((adhetablissement_dossier_id = 15) AND ((adhetablissement_enseignelib)::text = 'Ambiance & Styles'::text))
  • Rows Removed by Filter: 219
Planning time : 0.799 ms