explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xiqO : Optimization for: Optimization for: Optimization for: plan #gCJ3; plan #EMZK; plan #NiDq

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.133 2,334.855 ↑ 4.0 252 1

GroupAggregate (cost=1,974,802.85..1,974,823.21 rows=1,018 width=16) (actual time=2,331.400..2,334.855 rows=252 loops=1)

  • Group Key: (date_trunc('day'::text, vcentete.vcentete_date))
2. 6.335 2,332.722 ↓ 23.6 24,020 1

Sort (cost=1,974,802.85..1,974,805.39 rows=1,018 width=12) (actual time=2,331.377..2,332.722 rows=24,020 loops=1)

  • Sort Key: (date_trunc('day'::text, vcentete.vcentete_date))
  • Sort Method: quicksort Memory: 1894kB
3. 9.952 2,326.387 ↓ 23.6 24,020 1

Hash Join (cost=446,985.82..1,974,751.99 rows=1,018 width=12) (actual time=413.417..2,326.387 rows=24,020 loops=1)

  • Hash Cond: (vcentete.vcentete_adhetablissement_id = adhetablissement.adhetablissement_id)
4. 23.837 2,316.285 ↓ 8.6 26,361 1

Nested Loop (cost=446,969.66..1,974,711.67 rows=3,054 width=20) (actual time=413.254..2,316.285 rows=26,361 loops=1)

5. 416.425 2,239.726 ↑ 1.0 26,361 1

Hash Join (cost=446,969.23..1,929,390.05 rows=27,281 width=12) (actual time=413.238..2,239.726 rows=26,361 loops=1)

  • Hash Cond: (vcligne.vcligne_vcarticle_id = vcarticle.vcarticle_id)
6. 1,422.372 1,634.363 ↓ 1.0 1,904,072 1

Bitmap Heap Scan on vcligne (cost=52,003.97..1,508,407.77 rows=1,835,256 width=12) (actual time=223.526..1,634.363 rows=1,904,072 loops=1)

  • Recheck Cond: ((vcligne_dossier_id = 15) AND (vcligne_date >= '2019-01-01 00:00:00'::timestamp without time zone) AND (vcligne_date <= '2019-10-04 23:59:59.999999'::timestamp without time zone))
  • Rows Removed by Index Recheck: 206026
  • Filter: ((vcligne_typelotcalc)::text = ANY ('{"Article simple","Article lot détail","Article assemblé détail"}'::text[]))
  • Rows Removed by Filter: 188
  • Heap Blocks: exact=64579 lossy=133058
7. 211.991 211.991 ↓ 1.0 1,918,861 1

Bitmap Index Scan on vcligne_date_sk (cost=0.00..51,545.16 rows=1,836,848 width=0) (actual time=211.991..211.991 rows=1,918,861 loops=1)

  • Index Cond: ((vcligne_dossier_id = 15) AND (vcligne_date >= '2019-01-01 00:00:00'::timestamp without time zone) AND (vcligne_date <= '2019-10-04 23:59:59.999999'::timestamp without time zone))
8. 39.128 188.938 ↓ 1.1 253,173 1

Hash (cost=391,026.68..391,026.68 rows=240,046 width=8) (actual time=188.938..188.938 rows=253,173 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 7005kB
9. 149.810 149.810 ↓ 1.1 253,173 1

Index Scan using vcarticle_vcclassif2_sk on vcarticle (cost=0.56..391,026.68 rows=240,046 width=8) (actual time=0.024..149.810 rows=253,173 loops=1)

  • Index Cond: ((vcarticle_dossier_id = 15) AND ((vcarticle_vcclassif2)::text = 'COUVERT COULEUR'::text))
10. 52.722 52.722 ↑ 1.0 1 26,361

Index Scan using vcentete_pk on vcentete (cost=0.43..1.65 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=26,361)

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

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

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

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

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