explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cf9j

Settings
# exclusive inclusive rows x rows loops node
1. 216.773 2,444.255 ↓ 15,663.0 15,663 1

Unique (cost=28,508.43..28,508.48 rows=1 width=288) (actual time=1,985.325..2,444.255 rows=15,663 loops=1)

2.          

CTE tef95416f02bf475ab2f4ead517e70ad0

3. 2.779 78.673 ↓ 199.4 4,387 1

Nested Loop (cost=93.38..7,964.33 rows=22 width=16) (actual time=1.132..78.673 rows=4,387 loops=1)

4. 35.627 36.411 ↓ 199.4 4,387 1

Bitmap Heap Scan on resjour (cost=92.97..7,778.82 rows=22 width=16) (actual time=1.109..36.411 rows=4,387 loops=1)

  • Recheck Cond: ((analyz)::text = 'ECBU2_URCUL'::text)
  • Filter: (btrim((resultat)::text) = ''::text)
  • Rows Removed by Filter: 263
  • Heap Blocks: exact=3648
5. 0.784 0.784 ↓ 1.1 4,650 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..92.96 rows=4,338 width=0) (actual time=0.784..0.784 rows=4,650 loops=1)

  • Index Cond: ((analyz)::text = 'ECBU2_URCUL'::text)
6. 39.483 39.483 ↑ 1.0 1 4,387

Index Scan using pk_tubejour on tubejour (cost=0.41..8.43 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=4,387)

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

CTE te66904f7a30c4dee9398d51929156e6c

8. 15.384 65.612 ↑ 1.0 4,943 1

Hash Join (cost=8,393.21..11,268.59 rows=5,024 width=16) (actual time=43.409..65.612 rows=4,943 loops=1)

  • Hash Cond: ((tubejour_1.numtube)::text = (resjour_1.numtube)::text)
9. 6.879 6.879 ↑ 1.0 64,505 1

Seq Scan on tubejour tubejour_1 (cost=0.00..2,706.05 rows=64,505 width=32) (actual time=0.014..6.879 rows=64,505 loops=1)

10. 1.427 43.349 ↑ 1.0 4,943 1

Hash (cost=8,330.41..8,330.41 rows=5,024 width=16) (actual time=43.349..43.349 rows=4,943 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 301kB
11. 40.906 41.922 ↑ 1.0 4,943 1

Bitmap Heap Scan on resjour resjour_1 (cost=107.56..8,330.41 rows=5,024 width=16) (actual time=1.422..41.922 rows=4,943 loops=1)

  • Recheck Cond: ((analyz)::text = 'QWBC'::text)
  • Filter: (btrim((resultat)::text) <> ''::text)
  • Rows Removed by Filter: 311
  • Heap Blocks: exact=3716
12. 1.016 1.016 ↓ 1.0 5,254 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..106.30 rows=5,050 width=0) (actual time=1.016..1.016 rows=5,254 loops=1)

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

CTE t6c794bbe22914c37bf2f276f1d06375b

14. 5.084 81.173 ↓ 177.6 5,149 1

Nested Loop (cost=119.90..9,007.59 rows=29 width=16) (actual time=1.759..81.173 rows=5,149 loops=1)

15. 33.583 34.897 ↓ 177.6 5,149 1

Bitmap Heap Scan on resjour resjour_2 (cost=119.48..8,767.04 rows=29 width=16) (actual time=1.733..34.897 rows=5,149 loops=1)

  • Recheck Cond: ((analyz)::text = 'ECBU2_URGE1'::text)
  • Filter: (btrim((resultat)::text) = ''::text)
  • Rows Removed by Filter: 104
  • Heap Blocks: exact=3960
16. 1.314 1.314 ↑ 1.1 5,253 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..119.47 rows=5,740 width=0) (actual time=1.314..1.314 rows=5,253 loops=1)

  • Index Cond: ((analyz)::text = 'ECBU2_URGE1'::text)
17. 41.192 41.192 ↑ 1.0 1 5,149

Index Scan using pk_tubejour on tubejour tubejour_2 (cost=0.41..8.29 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=5,149)

  • Index Cond: ((numtube)::text = (resjour_2.numtube)::text)
