explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.112 2,774.951 ↑ 4.0 252 1

GroupAggregate (cost=1,971,216.85..1,971,237.21 rows=1,018 width=16) (actual time=2,771.578..2,774.951 rows=252 loops=1)

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

Sort (cost=1,971,216.85..1,971,219.39 rows=1,018 width=12) (actual time=2,771.556..2,772.839 rows=24,020 loops=1)

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

Hash Join (cost=446,985.82..1,971,165.99 rows=1,018 width=12) (actual time=611.947..2,766.739 rows=24,020 loops=1)

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

Nested Loop (cost=446,969.66..1,971,125.67 rows=3,054 width=16) (actual time=611.795..2,755.552 rows=26,361 loops=1)

5. 405.269 2,653.875 ↑ 1.0 26,361 1

Hash Join (cost=446,969.23..1,925,804.05 rows=27,281 width=4) (actual time=611.771..2,653.875 rows=26,361 loops=1)

  • Hash Cond: (vcligne.vcligne_vcarticle_id = vcarticle.vcarticle_id)
6. 1,649.871 1,875.300 ↓ 1.0 1,904,060 1

Bitmap Heap Scan on vcligne (cost=52,003.97..1,508,407.77 rows=1,835,256 width=8) (actual time=237.695..1,875.300 rows=1,904,060 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=64578 lossy=133058
7. 225.429 225.429 ↓ 1.0 1,918,849 1

Bitmap Index Scan on vcligne_date_sk (cost=0.00..51,545.16 rows=1,836,848 width=0) (actual time=225.429..225.429 rows=1,918,849 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.217 373.306 ↓ 1.1 253,173 1

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

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

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

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

Index Scan using vcentete_pk on vcentete (cost=0.43..1.65 rows=1 width=20) (actual time=0.003..0.003 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.013 0.133 ↓ 1.0 109 1

Hash (cost=14.81..14.81 rows=107 width=4) (actual time=0.133..0.133 rows=109 loops=1)

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

Seq Scan on adhetablissement (cost=0.00..14.81 rows=107 width=4) (actual time=0.013..0.120 rows=109 loops=1)

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