explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 1,971.476 ↑ 1.0 10 1

Limit (cost=1,985,315.70..1,985,315.73 rows=10 width=86) (actual time=1,971.474..1,971.476 rows=10 loops=1)

2. 0.196 1,971.474 ↑ 950.0 10 1

Sort (cost=1,985,315.70..1,985,339.45 rows=9,500 width=86) (actual time=1,971.474..1,971.474 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.732 1,971.278 ↑ 8.0 1,182 1

GroupAggregate (cost=1,984,920.41..1,985,110.41 rows=9,500 width=86) (actual time=1,961.917..1,971.278 rows=1,182 loops=1)

  • Group Key: vcarticle.vcarticle_tag
4. 52.771 1,964.546 ↓ 2.6 24,336 1

Sort (cost=1,984,920.41..1,984,944.16 rows=9,500 width=44) (actual time=1,961.904..1,964.546 rows=24,336 loops=1)

  • Sort Key: vcarticle.vcarticle_tag
  • Sort Method: quicksort Memory: 3810kB
5. 5.679 1,911.775 ↓ 2.6 24,336 1

Nested Loop (cost=452,542.22..1,984,292.76 rows=9,500 width=44) (actual time=406.858..1,911.775 rows=24,336 loops=1)

6. 0.986 1,879.387 ↑ 1.1 26,709 1

Nested Loop (cost=452,542.08..1,979,162.41 rows=28,501 width=52) (actual time=406.845..1,879.387 rows=26,709 loops=1)

7. 428.627 1,798.274 ↑ 1.1 26,709 1

Hash Join (cost=452,541.64..1,933,300.40 rows=28,626 width=52) (actual time=406.825..1,798.274 rows=26,709 loops=1)

  • Hash Cond: (vcligne.vcligne_vcarticle_id = vcarticle.vcarticle_id)
8. 974.854 1,172.645 ↓ 1.0 1,933,272 1

Bitmap Heap Scan on vcligne (cost=55,046.36..1,503,612.15 rows=1,925,788 width=20) (actual time=209.268..1,172.645 rows=1,933,272 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=69550 lossy=133078
9. 197.791 197.791 ↓ 1.0 1,953,706 1

Bitmap Index Scan on vcligne_date_sk (cost=0.00..54,564.91 rows=1,925,788 width=0) (actual time=197.791..197.791 rows=1,953,706 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. 48.624 197.002 ↓ 1.1 254,025 1

Hash (cost=392,367.47..392,367.47 rows=240,865 width=44) (actual time=197.002..197.002 rows=254,025 loops=1)

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

Index Scan using vcarticle_vcclassif2_sk on vcarticle (cost=0.56..392,367.47 rows=240,865 width=44) (actual time=0.021..148.378 rows=254,025 loops=1)

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

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

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

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,709)

  • 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 : 1.018 ms