explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HUwo

Settings
# exclusive inclusive rows x rows loops node
1. 565.986 1,330,200.564 ↓ 10,000.0 10,000 1

Unique (cost=24,923.08..24,923.13 rows=1 width=293) (actual time=1,328,897.819..1,330,200.564 rows=10,000 loops=1)

2.          

CTE tef95416f02bf475ab2f4ead517e70ad0

3. 25.461 144.109 ↓ 173.1 2,769 1

Nested Loop (cost=249.20..7,343.84 rows=16 width=16) (actual time=2.684..144.109 rows=2,769 loops=1)

4. 22.954 24.502 ↓ 173.1 2,769 1

Bitmap Heap Scan on resjour (cost=248.79..7,208.96 rows=16 width=16) (actual time=2.456..24.502 rows=2,769 loops=1)

  • Recheck Cond: ((analyz)::text = 'ECBU2_URCUL'::text)
  • Filter: (btrim((resultat)::text) = ''::text)
  • Rows Removed by Filter: 414
  • Heap Blocks: exact=2,663
5. 1.548 1.548 ↑ 1.0 3,192 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..248.78 rows=3,248 width=0) (actual time=1.546..1.548 rows=3,192 loops=1)

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

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

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

CTE te66904f7a30c4dee9398d51929156e6c

8. 96.935 213.327 ↓ 1.0 3,298 1

Hash Join (cost=7,265.68..9,928.08 rows=3,243 width=16) (actual time=32.572..213.327 rows=3,298 loops=1)

  • Hash Cond: ((tubejour_1.numtube)::text = (resjour_1.numtube)::text)
9. 84.100 84.100 ↑ 1.0 31,239 1

Seq Scan on tubejour tubejour_1 (cost=0.00..2,580.39 rows=31,239 width=32) (actual time=0.035..84.100 rows=31,239 loops=1)

10. 6.429 32.292 ↓ 1.0 3,298 1

Hash (cost=7,225.14..7,225.14 rows=3,243 width=16) (actual time=32.289..32.292 rows=3,298 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 190kB
11. 23.136 25.863 ↓ 1.0 3,298 1

Bitmap Heap Scan on resjour resjour_1 (cost=249.68..7,225.14 rows=3,243 width=16) (actual time=3.185..25.863 rows=3,298 loops=1)

  • Recheck Cond: ((analyz)::text = 'QWBC'::text)
  • Filter: (btrim((resultat)::text) <> ''::text)
  • Rows Removed by Filter: 109
  • Heap Blocks: exact=2,557
12. 2.727 2.727 ↓ 1.1 3,463 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..248.87 rows=3,260 width=0) (actual time=2.725..2.727 rows=3,463 loops=1)

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

CTE t6c794bbe22914c37bf2f276f1d06375b

14. 33.491 183.455 ↓ 197.1 3,351 1

Nested Loop (cost=253.72..7,444.65 rows=17 width=16) (actual time=5.189..183.455 rows=3,351 loops=1)

15. 28.512 32.679 ↓ 197.1 3,351 1

Bitmap Heap Scan on resjour resjour_2 (cost=253.30..7,301.34 rows=17 width=16) (actual time=5.084..32.679 rows=3,351 loops=1)

  • Recheck Cond: ((analyz)::text = 'ECBU2_URGE1'::text)
  • Filter: (btrim((resultat)::text) = ''::text)
  • Rows Removed by Filter: 53
  • Heap Blocks: exact=2,839
16. 4.167 4.167 ↓ 1.0 3,414 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..253.30 rows=3,317 width=0) (actual time=4.166..4.167 rows=3,414 loops=1)

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

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

  • Index Cond: ((numtube)::text = (resjour_2.numtube)::text)
18. 5,340.204 1,329,634.578 ↓ 158,732.0 158,732 1

