explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SeiM : interne avec copie des 3 tables

Settings
# exclusive inclusive rows x rows loops node
1. 135.842 3,088.145 ↓ 9,996.0 9,996 1

Unique (cost=13,549.56..13,549.60 rows=1 width=395) (actual time=2,814.129..3,088.145 rows=9,996 loops=1)

  • Execution Time: 3094.082 ms(66 lignes)
2.          

CTE tef95416f02bf475ab2f4ead517e70ad0

3. 2.153 35.605 ↓ 294.1 2,941 1

Nested Loop (cost=43.00..3,958.46 rows=10 width=16) (actual time=1.393..35.605 rows=2,941 loops=1)

4. 6.038 6.983 ↓ 294.1 2,941 1

Bitmap Heap Scan on resjour (cost=42.71..3,875.39 rows=10 width=118) (actual time=1.269..6.983 rows=2,941 loops=1)

  • Recheck Cond: ((analyz)::text = 'ECBU2_URCUL'::text)
  • Filter: (btrim((resultat)::text) = ''::text)
  • Rows Removed by Filter: 414
  • Heap Blocks: exact=2499
5. 0.945 0.945 ↓ 1.8 3,355 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..42.71 rows=1,905 width=0) (actual time=0.945..0.945 rows=3,355 loops=1)

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

Index Scan using pk_tubejour on tubejour (cost=0.29..8.31 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=2,941)

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

CTE te66904f7a30c4dee9398d51929156e6c

8. 6.061 43.014 ↓ 1.7 3,298 1

Hash Join (cost=3,899.55..5,483.43 rows=1,895 width=16) (actual time=32.073..43.014 rows=3,298 loops=1)

  • Hash Cond: ((tubejour_1.numtube)::text = (resjour_1.numtube)::text)
9. 4.920 4.920 ↑ 1.0 35,713 1

Seq Scan on tubejour tubejour_1 (cost=0.00..1,490.13 rows=35,713 width=32) (actual time=0.016..4.920 rows=35,713 loops=1)

10. 1.112 32.033 ↓ 1.7 3,298 1

Hash (cost=3,875.86..3,875.86 rows=1,895 width=118) (actual time=32.033..32.033 rows=3,298 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 190kB
11. 30.125 30.921 ↓ 1.7 3,298 1

Bitmap Heap Scan on resjour resjour_1 (cost=43.18..3,875.86 rows=1,895 width=118) (actual time=1.041..30.921 rows=3,298 loops=1)

  • Recheck Cond: ((analyz)::text = 'QWBC'::text)
  • Filter: (btrim((resultat)::text) <> ''::text)
  • Rows Removed by Filter: 283
  • Heap Blocks: exact=2353
12. 0.796 0.796 ↓ 1.9 3,581 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..42.71 rows=1,905 width=0) (actual time=0.796..0.796 rows=3,581 loops=1)

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

CTE t6c794bbe22914c37bf2f276f1d06375b

14. 3.173 60.297 ↓ 352.1 3,521 1

Nested Loop (cost=43.00..3,958.46 rows=10 width=16) (actual time=1.369..60.297 rows=3,521 loops=1)

15. 20.831 21.914 ↓ 352.1 3,521 1

Bitmap Heap Scan on resjour resjour_2 (cost=42.71..3,875.39 rows=10 width=118) (actual time=1.326..21.914 rows=3,521 loops=1)

  • Recheck Cond: ((analyz)::text = 'ECBU2_URGE1'::text)
  • Filter: (btrim((resultat)::text) = ''::text)
  • Rows Removed by Filter: 53
  • Heap Blocks: exact=2554
16. 1.083 1.083 ↓ 1.9 3,574 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..42.71 rows=1,905 width=0) (actual time=1.083..1.083 rows=3,574 loops=1)

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

Index Scan using pk_tubejour on tubejour tubejour_2 (cost=0.29..8.31 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=3,521)

  • Index Cond: ((numtube)::text = (resjour_2.numtube)::text)
18. 1,057.984 2,952.303 ↓ 160,160.0 160,160 1

