explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LVds

Settings
# exclusive inclusive rows x rows loops node
1. 40.058 40.058 ↓ 0.0 0 1

CTE Scan on table_data (cost=3,327.64..3,327.66 rows=1 width=1,669) (actual time=40.058..40.058 rows=0 loops=1)

  • Filter: (mapfre_id = 'ARP20000854950317'::text)
  • Rows Removed by Filter: 149
2.          

CTE table_data

3. 0.486 39.865 ↓ 149.0 149 1

Nested Loop Left Join (cost=44.43..3,327.64 rows=1 width=582) (actual time=0.829..39.865 rows=149 loops=1)

  • Filter: (((array_agg(c.cdcentro)) IS NULL) OR (array_length((array_agg(c.cdcentro)), 1) <> 0))
4. 0.021 1.533 ↓ 1.0 149 1

Nested Loop Left Join (cost=44.29..510.20 rows=147 width=208) (actual time=0.457..1.533 rows=149 loops=1)

5. 0.060 0.767 ↓ 1.0 149 1

Hash Left Join (cost=41.20..48.99 rows=147 width=176) (actual time=0.439..0.767 rows=149 loops=1)

  • Hash Cond: (((pm.cdama)::text = (e.cdama)::text) AND ((pw.cdestadoprov)::text = (e.cdestadoprov)::text))
6. 0.077 0.688 ↓ 1.0 149 1

Hash Left Join (cost=27.20..34.21 rows=147 width=144) (actual time=0.414..0.688 rows=149 loops=1)

  • Hash Cond: (((pm.cdama)::text = (pw.cdama)::text) AND ((pm.cdprovee)::text = (pw.cdprovee)::text))
7. 0.135 0.592 ↓ 1.0 149 1

Hash Left Join (cost=25.65..31.89 rows=147 width=142) (actual time=0.389..0.592 rows=149 loops=1)

  • Hash Cond: (((b.cdama)::text = (pm.cdama)::text) AND ((b.cdprovee)::text = (pm.cdprovee)::text))
8. 0.090 0.090 ↓ 1.0 149 1

Seq Scan on base b (cost=0.00..5.47 rows=147 width=111) (actual time=0.013..0.090 rows=149 loops=1)

9. 0.164 0.367 ↓ 1.0 707 1

