explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aPEe : Optimization for: Optimization for: plan #5Hn; plan #6TVY

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 2,081.037 ↑ 1.0 10 1

Limit (cost=1,985,319.90..1,985,319.92 rows=10 width=86) (actual time=2,081.036..2,081.037 rows=10 loops=1)

2. 0.156 2,081.036 ↑ 950.0 10 1

Sort (cost=1,985,319.90..1,985,343.65 rows=9,500 width=86) (actual time=2,081.035..2,081.036 rows=10 loops=1)

  • Sort Key: (sum(vcligne.vcligne_totalttccomptable)) DESC NULLS LAST, (COALESCE(vcarticle.vcarticle_tag, 'article non renseigné'::character varying)) DESC
  • Sort Method: top-N heapsort Memory: 26kB
3. 6.803 2,080.880 ↑ 8.0 1,182 1

GroupAggregate (cost=1,984,924.61..1,985,114.61 rows=9,500 width=86) (actual time=2,071.213..2,080.880 rows=1,182 loops=1)

  • Group Key: vcarticle.vcarticle_tag
4. 53.587 2,074.077 ↓ 2.6 24,337 1

Sort (cost=1,984,924.61..1,984,948.36 rows=9,500 width=44) (actual time=2,071.205..2,074.077 rows=24,337 loops=1)

  • Sort Key: vcarticle.vcarticle_tag
  • Sort Method: quicksort Memory: 3811kB
5. 7.819 2,020.490 ↓ 2.6 24,337 1

Nested Loop (cost=452,546.42..1,984,296.96 rows=9,500 width=44) (actual time=426.110..2,020.490 rows=24,337 loops=1)

6. 5.567 1,985.961 ↑ 1.1 26,710 1

Nested Loop (cost=452,546.27..1,979,166.61 rows=28,501 width=52) (actual time=426.100..1,985.961 rows=26,710 loops=1)

7. 490.723 1,900.264 ↑ 1.1 26,710 1

Hash Join (cost=452,545.84..1,933,304.60 rows=28,626 width=52) (actual time=426.086..1,900.264 rows=26,710 loops=1)

  • Hash Cond: (vcligne.vcligne_vcarticle_id = vcarticle.vcarticle_id)
8. 996.835 1,200.674 ↓ 1.0 1,933,333 1

Bitmap Heap Scan on vcligne (cost=55,046.36..1,503,612.15 rows=1,925,788 width=20) (actual time=216.691..1,200.674 rows=1,933,333 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-08 23:59:59.999999'::timestamp without time zone))
  • Rows Removed by Index Recheck: 205507
  • Heap Blocks: exact=69557 lossy=133078
9. 203.839 203.839 ↓ 1.0 1,953,767 1

Bitmap Index Scan on vcligne_date_sk (cost=0.00..54,564.91 rows=1,925,788 width=0) (actual time=203.839..203.839 rows=1,953,767 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-08 23:59:59.999999'::timestamp without time zone))
10. 51.315 208.867 ↓ 1.1 254,026 1

Hash (cost=392,371.65..392,371.65 rows=240,867 width=44) (actual time=208.867..208.867 rows=254,026 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 6099kB
11. 157.552 157.552 ↓ 1.1 254,026 1

Index Scan using vcarticle_vcclassif2_sk on vcarticle (cost=0.56..392,371.65 rows=240,867 width=44) (actual time=0.026..157.552 rows=254,026 loops=1)

  • Index Cond: ((vcarticle_dossier_id = 15) AND ((vcarticle_vcclassif2)::text = 'COUVERT COULEUR'::text))
12. 80.130 80.130 ↑ 1.0 1 26,710

Index Scan using vcentete_pk on vcentete (cost=0.43..1.59 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=26,710)

  • Index Cond: (vcentete_id = vcligne.vcligne_vcentete_id)
  • Filter: (vcentete_dossier_id = 15)
13. 26.710 26.710 ↑ 1.0 1 26,710

Index Scan using adhetablissement_pk on adhetablissement (cost=0.15..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=26,710)

  • Index Cond: (adhetablissement_id = vcentete.vcentete_adhetablissement_id)
  • Filter: ((adhetablissement_dossier_id = 15) AND ((adhetablissement_enseignelib)::text = 'Ambiance & Styles'::text))
  • Rows Removed by Filter: 0
Planning time : 0.722 ms