explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oPiH : Optimization for: plan #XGnu

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.227 3,955.591 ↑ 329.1 17 1

GroupAggregate (cost=1,688,055.82..1,688,223.67 rows=5,595 width=38) (actual time=3,951.387..3,955.591 rows=17 loops=1)

  • Group Key: vcarticle.vcarticle_tag
2. 6.174 3,952.364 ↓ 3.5 19,538 1

Sort (cost=1,688,055.82..1,688,069.81 rows=5,595 width=38) (actual time=3,951.372..3,952.364 rows=19,538 loops=1)

  • Sort Key: vcarticle.vcarticle_tag
  • Sort Method: quicksort Memory: 2295kB
3. 6.981 3,946.190 ↓ 3.5 19,538 1

Hash Join (cost=245,034.34..1,687,707.54 rows=5,595 width=38) (actual time=565.562..3,946.190 rows=19,538 loops=1)

  • Hash Cond: (vcentete.vcentete_adhetablissement_id = adhetablissement.adhetablissement_id)
4. 5.971 3,938.992 ↓ 1.5 25,138 1

Nested Loop (cost=245,018.19..1,687,572.54 rows=16,786 width=46) (actual time=565.338..3,938.992 rows=25,138 loops=1)

5. 520.398 3,857.607 ↓ 1.5 25,138 1

Hash Join (cost=245,017.75..1,667,672.52 rows=16,860 width=46) (actual time=565.327..3,857.607 rows=25,138 loops=1)

  • Hash Cond: (vcligne.vcligne_vcarticle_id = vcarticle.vcarticle_id)
6. 2,790.995 3,227.199 ↓ 1.0 3,144,650 1

Bitmap Heap Scan on vcligne (cost=86,530.85..1,497,568.43 rows=3,052,956 width=36) (actual time=454.676..3,227.199 rows=3,144,650 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=95676 lossy=331038
7. 436.204 436.204 ↓ 1.0 3,166,002 1

Bitmap Index Scan on vcligne_date_sk (cost=0.00..85,767.61 rows=3,055,604 width=0) (actual time=436.204..436.204 rows=3,166,002 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. 16.267 110.010 ↓ 1.1 95,074 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 5697kB
9. 93.743 93.743 ↓ 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.230..93.743 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.17 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_dossier_id = 15)
11. 0.027 0.217 ↓ 1.0 109 1

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

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

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

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