Sort (cost=149.20..149.20 rows=1 width=395) (actual time=2,814.126..2,952.303 rows=160,160 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: 20384kB
19. 90.328 1,894.319 ↓ 160,160.0 160,160 1

Nested Loop (cost=1.32..149.19 rows=1 width=395) (actual time=110.059..1,894.319 rows=160,160 loops=1)

20. 5.872 1,614.067 ↓ 9,996.0 9,996 1

Nested Loop (cost=0.90..95.56 rows=1 width=181) (actual time=109.297..1,614.067 rows=9,996 loops=1)

21. 1,058.708 1,575.695 ↓ 2,500.0 2,500 1

Nested Loop (cost=0.61..93.91 rows=1 width=422) (actual time=109.244..1,575.695 rows=2,500 loops=1)

  • Join Filter: ((form_pj.pj_numtravail)::text = (tef95416f02bf475ab2f4ead517e70ad0.numtravail)::text)
  • Rows Removed by Join Filter: 9367526
22. 37.604 37.604 ↓ 294.1 2,941 1

CTE Scan on tef95416f02bf475ab2f4ead517e70ad0 (cost=0.00..0.20 rows=10 width=118) (actual time=1.396..37.604 rows=2,941 loops=1)

23. 343.005 479.383 ↓ 1,062.0 3,186 2,941

Materialize (cost=0.61..93.27 rows=3 width=304) (actual time=0.033..0.163 rows=3,186 loops=2,941)

24. 1.013 136.378 ↓ 1,062.0 3,186 1

Nested Loop (cost=0.61..93.25 rows=3 width=304) (actual time=95.782..136.378 rows=3,186 loops=1)

25. 1.863 109.877 ↓ 33.5 3,186 1

Hash Join (cost=0.33..46.28 rows=95 width=236) (actual time=95.684..109.877 rows=3,186 loops=1)

  • Hash Cond: ((te66904f7a30c4dee9398d51929156e6c.numtravail)::text = (t6c794bbe22914c37bf2f276f1d06375b.numtravail)::text)
26. 44.418 44.418 ↓ 1.7 3,298 1

CTE Scan on te66904f7a30c4dee9398d51929156e6c (cost=0.00..37.90 rows=1,895 width=118) (actual time=32.076..44.418 rows=3,298 loops=1)

27. 1.239 63.596 ↓ 352.1 3,521 1

Hash (cost=0.20..0.20 rows=10 width=118) (actual time=63.596..63.596 rows=3,521 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 198kB
28. 62.357 62.357 ↓ 352.1 3,521 1

CTE Scan on t6c794bbe22914c37bf2f276f1d06375b (cost=0.00..0.20 rows=10 width=118) (actual time=1.371..62.357 rows=3,521 loops=1)

29. 25.488 25.488 ↑ 1.0 1 3,186

Index Scan using inx_dossier on form_pj (cost=0.28..0.48 rows=1 width=68) (actual time=0.007..0.008 rows=1 loops=3,186)

  • Index Cond: ((pj_numtravail)::text = (te66904f7a30c4dee9398d51929156e6c.numtravail)::text)
  • Filter: ((pj_typedoc = 1) AND ((pj_analyz)::text = 'ECBU'::text))
  • Rows Removed by Filter: 0
30. 32.500 32.500 ↓ 4.0 4 2,500

Index Scan using idx_tubejour2 on tubejour t (cost=0.29..1.64 rows=1 width=141) (actual time=0.010..0.013 rows=4 loops=2,500)

  • Index Cond: ((numtravail)::text = (form_pj.pj_numtravail)::text)
  • Filter: ((form_pj.pj_numtube)::text = (numcodebarre)::text)
  • Rows Removed by Filter: 0
31. 189.924 189.924 ↑ 119.1 16 9,996

Index Only Scan using idx_resjour0 on resjour r (cost=0.42..34.56 rows=1,905 width=118) (actual time=0.012..0.019 rows=16 loops=9,996)

  • Index Cond: (numtube = (t.numtube)::text)
  • Heap Fetches: 160160
Planning time : 12.747 ms