explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tlOg : test Charles

Settings
# exclusive inclusive rows x rows loops node
1. 0.052 32,315.079 ↑ 48,179.0 1 1

Sort (cost=3,467,861.73..3,467,982.17 rows=48,179 width=949) (actual time=32,315.079..32,315.079 rows=1 loops=1)

  • Sort Key: "*SELECT* 1".ioriginereferentiel DESC, "*SELECT* 1".ddatecommande DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=25024077 read=89640 dirtied=2, temp read=5824 written=5822
2. 0.002 32,315.027 ↑ 48,179.0 1 1

Append (cost=158,126.43..3,464,114.33 rows=48,179 width=949) (actual time=5,152.509..32,315.027 rows=1 loops=1)

  • Buffers: shared hit=25024071 read=89640 dirtied=2, temp read=5824 written=5822
3. 0.002 32,016.836 ↑ 48,105.0 1 1

Subquery Scan on *SELECT* 1 (cost=158,126.43..3,424,547.16 rows=48,105 width=388) (actual time=5,152.508..32,016.836 rows=1 loops=1)

  • Buffers: shared hit=24984044 read=89459 dirtied=2, temp read=5824 written=5822
4. 0.902 32,016.834 ↑ 48,105.0 1 1

Hash Join (cost=158,126.43..3,423,945.84 rows=48,105 width=386) (actual time=5,152.507..32,016.834 rows=1 loops=1)

  • Hash Cond: ((t_offre.col_login)::text = (t_collaborateur.col_login)::text)
  • Buffers: shared hit=24984044 read=89459 dirtied=2, temp read=5824 written=5822
5. 0.023 32,015.579 ↑ 48,105.0 1 1

Hash Left Join (cost=158,102.05..239,483.43 rows=48,105 width=248) (actual time=5,151.253..32,015.579 rows=1 loops=1)

  • Hash Cond: ((t_commande.com_id = rq_rdv_livraison.com_id) AND (t_commande.ent_id = rq_rdv_livraison.ent_id))
  • Buffers: shared hit=24983964 read=89459 dirtied=2, temp read=5824 written=5822
6. 0.006 31,977.228 ↑ 48,105.0 1 1

Nested Loop (cost=156,994.65..238,015.23 rows=48,105 width=240) (actual time=5,112.903..31,977.228 rows=1 loops=1)

  • Buffers: shared hit=24983600 read=89444, temp read=5824 written=5822
7. 615.189 31,977.150 ↑ 48,105.0 1 1

Hash Join (cost=156,994.22..180,081.98 rows=48,105 width=222) (actual time=5,112.827..31,977.150 rows=1 loops=1)

  • Hash Cond: (t_offre.veh_id = t_vehicule.veh_id)
  • Join Filter: (SubPlan 7)
  • Rows Removed by Join Filter: 96331
  • Buffers: shared hit=24983597 read=89443, temp read=5824 written=5822
8. 227.739 791.261 ↓ 1.0 96,332 1

Hash Join (cost=34,289.32..41,697.53 rows=96,210 width=158) (actual time=528.091..791.261 rows=96,332 loops=1)

  • Hash Cond: ((t_commande.off_id = t_offre.off_id) AND (t_commande.ent_id = t_offre.ent_id))
  • Buffers: shared hit=32769
9. 36.966 36.966 ↓ 1.0 96,332 1

Seq Scan on t_commande (cost=0.00..6,903.10 rows=96,210 width=116) (actual time=0.009..36.966 rows=96,332 loops=1)

  • Buffers: shared hit=5941
10. 126.349 526.556 ↓ 1.0 298,844 1

