explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vlH7 : Avec lenteur

Settings
# exclusive inclusive rows x rows loops node
1. 603.430 1,906,829.775 ↓ 8,161.0 8,161 1

Unique (cost=24,615.48..24,615.53 rows=1 width=294) (actual time=1,905,581.277..1,906,829.775 rows=8,161 loops=1)

2.          

CTE tcaa7b77643db44f190523ec3ccf27b1d

3. 17.579 91.384 ↓ 165.5 2,648 1

Nested Loop (cost=232.31..7,172.27 rows=16 width=16) (actual time=2.138..91.384 rows=2,648 loops=1)

4. 16.588 18.197 ↓ 165.5 2,648 1

Bitmap Heap Scan on resjour (cost=231.89..7,037.35 rows=16 width=16) (actual time=2.098..18.197 rows=2,648 loops=1)

  • Recheck Cond: ((analyz)::text = 'ECBU2_URCUL'::text)
  • Filter: (btrim((resultat)::text) = ''::text)
  • Rows Removed by Filter: 441
  • Heap Blocks: exact=2590
5. 1.609 1.609 ↑ 1.0 3,095 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..231.89 rows=3,129 width=0) (actual time=1.607..1.609 rows=3,095 loops=1)

  • Index Cond: ((analyz)::text = 'ECBU2_URCUL'::text)
6. 55.608 55.608 ↑ 1.0 1 2,648

Index Scan using pk_tubejour on tubejour (cost=0.41..8.43 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=2,648)

  • Index Cond: ((numtube)::text = (resjour.numtube)::text)
7.          

CTE t4998696dd47b4c89a7b83eb077937f12

8. 120.368 241.351 ↑ 1.1 2,885 1

Hash Join (cost=7,321.79..10,044.74 rows=3,291 width=16) (actual time=17.619..241.351 rows=2,885 loops=1)

  • Hash Cond: ((tubejour_1.numtube)::text = (resjour_1.numtube)::text)
9. 103.525 103.525 ↑ 1.0 36,035 1

Seq Scan on tubejour tubejour_1 (cost=0.00..2,628.35 rows=36,035 width=32) (actual time=0.024..103.525 rows=36,035 loops=1)

10. 4.736 17.458 ↑ 1.1 2,885 1

Hash (cost=7,280.65..7,280.65 rows=3,291 width=16) (actual time=17.455..17.458 rows=2,885 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 171kB
11. 10.892 12.722 ↑ 1.1 2,885 1

Bitmap Heap Scan on resjour resjour_1 (cost=246.05..7,280.65 rows=3,291 width=16) (actual time=2.281..12.722 rows=2,885 loops=1)

  • Recheck Cond: ((analyz)::text = 'QWBC'::text)
  • Filter: (btrim((resultat)::text) <> ''::text)
  • Rows Removed by Filter: 387
  • Heap Blocks: exact=2552
12. 1.830 1.830 ↓ 1.1 3,579 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..245.23 rows=3,307 width=0) (actual time=1.828..1.830 rows=3,579 loops=1)

  • Index Cond: ((analyz)::text = 'QWBC'::text)
13.          

CTE t1625ac314c5b4fa9bde9b0c0f9161ddc

14. 23.529 119.701 ↓ 201.6 3,226 1

Nested Loop (cost=232.13..7,140.74 rows=16 width=16) (actual time=1.566..119.701 rows=3,226 loops=1)

15. 17.765 18.748 ↓ 201.6 3,226 1

Bitmap Heap Scan on resjour resjour_2 (cost=231.71..7,005.82 rows=16 width=16) (actual time=1.524..18.748 rows=3,226 loops=1)

  • Recheck Cond: ((analyz)::text = 'ECBU2_URGE1'::text)
  • Filter: (btrim((resultat)::text) = ''::text)
  • Rows Removed by Filter: 43
  • Heap Blocks: exact=2705
16. 0.983 0.983 ↓ 1.1 3,299 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..231.71 rows=3,105 width=0) (actual time=0.982..0.983 rows=3,299 loops=1)

  • Index Cond: ((analyz)::text = 'ECBU2_URGE1'::text)
17. 77.424 77.424 ↑ 1.0 1 3,226

Index Scan using pk_tubejour on tubejour tubejour_2 (cost=0.41..8.43 rows=1 width=32) (actual time=0.024..0.024 rows=1 loops=3,226)

  • Index Cond: ((numtube)::text = (resjour_2.numtube)::text)
18. 4,112.575 1,906,226.345 ↓ 136,794.0 136,794 1