Hash (cost=15.06..15.06 rows=706 width=41) (actual time=0.366..0.367 rows=707 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
10. 0.203 0.203 ↓ 1.0 707 1

Seq Scan on proveedor_manual pm (cost=0.00..15.06 rows=706 width=41) (actual time=0.026..0.203 rows=707 loops=1)

11. 0.005 0.019 ↑ 1.0 22 1

Hash (cost=1.22..1.22 rows=22 width=12) (actual time=0.019..0.019 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.014 0.014 ↑ 1.0 22 1

Seq Scan on proveedor_web pw (cost=0.00..1.22 rows=22 width=12) (actual time=0.009..0.014 rows=22 loops=1)

13. 0.004 0.019 ↑ 14.5 11 1

Hash (cost=11.60..11.60 rows=160 width=468) (actual time=0.019..0.019 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.015 0.015 ↑ 14.5 11 1

Seq Scan on estado_proveedor e (cost=0.00..11.60 rows=160 width=468) (actual time=0.013..0.015 rows=11 loops=1)

15. 0.149 0.745 ↓ 0.0 0 149

GroupAggregate (cost=3.09..3.12 rows=1 width=43) (actual time=0.005..0.005 rows=0 loops=149)

  • Group Key: s_2.cdama, asig.cdprovee
16. 0.149 0.596 ↓ 0.0 0 149

Sort (cost=3.09..3.10 rows=1 width=15) (actual time=0.004..0.004 rows=0 loops=149)

  • Sort Key: s_2.cdama
  • Sort Method: quicksort Memory: 25kB
17. 0.000 0.447 ↓ 0.0 0 149

Nested Loop Left Join (cost=0.00..3.08 rows=1 width=15) (actual time=0.003..0.003 rows=0 loops=149)

  • Join Filter: (((s_2.cdama)::text = (cli.cdama)::text) AND (s_2.cdcliente = cli.cdcliente))
  • Rows Removed by Join Filter: 0
18. 0.136 0.447 ↓ 0.0 0 149

Nested Loop Left Join (cost=0.00..2.05 rows=1 width=15) (actual time=0.003..0.003 rows=0 loops=149)

  • Join Filter: (((s_2.cdama)::text = (asig.cdama)::text) AND (s_2.rgpresta = asig.rgpresta))
  • Rows Removed by Join Filter: 0
19. 0.298 0.298 ↓ 0.0 0 149

Seq Scan on asignacion asig (cost=0.00..1.00 rows=1 width=15) (actual time=0.002..0.002 rows=0 loops=149)

  • Filter: ((cdusuario IS NULL) AND ((cdama)::text = (pm.cdama)::text) AND ((cdprovee)::text = (pm.cdprovee)::text) AND ((cdbase)::text = (b.cdbase)::text))
  • Rows Removed by Filter: 1
20. 0.013 0.013 ↑ 1.0 2 1

Seq Scan on siniestro s_2 (cost=0.00..1.02 rows=2 width=11) (actual time=0.012..0.013 rows=2 loops=1)

  • Filter: ((cdama)::text = (pm.cdama)::text)
21. 0.025 0.025 ↓ 3.0 3 1

Seq Scan on cliente cli (cost=0.00..1.01 rows=1 width=7) (actual time=0.025..0.025 rows=3 loops=1)

  • Filter: ((cdama)::text = (pm.cdama)::text)
  • Rows Removed by Filter: 2
22. 0.149 1.788 ↑ 1.0 1 149

GroupAggregate (cost=0.14..18.86 rows=1 width=42) (actual time=0.012..0.012 rows=1 loops=149)

  • Group Key: cpm.cdama, cpm.cdprovee
23. 0.211 1.639 ↑ 1.0 1 149

Nested Loop (cost=0.14..18.84 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=149)

  • Join Filter: ((c.cdcentro)::text = (cpm.cdcentro)::text)
  • Rows Removed by Join Filter: 0
24. 0.805 0.894 ↑ 1.0 1 149

Index Only Scan using centros_proveedor_manual_pk on centro_proveedor_manual cpm (cost=0.14..17.17 rows=1 width=13) (actual time=0.006..0.006 rows=1 loops=149)

  • Index Cond: ((cdprovee = (pm.cdprovee)::text) AND (cdama = (pm.cdama)::text))
  • Filter: (NOT (SubPlan 5))
  • Heap Fetches: 89
25.          

SubPlan (forIndex Only Scan)

26. 0.089 0.089 ↓ 0.0 0 89

Index Only Scan using pk_public_holiday on public_holiday ph (cost=0.15..8.17 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=89)

  • Index Cond: ((cdama = (cpm.cdama)::text) AND (cdcentro = (cpm.cdcentro)::text))
  • Heap Fetches: 0
27. 0.534 0.534 ↑ 1.0 2 89

Seq Scan on centro c (cost=0.00..1.65 rows=2 width=5) (actual time=0.003..0.006 rows=2 loops=89)

  • Filter: ((cdama)::text = (pm.cdama)::text)
  • Rows Removed by Filter: 36
28.          

SubPlan (forNested Loop Left Join)

29. 0.149 0.298 ↓ 0.0 0 149

GroupAggregate (cost=0.00..1.03 rows=1 width=50) (actual time=0.002..0.002 rows=0 loops=149)

  • Group Key: a.cdama, a.cdprovee, a.cdbase
30. 0.149 0.149 ↓ 0.0 0 149

Seq Scan on asignacion a (cost=0.00..1.00 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=149)

  • Filter: ((cdusuario IS NULL) AND ((cdama)::text = (pm.cdama)::text) AND ((cdprovee)::text = (pm.cdprovee)::text) AND ((cdbase)::text = (b.cdbase)::text))
  • Rows Removed by Filter: 1
31. 0.149 1.937 ↓ 0.0 0 149

GroupAggregate (cost=0.00..4.74 rows=2 width=42) (actual time=0.013..0.013 rows=0 loops=149)

  • Group Key: pmov.cdama, pmov.cdprovee
32. 1.788 1.788 ↓ 0.0 0 149

Seq Scan on proveedor_movil pmov (cost=0.00..4.69 rows=2 width=18) (actual time=0.012..0.012 rows=0 loops=149)

  • Filter: (((cdama)::text = (pm.cdama)::text) AND ((cdprovee)::text = (pm.cdprovee)::text))
  • Rows Removed by Filter: 46
33. 0.149 2.086 ↑ 1.0 1 149

Aggregate (cost=10.62..10.63 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=149)

34. 0.149 1.937 ↓ 0.0 0 149

Subquery Scan on aux (cost=10.52..10.61 rows=4 width=27) (actual time=0.013..0.013 rows=0 loops=149)

35. 0.000 1.788 ↓ 0.0 0 149

HashAggregate (cost=10.52..10.57 rows=4 width=45) (actual time=0.012..0.012 rows=0 loops=149)

  • Group Key: rpm.cdama, rpm.cdprovee, r.nbred
36. 0.139 1.788 ↓ 0.0 0 149

Hash Left Join (cost=8.79..10.42 rows=10 width=32) (actual time=0.011..0.012 rows=0 loops=149)

  • Hash Cond: (((rpm.cdama)::text = (s.cdama)::text) AND (rpm.cdservicio = s.cdservicio))
37. 0.144 1.341 ↓ 0.0 0 149

Hash Left Join (cost=2.10..3.69 rows=9 width=17) (actual time=0.009..0.009 rows=0 loops=149)

  • Hash Cond: (((rpm.cdama)::text = (r.cdama)::text) AND ((rpm.cdred)::text = (r.cdred)::text))
38. 1.043 1.043 ↓ 0.0 0 149

Seq Scan on servicio_proveedor_manual_red rpm (cost=0.00..1.54 rows=9 width=16) (actual time=0.007..0.007 rows=0 loops=149)

  • Filter: (((cdama)::text = (pm.cdama)::text) AND ((cdprovee)::text = (pm.cdprovee)::text))
  • Rows Removed by Filter: 41
39. 0.028 0.154 ↓ 8.0 8 7

Hash (cost=2.09..2.09 rows=1 width=22) (actual time=0.022..0.022 rows=8 loops=7)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.126 0.126 ↓ 8.0 8 7

Seq Scan on red r (cost=0.00..2.09 rows=1 width=22) (actual time=0.010..0.018 rows=8 loops=7)

  • Filter: ((cdama)::text = (pm.cdama)::text)
  • Rows Removed by Filter: 92
41. 0.140 0.308 ↑ 1.1 82 7

Hash (cost=5.35..5.35 rows=89 width=26) (actual time=0.044..0.044 rows=82 loops=7)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
42. 0.168 0.168 ↑ 1.1 82 7

Seq Scan on servicio s (cost=0.00..5.35 rows=89 width=26) (actual time=0.003..0.024 rows=82 loops=7)

  • Filter: ((cdama)::text = (pm.cdama)::text)
  • Rows Removed by Filter: 56
43. 3.129 31.737 ↑ 1.0 1 149

Aggregate (cost=24.37..24.38 rows=1 width=32) (actual time=0.213..0.213 rows=1 loops=149)

44. 0.298 28.608 ↓ 5.0 5 149

Subquery Scan on x (cost=24.32..24.36 rows=1 width=45) (actual time=0.188..0.192 rows=5 loops=149)

45. 0.596 28.310 ↓ 5.0 5 149

GroupAggregate (cost=24.32..24.35 rows=1 width=71) (actual time=0.187..0.190 rows=5 loops=149)

  • Group Key: bs.cdbase, bs.cdprovee, bs.cdama, h.cddiasem, s_1.nbservicio
46. 0.596 27.714 ↓ 5.0 5 149

Sort (cost=24.32..24.32 rows=1 width=51) (actual time=0.186..0.186 rows=5 loops=149)

  • Sort Key: h.cddiasem, s_1.nbservicio
  • Sort Method: quicksort Memory: 25kB
47. 0.792 27.118 ↓ 5.0 5 149

Nested Loop Left Join (cost=16.41..24.31 rows=1 width=51) (actual time=0.120..0.182 rows=5 loops=149)

  • Join Filter: (((h.cdama)::text = (bs.cdama)::text) AND ((h.cdprovee)::text = (bs.cdprovee)::text) AND ((h.cdbase)::text = (bs.cdbase)::text) AND (h.cdservicio = bs.cdservicio))
  • Rows Removed by Join Filter: 1
48. 2.384 20.413 ↓ 4.0 4 149

Hash Right Join (cost=16.41..22.24 rows=1 width=41) (actual time=0.109..0.137 rows=4 loops=149)

  • Hash Cond: (((s_1.cdama)::text = (bs.cdama)::text) AND (s_1.cdservicio = bs.cdservicio))
49. 2.980 2.980 ↓ 1.0 93 149

Seq Scan on servicio s_1 (cost=0.00..5.35 rows=89 width=26) (actual time=0.003..0.020 rows=93 loops=149)

  • Filter: ((cdama)::text = (pm.cdama)::text)
  • Rows Removed by Filter: 45
50. 0.298 15.049 ↓ 4.0 4 149

Hash (cost=16.39..16.39 rows=1 width=22) (actual time=0.101..0.101 rows=4 loops=149)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 14.751 14.751 ↓ 4.0 4 149

Seq Scan on base_servicio bs (cost=0.00..16.39 rows=1 width=22) (actual time=0.049..0.099 rows=4 loops=149)

  • Filter: (((cdama)::text = (pm.cdama)::text) AND ((cdprovee)::text = (pm.cdprovee)::text) AND ((cdbase)::text = (b.cdbase)::text))
  • Rows Removed by Filter: 653
52. 5.913 5.913 ↓ 0.0 0 657

Seq Scan on horario h (cost=0.00..2.05 rows=1 width=36) (actual time=0.009..0.009 rows=0 loops=657)

  • Filter: (((cdama)::text = (pm.cdama)::text) AND ((cdprovee)::text = (pm.cdprovee)::text) AND ((cdbase)::text = (b.cdbase)::text))
  • Rows Removed by Filter: 60
Planning time : 4.506 ms
Execution time : 40.431 ms