explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fiXL

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

Unique (cost=28,575.98..28,576.03 rows=1 width=289) (actual time=1,962.072..2,416.478 rows=15,663 loops=1)

  • Execution Time: 2424.100 ms(67 lignes)
2.          

CTE tef95416f02bf475ab2f4ead517e70ad0

3. 2.210 52.718 ↓ 190.7 4,387 1

Nested Loop (cost=94.84..8,131.97 rows=23 width=16) (actual time=0.946..52.718 rows=4,387 loops=1)

4. 10.433 11.025 ↓ 190.7 4,387 1

Bitmap Heap Scan on resjour (cost=94.42..7,938.02 rows=23 width=16) (actual time=0.924..11.025 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.592 0.592 ↓ 1.0 4,650 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..94.42 rows=4,532 width=0) (actual time=0.592..0.592 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. 13.425 42.145 ↑ 1.0 4,943 1

Hash Join (cost=8,407.86..11,283.24 rows=5,046 width=16) (actual time=21.005..42.145 rows=4,943 loops=1)

  • Hash Cond: ((tubejour_1.numtube)::text = (resjour_1.numtube)::text)
9. 7.928 7.928 ↑ 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.018..7.928 rows=64,505 loops=1)

10. 1.604 20.792 ↑ 1.0 4,943 1

Hash (cost=8,344.78..8,344.78 rows=5,046 width=16) (actual time=20.792..20.792 rows=4,943 loops=1)

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

Bitmap Heap Scan on resjour resjour_1 (cost=107.72..8,344.78 rows=5,046 width=16) (actual time=1.280..19.188 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. 0.893 0.893 ↓ 1.0 5,254 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..106.46 rows=5,071 width=0) (actual time=0.893..0.893 rows=5,254 loops=1)

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

CTE t6c794bbe22914c37bf2f276f1d06375b

14. 0.817 51.759 ↓ 183.9 5,149 1

Nested Loop (cost=114.44..8,883.39 rows=28 width=16) (actual time=1.601..51.759 rows=5,149 loops=1)

15. 8.590 9.750 ↓ 183.9 5,149 1

Bitmap Heap Scan on resjour resjour_2 (cost=114.03..8,651.28 rows=28 width=16) (actual time=1.567..9.750 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.160 1.160 ↑ 1.1 5,253 1

Bitmap Index Scan on idx_resjour1 (cost=0.00..114.02 rows=5,546 width=0) (actual time=1.160..1.160 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,574.202 2,203.047 ↓ 251,150.0 251,150 1

Sort (cost=277.38..277.38 rows=1 width=289) (actual time=1,962.068..2,203.047 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. 122.215 628.845 ↓ 251,150.0 251,150 1

Nested Loop (cost=154.37..277.37 rows=1 width=289) (actual time=165.730..628.845 rows=251,150 loops=1)

20. 12.558 256.022 ↓ 15,663.0 15,663 1

Nested Loop (cost=153.94..275.63 rows=1 width=177) (actual time=165.703..256.022 rows=15,663 loops=1)

21. 3.686 192.569 ↓ 3,915.0 3,915 1

Hash Join (cost=153.53..273.38 rows=1 width=418) (actual time=165.678..192.569 rows=3,915 loops=1)

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

CTE Scan on te66904f7a30c4dee9398d51929156e6c (cost=0.00..100.92 rows=5,046 width=118) (actual time=21.009..44.236 rows=4,943 loops=1)

23. 2.265 144.647 ↓ 4,271.0 4,271 1

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

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

Nested Loop (cost=1.03..153.52 rows=1 width=300) (actual time=57.598..142.382 rows=4,271 loops=1)

  • Join Filter: ((tef95416f02bf475ab2f4ead517e70ad0.numtravail)::text = (form_pj.pj_numtravail)::text)
25. 2.549 112.424 ↓ 185.7 4,271 1

Hash Join (cost=0.75..1.64 rows=23 width=236) (actual time=57.574..112.424 rows=4,271 loops=1)

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

CTE Scan on t6c794bbe22914c37bf2f276f1d06375b (cost=0.00..0.56 rows=28 width=118) (actual time=1.603..53.926 rows=5,149 loops=1)

27. 1.288 55.949 ↓ 190.7 4,387 1

Hash (cost=0.46..0.46 rows=23 width=118) (actual time=55.949..55.949 rows=4,387 loops=1)

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

CTE Scan on tef95416f02bf475ab2f4ead517e70ad0 (cost=0.00..0.46 rows=23 width=118) (actual time=0.947..54.661 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.59 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. 50.895 50.895 ↓ 4.0 4 3,915

Index Scan using idx_tubejour2 on tubejour t (cost=0.41..2.24 rows=1 width=141) (actual time=0.010..0.013 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. 250.608 250.608 ↑ 1.2 16 15,663

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

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