Sort (cost=257.72..257.73 rows=1 width=294) (actual time=1,905,581.267..1,906,226.345 rows=136,794 loops=1)

  • Sort Key: t.numtravail, t.creerle, t.numcodebarre, ((((t.nom)::text || ' '::text) || (t.prenom)::text)), form_pj.pj_ordre, t.urgent, t.originecode, t.preleveur, t.medecin, t.zprescripteur, r.numtube, t.sexe, form_pj.pj_infos, form_pj.pj_stade, (to_char((CURRENT_TIMESTAMP - (t.dhdeballage)::timestamp with time zone), 'DDDHH24'::text)), t.dhdeballage, form_pj.pj_nom_logique
  • Sort Method: external merge Disk: 17872kB
19. 954,903.475 1,902,113.770 ↓ 136,794.0 136,794 1

Nested Loop (cost=177.04..257.71 rows=1 width=294) (actual time=432.774..1,902,113.770 rows=136,794 loops=1)

  • Join Filter: ((t.numtravail)::text = (t1625ac314c5b4fa9bde9b0c0f9161ddc.numtravail)::text)
  • Rows Removed by Join Filter: 454909862
20. 963.453 4,250.935 ↓ 141,056.0 141,056 1

Nested Loop (cost=177.04..257.18 rows=1 width=434) (actual time=371.622..4,250.935 rows=141,056 loops=1)

21. 77.048 695.386 ↓ 8,308.0 8,308 1

Hash Join (cost=176.62..254.79 rows=1 width=434) (actual time=371.536..695.386 rows=8,308 loops=1)

  • Hash Cond: ((t4998696dd47b4c89a7b83eb077937f12.numtravail)::text = (t.numtravail)::text)
22. 264.451 264.451 ↑ 1.1 2,885 1

CTE Scan on t4998696dd47b4c89a7b83eb077937f12 (cost=0.00..65.82 rows=3,291 width=118) (actual time=17.624..264.451 rows=2,885 loops=1)

23. 32.570 353.887 ↓ 9,957.0 9,957 1

Hash (cost=176.61..176.61 rows=1 width=316) (actual time=353.885..353.887 rows=9,957 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1933kB
24. 50.124 321.317 ↓ 9,957.0 9,957 1

Nested Loop (cost=0.70..176.61 rows=1 width=316) (actual time=2.397..321.317 rows=9,957 loops=1)

  • Join Filter: ((tcaa7b77643db44f190523ec3ccf27b1d.numtravail)::text = (t.numtravail)::text)
25. 20.277 170.569 ↓ 203.7 2,648 1

Nested Loop (cost=0.28..121.36 rows=13 width=187) (actual time=2.240..170.569 rows=2,648 loops=1)

26. 102.628 102.628 ↓ 165.5 2,648 1

CTE Scan on tcaa7b77643db44f190523ec3ccf27b1d (cost=0.00..0.32 rows=16 width=118) (actual time=2.143..102.628 rows=2,648 loops=1)

27. 47.664 47.664 ↑ 1.0 1 2,648

Index Scan using inx_dossier on form_pj (cost=0.28..7.55 rows=1 width=69) (actual time=0.016..0.018 rows=1 loops=2,648)

  • Index Cond: ((pj_numtravail)::text = (tcaa7b77643db44f190523ec3ccf27b1d.numtravail)::text)
  • Filter: ((pj_typedoc = 1) AND ((pj_analyz)::text = 'ECBU'::text))
  • Rows Removed by Filter: 0
28. 100.624 100.624 ↓ 4.0 4 2,648

Index Scan using idx_tubejour2 on tubejour t (cost=0.41..4.24 rows=1 width=141) (actual time=0.025..0.038 rows=4 loops=2,648)

  • Index Cond: ((numtravail)::text = (form_pj.pj_numtravail)::text)
  • Filter: (((numtravail)::text <> 'Inconnu'::text) AND ((numtravail)::text <> 'Inconnu'::text) AND ((form_pj.pj_numtube)::text = (numcodebarre)::text))
  • Rows Removed by Filter: 0
29. 2,592.096 2,592.096 ↑ 1.1 17 8,308

Index Only Scan using idx_resjour0 on resjour r (cost=0.42..2.21 rows=18 width=16) (actual time=0.100..0.312 rows=17 loops=8,308)

  • Index Cond: (numtube = (t.numtube)::text)
  • Heap Fetches: 82908
30. 942,959.360 942,959.360 ↓ 201.6 3,226 141,056

CTE Scan on t1625ac314c5b4fa9bde9b0c0f9161ddc (cost=0.00..0.32 rows=16 width=118) (actual time=0.002..6.685 rows=3,226 loops=141,056)