Sort (cost=206.51..206.51 rows=1 width=293) (actual time=1,328,897.807..1,329,634.578 rows=158,732 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: 20,184kB
19. 623,491.382 1,324,294.374 ↓ 158,732.0 158,732 1

Nested Loop (cost=75.21..206.50 rows=1 width=293) (actual time=667.725..1,324,294.374 rows=158,732 loops=1)

  • Join Filter: ((form_pj.pj_numtravail)::text = (te66904f7a30c4dee9398d51929156e6c.numtravail)::text)
  • Rows Removed by Join Filter: 280,392,121
20. 1,249.306 6,605.488 ↓ 164,346.0 164,346 1

Nested Loop (cost=2.24..129.02 rows=1 width=433) (actual time=417.117..6,605.488 rows=164,346 loops=1)

21. 146.155 1,630.887 ↓ 10,435.0 10,435 1

Nested Loop (cost=1.82..126.39 rows=1 width=433) (actual time=417.009..1,630.887 rows=10,435 loops=1)

22. 40.375 806.450 ↓ 2,629.0 2,629 1

Hash Join (cost=1.41..122.38 rows=1 width=304) (actual time=416.961..806.450 rows=2,629 loops=1)

  • Hash Cond: ((form_pj.pj_numtravail)::text = (t6c794bbe22914c37bf2f276f1d06375b.numtravail)::text)
23. 61.120 526.950 ↓ 213.0 2,769 1

Nested Loop (cost=0.64..121.56 rows=13 width=186) (actual time=177.727..526.950 rows=2,769 loops=1)

24. 33.592 197.237 ↓ 173.1 2,769 1

HashAggregate (cost=0.36..0.52 rows=16 width=118) (actual time=177.607..197.237 rows=2,769 loops=1)

  • Group Key: (tef95416f02bf475ab2f4ead517e70ad0.numtravail)::text
25. 163.645 163.645 ↓ 173.1 2,769 1

CTE Scan on tef95416f02bf475ab2f4ead517e70ad0 (cost=0.00..0.32 rows=16 width=118) (actual time=2.701..163.645 rows=2,769 loops=1)

26. 268.593 268.593 ↑ 1.0 1 2,769

Index Scan using inx_dossier on form_pj (cost=0.28..7.55 rows=1 width=68) (actual time=0.091..0.097 rows=1 loops=2,769)

  • Index Cond: ((pj_numtravail)::text = (tef95416f02bf475ab2f4ead517e70ad0.numtravail)::text)
  • Filter: ((pj_typedoc = 1) AND ((pj_analyz)::text = 'ECBU'::text))
  • Rows Removed by Filter: 0
27. 8.744 239.125 ↓ 197.1 3,351 1

Hash (cost=0.55..0.55 rows=17 width=118) (actual time=239.123..239.125 rows=3,351 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 190kB
28. 24.464 230.381 ↓ 197.1 3,351 1

HashAggregate (cost=0.38..0.55 rows=17 width=118) (actual time=222.128..230.381 rows=3,351 loops=1)

  • Group Key: (t6c794bbe22914c37bf2f276f1d06375b.numtravail)::text
29. 205.917 205.917 ↓ 197.1 3,351 1

CTE Scan on t6c794bbe22914c37bf2f276f1d06375b (cost=0.00..0.34 rows=17 width=118) (actual time=5.203..205.917 rows=3,351 loops=1)

30. 678.282 678.282 ↓ 4.0 4 2,629

Index Scan using idx_tubejour2 on tubejour t (cost=0.41..4.00 rows=1 width=141) (actual time=0.148..0.258 rows=4 loops=2,629)

  • Index Cond: ((numtravail)::text = (form_pj.pj_numtravail)::text)
  • Filter: ((form_pj.pj_numtube)::text = (numcodebarre)::text)
  • Rows Removed by Filter: 0
31. 3,725.295 3,725.295 ↑ 1.1 16 10,435

Index Only Scan using idx_resjour0 on resjour r (cost=0.42..2.44 rows=18 width=16) (actual time=0.113..0.357 rows=16 loops=10,435)

  • Index Cond: (numtube = (t.numtube)::text)
  • Heap Fetches: 82,244
32. 693,962.462 694,197.504 ↓ 8.5 1,707 164,346

HashAggregate (cost=72.97..74.97 rows=200 width=118) (actual time=0.005..4.224 rows=1,707 loops=164,346)

  • Group Key: (te66904f7a30c4dee9398d51929156e6c.numtravail)::text
33. 235.042 235.042 ↓ 1.0 3,298 1

CTE Scan on te66904f7a30c4dee9398d51929156e6c (cost=0.00..64.86 rows=3,243 width=118) (actual time=32.584..235.042 rows=3,298 loops=1)