18. 1,586.509 2,227.482 ↓ 251,150.0 251,150 1

Sort (cost=267.92..267.92 rows=1 width=288) (actual time=1,985.323..2,227.482 rows=251,150 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: 31528kB
19. 114.005 640.973 ↓ 251,150.0 251,150 1

Nested Loop (cost=146.01..267.91 rows=1 width=288) (actual time=246.022..640.973 rows=251,150 loops=1)

20. 10.667 339.012 ↓ 15,663.0 15,663 1

Nested Loop (cost=145.59..266.76 rows=1 width=176) (actual time=245.950..339.012 rows=15,663 loops=1)

21. 3.224 273.535 ↓ 3,915.0 3,915 1

Hash Join (cost=145.17..264.50 rows=1 width=418) (actual time=245.910..273.535 rows=3,915 loops=1)

  • Hash Cond: ((te66904f7a30c4dee9398d51929156e6c.numtravail)::text = (form_pj.pj_numtravail)::text)
22. 67.829 67.829 ↑ 1.0 4,943 1

CTE Scan on te66904f7a30c4dee9398d51929156e6c (cost=0.00..100.48 rows=5,024 width=118) (actual time=43.412..67.829 rows=4,943 loops=1)

23. 2.400 202.482 ↓ 4,271.0 4,271 1

Hash (cost=145.16..145.16 rows=1 width=300) (actual time=202.482..202.482 rows=4,271 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 530kB
24. 5.339 200.082 ↓ 4,271.0 4,271 1

Nested Loop (cost=1.00..145.16 rows=1 width=300) (actual time=83.996..200.082 rows=4,271 loops=1)

  • Join Filter: ((tef95416f02bf475ab2f4ead517e70ad0.numtravail)::text = (form_pj.pj_numtravail)::text)
25. 2.909 169.117 ↓ 194.1 4,271 1

Hash Join (cost=0.72..1.62 rows=22 width=236) (actual time=83.899..169.117 rows=4,271 loops=1)

  • Hash Cond: ((t6c794bbe22914c37bf2f276f1d06375b.numtravail)::text = (tef95416f02bf475ab2f4ead517e70ad0.numtravail)::text)
26. 84.093 84.093 ↓ 177.6 5,149 1

CTE Scan on t6c794bbe22914c37bf2f276f1d06375b (cost=0.00..0.58 rows=29 width=118) (actual time=1.761..84.093 rows=5,149 loops=1)

27. 1.287 82.115 ↓ 199.4 4,387 1

Hash (cost=0.44..0.44 rows=22 width=118) (actual time=82.115..82.115 rows=4,387 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 271kB
28. 80.828 80.828 ↓ 199.4 4,387 1

CTE Scan on tef95416f02bf475ab2f4ead517e70ad0 (cost=0.00..0.44 rows=22 width=118) (actual time=1.133..80.828 rows=4,387 loops=1)

29. 25.626 25.626 ↑ 1.0 1 4,271

Index Scan using inx_dossier on form_pj (cost=0.28..6.51 rows=1 width=64) (actual time=0.006..0.006 rows=1 loops=4,271)

  • Index Cond: ((pj_numtravail)::text = (t6c794bbe22914c37bf2f276f1d06375b.numtravail)::text)
  • Filter: ((pj_typedoc = 1) AND ((pj_analyz)::text = 'ECBU'::text))
  • Rows Removed by Filter: 0
30. 54.810 54.810 ↓ 4.0 4 3,915

Index Scan using idx_tubejour2 on tubejour t (cost=0.41..2.24 rows=1 width=140) (actual time=0.010..0.014 rows=4 loops=3,915)

  • Index Cond: ((numtravail)::text = (form_pj.pj_numtravail)::text)
  • Filter: ((form_pj.pj_numtube)::text = (numcodebarre)::text)
  • Rows Removed by Filter: 0
31. 187.956 187.956 ↑ 1.2 16 15,663

Index Only Scan using idx_resjour0 on resjour r (cost=0.42..0.95 rows=19 width=16) (actual time=0.010..0.012 rows=16 loops=15,663)

  • Index Cond: (numtube = (t.numtube)::text)
  • Heap Fetches: 0