Hash (cost=29,812.53..29,812.53 rows=298,453 width=42) (actual time=526.556..526.556 rows=298,844 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 28415kB
  • Buffers: shared hit=26828
11. 400.207 400.207 ↓ 1.0 298,844 1

Seq Scan on t_offre (cost=0.00..29,812.53 rows=298,453 width=42) (actual time=0.020..400.207 rows=298,844 loops=1)

  • Buffers: shared hit=26828
12. 509.557 4,079.400 ↓ 1.0 947,556 1

Hash (cost=99,755.51..99,755.51 rows=947,551 width=68) (actual time=4,079.400..4,079.400 rows=947,556 loops=1)

  • Buckets: 1048576 Batches: 2 Memory Usage: 52682kB
  • Buffers: shared hit=841 read=89439, temp written=4868
13. 3,569.843 3,569.843 ↓ 1.0 947,556 1

Seq Scan on t_vehicule (cost=0.00..99,755.51 rows=947,551 width=68) (actual time=7.200..3,569.843 rows=947,556 loops=1)

  • Buffers: shared hit=841 read=89439
14.          

SubPlan (forHash Join)

15. 96.332 26,491.300 ↓ 0.0 0 96,332

Subquery Scan on rq_filtre_1 (cost=253.59..253.62 rows=1 width=0) (actual time=0.275..0.275 rows=0 loops=96,332)

  • Filter: ((rq_filtre_1.indice_rq = 0) AND (rq_filtre_1.iidcommande = t_commande.com_id) AND (rq_filtre_1.iidentite = t_commande.ent_id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=24949987 read=4
16. 0.000 26,394.968 ↑ 1.0 1 96,332

Limit (cost=253.59..253.60 rows=1 width=20) (actual time=0.274..0.274 rows=1 loops=96,332)

  • Buffers: shared hit=24949987 read=4
17. 481.660 26,394.968 ↑ 1.0 1 96,332

Sort (cost=253.59..253.60 rows=1 width=20) (actual time=0.274..0.274 rows=1 loops=96,332)

  • Sort Key: t_vehicule_3.veh_origine_referentiel DESC, t_commande_2.com_date_commande DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=24949987 read=4
18. 0.000 25,913.308 ↑ 1.0 1 96,332

Append (cost=1.14..253.58 rows=1 width=20) (actual time=0.054..0.269 rows=1 loops=96,332)

  • Buffers: shared hit=24949987 read=4
19. 96.332 25,913.308 ↑ 1.0 1 96,332

Nested Loop (cost=1.14..253.57 rows=1 width=20) (actual time=0.054..0.269 rows=1 loops=96,332)

  • Buffers: shared hit=24949987 read=4
20. 385.328 25,335.316 ↑ 1.0 1 96,332

Nested Loop (cost=0.71..245.13 rows=1 width=18) (actual time=0.048..0.263 rows=1 loops=96,332)

  • Buffers: shared hit=24564660 read=3
21. 15,702.116 15,702.116 ↓ 4.6 32 96,332

Index Scan using t_offre_idx_08 on t_offre t_offre_2 (cost=0.42..186.72 rows=7 width=10) (actual time=0.019..0.163 rows=32 loops=96,332)

  • Index Cond: ((ent_id = 5) AND ((col_login)::text = '00mp663'::text))
  • Filter: (off_type_offre = 2)
  • Rows Removed by Filter: 288
  • Buffers: shared hit=16858100 read=3
22. 9,247.872 9,247.872 ↓ 0.0 0 3,082,624

Index Scan using t_commande_idx_01 on t_commande t_commande_2 (cost=0.29..8.34 rows=1 width=18) (actual time=0.003..0.003 rows=0 loops=3,082,624)

  • Index Cond: ((ent_id = 5) AND (off_id = t_offre_2.off_id))
  • Filter: ((com_etat_commande = 1) AND (date_trunc('day'::text, com_date_commande) > to_timestamp('00010101 00:00:00'::text, 'YYYYMMDD HH24:MI:SS'::text)) AND (date_trunc('day'::text, com_date_commande) > date_trunc('day'::text, (now() - '1 year'::interval))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=7706560
23. 481.660 481.660 ↑ 1.0 1 96,332

Index Scan using t_vehicule_pk on t_vehicule t_vehicule_3 (cost=0.42..8.44 rows=1 width=6) (actual time=0.005..0.005 rows=1 loops=96,332)

  • Index Cond: (veh_id = t_offre_2.veh_id)
  • Buffers: shared hit=385327 read=1
24. 0.072 0.072 ↑ 1.0 1 1

Index Scan using t_client_pk on t_client (cost=0.42..1.20 rows=1 width=22) (actual time=0.072..0.072 rows=1 loops=1)

  • Index Cond: (cli_id = t_offre.cli_id)
  • Buffers: shared hit=3 read=1
25. 0.044 38.328 ↓ 104.0 104 1

Hash (cost=1,107.39..1,107.39 rows=1 width=14) (actual time=38.328..38.328 rows=104 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=364 read=15 dirtied=2
26. 0.024 38.284 ↓ 104.0 104 1

Subquery Scan on rq_rdv_livraison (cost=1,107.37..1,107.39 rows=1 width=14) (actual time=38.252..38.284 rows=104 loops=1)

  • Buffers: shared hit=364 read=15 dirtied=2
27. 0.084 38.260 ↓ 104.0 104 1

Sort (cost=1,107.37..1,107.38 rows=1 width=14) (actual time=38.251..38.260 rows=104 loops=1)

  • Sort Key: t_activite.act_date_debut DESC
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=364 read=15 dirtied=2
28. 38.176 38.176 ↓ 104.0 104 1

Index Scan using t_activite_idx_01 on t_activite (cost=0.42..1,107.36 rows=1 width=14) (actual time=17.339..38.176 rows=104 loops=1)

  • Index Cond: (act_type_activite = 2)
  • Filter: ((act_sous_type_activite = 3) AND (act_etat_activite = 0))
  • Rows Removed by Filter: 4060
  • Buffers: shared hit=364 read=15 dirtied=2
29. 0.080 0.201 ↑ 1.0 417 1

Hash (cost=19.17..19.17 rows=417 width=23) (actual time=0.201..0.201 rows=417 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
  • Buffers: shared hit=15
30. 0.121 0.121 ↑ 1.0 417 1

Seq Scan on t_collaborateur (cost=0.00..19.17 rows=417 width=23) (actual time=0.009..0.121 rows=417 loops=1)

  • Buffers: shared hit=15
31.          

SubPlan (forHash Join)

32. 0.010 0.010 ↓ 0.0 0 1

Index Scan using t_parc_vo_pk on t_parc_vo (cost=0.29..8.31 rows=1 width=6) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: ((ent_id = t_offre.ent_id_liaison) AND (pvo_id = t_offre.pvo_id))
33. 0.004 0.019 ↑ 1.0 1 1

Aggregate (cost=4.44..4.45 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=1)

  • Buffers: shared hit=3
34. 0.015 0.015 ↓ 0.0 0 1

Index Only Scan using t_offre_reprise_pk on t_offre_reprise (cost=0.42..4.44 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: ((ent_id_offre = t_offre.ent_id) AND (off_id = t_offre.off_id))
  • Heap Fetches: 0
  • Buffers: shared hit=3
35. 0.002 0.011 ↑ 1.0 1 1

Aggregate (cost=4.44..4.45 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

  • Buffers: shared hit=3
36. 0.009 0.009 ↓ 0.0 0 1

Index Only Scan using t_offre_reprise_pk on t_offre_reprise t_offre_reprise_1 (cost=0.42..4.44 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: ((ent_id_offre = t_offre.ent_id) AND (off_id = t_offre.off_id))
  • Heap Fetches: 0
  • Buffers: shared hit=3
37. 0.002 0.007 ↑ 1.0 1 1

Aggregate (cost=8.44..8.45 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=1)

  • Buffers: shared hit=3
38. 0.005 0.005 ↓ 0.0 0 1

Index Scan using t_offre_reprise_pk on t_offre_reprise t_offre_reprise_2 (cost=0.42..8.44 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((ent_id_offre = t_offre.ent_id) AND (off_id = t_offre.off_id))
  • Buffers: shared hit=3
39. 0.105 0.105 ↓ 0.0 0 1

Seq Scan on t_offre_externe (cost=0.00..40.46 rows=1 width=2) (actual time=0.105..0.105 rows=0 loops=1)

  • Filter: ((com_id = t_commande.com_id) AND (ent_id = t_commande.ent_id))
  • Rows Removed by Filter: 159
  • Buffers: shared hit=38
40. 0.001 298.189 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=48.54..39,567.18 rows=74 width=387) (actual time=298.189..298.189 rows=0 loops=1)

  • Buffers: shared hit=40027 read=181
41. 0.001 298.188 ↓ 0.0 0 1

Nested Loop (cost=48.54..39,566.25 rows=74 width=383) (actual time=298.188..298.188 rows=0 loops=1)

  • Buffers: shared hit=40027 read=181
42. 0.001 298.187 ↓ 0.0 0 1

Nested Loop Left Join (cost=48.27..39,530.69 rows=74 width=195) (actual time=298.187..298.187 rows=0 loops=1)

  • Buffers: shared hit=40027 read=181
43. 1.204 298.186 ↓ 0.0 0 1

Nested Loop (cost=47.84..38,933.83 rows=74 width=177) (actual time=298.186..298.186 rows=0 loops=1)

  • Join Filter: (SubPlan 1)
  • Rows Removed by Join Filter: 151
  • Buffers: shared hit=40027 read=181
44. 0.586 155.042 ↓ 1.0 151 1

Nested Loop (cost=47.42..905.06 rows=148 width=113) (actual time=30.676..155.042 rows=151 loops=1)

  • Buffers: shared hit=447 read=47
45. 0.488 1.191 ↓ 1.0 151 1

Hash Join (cost=47.12..58.38 rows=148 width=115) (actual time=0.599..1.191 rows=151 loops=1)

  • Hash Cond: ((t_commande_achat_reprise.ent_id = t_offre_achat_reprise.ent_id) AND (t_commande_achat_reprise.oar_id = t_offre_achat_reprise.oar_id))
  • Buffers: shared hit=41
46. 0.142 0.142 ↓ 1.0 151 1

Seq Scan on t_commande_achat_reprise (cost=0.00..10.48 rows=148 width=88) (actual time=0.014..0.142 rows=151 loops=1)

  • Buffers: shared hit=9
47. 0.142 0.561 ↑ 1.0 605 1

Hash (cost=38.05..38.05 rows=605 width=31) (actual time=0.561..0.561 rows=605 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
  • Buffers: shared hit=32
48. 0.419 0.419 ↑ 1.0 605 1

Seq Scan on t_offre_achat_reprise (cost=0.00..38.05 rows=605 width=31) (actual time=0.018..0.419 rows=605 loops=1)

  • Buffers: shared hit=32
49. 153.265 153.265 ↑ 1.0 1 151

Index Scan using t_reprise_pk on t_reprise (cost=0.29..5.72 rows=1 width=10) (actual time=1.015..1.015 rows=1 loops=151)

  • Index Cond: ((ent_id = t_offre_achat_reprise.ent_id) AND (rep_id = t_offre_achat_reprise.rep_id))
  • Buffers: shared hit=406 read=47
50. 89.392 89.392 ↑ 1.0 1 151

Index Scan using t_vehicule_pk on t_vehicule t_vehicule_1 (cost=0.42..3.33 rows=1 width=68) (actual time=0.591..0.592 rows=1 loops=151)

  • Index Cond: (veh_id = t_reprise.veh_id)
  • Buffers: shared hit=471 read=134
51.          

SubPlan (forNested Loop)

52. 0.151 52.548 ↓ 0.0 0 151

Subquery Scan on rq_filtre (cost=253.59..253.62 rows=1 width=0) (actual time=0.348..0.348 rows=0 loops=151)

  • Filter: ((rq_filtre.indice_rq = 1) AND (rq_filtre.iidcommande = t_commande_achat_reprise.coa_id) AND (rq_filtre.iidentite = t_commande_achat_reprise.ent_id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=39109
53. 0.000 52.397 ↑ 1.0 1 151

Limit (cost=253.59..253.60 rows=1 width=20) (actual time=0.347..0.347 rows=1 loops=151)

  • Buffers: shared hit=39109
54. 1.359 52.397 ↑ 1.0 1 151

Sort (cost=253.59..253.60 rows=1 width=20) (actual time=0.347..0.347 rows=1 loops=151)

  • Sort Key: t_vehicule_2.veh_origine_referentiel DESC, t_commande_1.com_date_commande DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=39109
55. 0.000 51.038 ↑ 1.0 1 151

Append (cost=1.14..253.58 rows=1 width=20) (actual time=0.075..0.338 rows=1 loops=151)

  • Buffers: shared hit=39109
56. 0.302 51.038 ↑ 1.0 1 151

Nested Loop (cost=1.14..253.57 rows=1 width=20) (actual time=0.075..0.338 rows=1 loops=151)

  • Buffers: shared hit=39109
57. 4.379 49.528 ↑ 1.0 1 151

Nested Loop (cost=0.71..245.13 rows=1 width=18) (actual time=0.066..0.328 rows=1 loops=151)

  • Buffers: shared hit=38505
58. 30.653 30.653 ↓ 4.6 32 151

Index Scan using t_offre_idx_08 on t_offre t_offre_1 (cost=0.42..186.72 rows=7 width=10) (actual time=0.024..0.203 rows=32 loops=151)

  • Index Cond: ((ent_id = 5) AND ((col_login)::text = '00mp663'::text))
  • Filter: (off_type_offre = 2)
  • Rows Removed by Filter: 288
  • Buffers: shared hit=26425
59. 14.496 14.496 ↓ 0.0 0 4,832

Index Scan using t_commande_idx_01 on t_commande t_commande_1 (cost=0.29..8.34 rows=1 width=18) (actual time=0.003..0.003 rows=0 loops=4,832)

  • Index Cond: ((ent_id = 5) AND (off_id = t_offre_1.off_id))
  • Filter: ((com_etat_commande = 1) AND (date_trunc('day'::text, com_date_commande) > to_timestamp('00010101 00:00:00'::text, 'YYYYMMDD HH24:MI:SS'::text)) AND (date_trunc('day'::text, com_date_commande) > date_trunc('day'::text, (now() - '1 year'::interval))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=12080
60. 1.208 1.208 ↑ 1.0 1 151

Index Scan using t_vehicule_pk on t_vehicule t_vehicule_2 (cost=0.42..8.44 rows=1 width=6) (actual time=0.008..0.008 rows=1 loops=151)

  • Index Cond: (veh_id = t_offre_1.veh_id)
  • Buffers: shared hit=604
61. 0.000 0.000 ↓ 0.0 0

Index Scan using t_client_idx_05 on t_client t_client_1 (cost=0.42..8.07 rows=1 width=22) (never executed)

  • Index Cond: (t_offre_achat_reprise.cli_id = cli_id)
62. 0.000 0.000 ↓ 0.0 0

Index Scan using t_collaborateur_pk on t_collaborateur t_collaborateur_1 (cost=0.27..0.42 rows=1 width=23) (never executed)

  • Index Cond: ((col_login)::text = (t_offre_achat_reprise.col_login)::text)
Planning time : 9.997 ms
Execution time : 32,316.253 ms