explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J0x8

Settings
# exclusive inclusive rows x rows loops node
1. 1,411.295 1,411.295 ↓ 201.8 34,300 1

CTE Scan on table_data (cost=1,362,039.24..1,362,042.64 rows=170 width=1,434) (actual time=17.128..1,411.295 rows=34,300 loops=1)

2.          

CTE table_data

3. 101.906 1,367.755 ↓ 201.8 34,300 1

Nested Loop Left Join (cost=1,298.35..1,362,039.24 rows=170 width=324) (actual time=17.124..1,367.755 rows=34,300 loops=1)

  • Filter: (((array_agg(c.cdcentro)) IS NULL) OR (array_length((array_agg(c.cdcentro)), 1) <> 0))
4. 10.968 408.349 ↓ 1.0 34,300 1

Nested Loop Left Join (cost=1,298.06..702,165.69 rows=33,912 width=231) (actual time=17.008..408.349 rows=34,300 loops=1)

5. 39.833 260.181 ↓ 1.0 34,300 1

Nested Loop Left Join (cost=1,287.88..355,208.25 rows=33,912 width=199) (actual time=16.993..260.181 rows=34,300 loops=1)

6. 18.337 83.148 ↓ 1.0 34,300 1

Hash Left Join (cost=1,277.53..2,817.69 rows=33,912 width=167) (actual time=16.963..83.148 rows=34,300 loops=1)

  • Hash Cond: ((pm.tpprovee)::text = (pt.tpprovee)::text)
7. 30.597 64.766 ↓ 1.0 34,300 1

Hash Left Join (cost=1,274.82..2,721.00 rows=33,912 width=161) (actual time=16.900..64.766 rows=34,300 loops=1)

  • Hash Cond: (((b.cdama)::text = (pm.cdama)::text) AND ((b.cdprovee)::text = (pm.cdprovee)::text))
8. 17.392 17.392 ↓ 1.0 34,300 1

Seq Scan on base b (cost=0.00..1,268.12 rows=33,912 width=113) (actual time=0.015..17.392 rows=34,300 loops=1)

9. 8.392 16.777 ↑ 1.0 33,534 1

Hash (cost=771.53..771.53 rows=33,553 width=48) (actual time=16.776..16.777 rows=33,534 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2889kB
10. 8.385 8.385 ↑ 1.0 33,534 1

Seq Scan on proveedor_manual pm (cost=0.00..771.53 rows=33,553 width=48) (actual time=0.005..8.385 rows=33,534 loops=1)

11. 0.018 0.045 ↓ 1.1 85 1

Hash (cost=1.76..1.76 rows=76 width=14) (actual time=0.044..0.045 rows=85 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
12. 0.027 0.027 ↓ 1.1 85 1

Seq Scan on proveedor_tipo pt (cost=0.00..1.76 rows=76 width=14) (actual time=0.016..0.027 rows=85 loops=1)

13. 0.000 137.200 ↓ 0.0 0 34,300

GroupAggregate (cost=10.34..10.37 rows=1 width=51) (actual time=0.004..0.004 rows=0 loops=34,300)

  • Group Key: a_1.cdama, a_1.cdprovee, a_1.cdusuario
14. 34.300 137.200 ↓ 0.0 0 34,300

Sort (cost=10.34..10.35 rows=1 width=23) (actual time=0.004..0.004 rows=0 loops=34,300)

  • Sort Key: a_1.cdusuario
  • Sort Method: quicksort Memory: 25kB
15. 34.287 102.900 ↓ 0.0 0 34,300

Nested Loop Left Join (cost=0.14..10.33 rows=1 width=23) (actual time=0.003..0.003 rows=0 loops=34,300)

16. 68.600 68.600 ↓ 0.0 0 34,300

Seq Scan on asignacion a_1 (cost=0.00..1.23 rows=1 width=23) (actual time=0.002..0.002 rows=0 loops=34,300)

  • 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: 13
17. 0.013 0.013 ↑ 1.0 1 1

Index Scan using unique_siniestro on siniestro s (cost=0.14..8.16 rows=1 width=11) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (((cdama)::text = (a_1.cdama)::text) AND ((cdama)::text = (pm.cdama)::text) AND (rgpresta = a_1.rgpresta))
18. 34.300 137.200 ↓ 0.0 0 34,300

GroupAggregate (cost=10.19..10.21 rows=1 width=42) (actual time=0.004..0.004 rows=0 loops=34,300)

  • Group Key: s_1.cdama, asig.cdprovee
19. 0.000 102.900 ↓ 0.0 0 34,300

Sort (cost=10.19..10.19 rows=1 width=14) (actual time=0.003..0.003 rows=0 loops=34,300)

  • Sort Key: s_1.cdama
  • Sort Method: quicksort Memory: 25kB
20. 34.281 102.900 ↓ 0.0 0 34,300

Nested Loop Left Join (cost=0.28..10.18 rows=1 width=14) (actual time=0.003..0.003 rows=0 loops=34,300)

21. 68.600 68.600 ↓ 0.0 0 34,300

Seq Scan on asignacion asig (cost=0.00..1.23 rows=1 width=14) (actual time=0.002..0.002 rows=0 loops=34,300)

  • 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: 13
22. 0.004 0.019 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.28..8.94 rows=1 width=11) (actual time=0.019..0.019 rows=1 loops=1)

23. 0.005 0.005 ↑ 1.0 1 1

Index Scan using unique_siniestro on siniestro s_1 (cost=0.14..8.16 rows=1 width=11) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (((cdama)::text = (asig.cdama)::text) AND ((cdama)::text = (pm.cdama)::text) AND (rgpresta = asig.rgpresta))
24. 0.010 0.010 ↑ 1.0 1 1

Index Only Scan using cliente_pk on cliente cli (cost=0.14..0.66 rows=1 width=7) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((cdcliente = s_1.cdcliente) AND (cdama = (s_1.cdama)::text) AND (cdama = (pm.cdama)::text))
  • Heap Fetches: 0
25. 68.600 548.800 ↑ 1.0 1 34,300

GroupAggregate (cost=0.29..19.36 rows=1 width=42) (actual time=0.016..0.016 rows=1 loops=34,300)

  • Group Key: cpm.cdama, cpm.cdprovee
26. 32.018 480.200 ↑ 1.0 1 34,300

Nested Loop (cost=0.29..19.34 rows=1 width=13) (actual time=0.012..0.014 rows=1 loops=34,300)

27. 205.800 205.800 ↑ 2.0 1 34,300

Seq Scan on centro c (cost=0.00..1.65 rows=2 width=6) (actual time=0.004..0.006 rows=1 loops=34,300)

  • Filter: ((cdama)::text = (pm.cdama)::text)
  • Rows Removed by Filter: 51
28. 208.132 242.382 ↑ 1.0 1 34,626

Index Only Scan using centros_proveedor_manual_pk on centro_proveedor_manual cpm (cost=0.29..8.84 rows=1 width=13) (actual time=0.007..0.007 rows=1 loops=34,626)

  • Index Cond: ((cdcentro = (c.cdcentro)::text) AND (cdprovee = (pm.cdprovee)::text) AND (cdama = (pm.cdama)::text))
  • Filter: (NOT (SubPlan 4))
  • Heap Fetches: 34058
29.          

SubPlan (for Index Only Scan)

30. 34.250 34.250 ↓ 0.0 0 34,250

Seq Scan on public_holiday ph (cost=0.00..1.04 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=34,250)

  • Filter: (((cpm.cdcentro)::text = (cdcentro)::text) AND ((cpm.cdama)::text = (cdama)::text))
  • Rows Removed by Filter: 3
31.          

SubPlan (for Nested Loop Left Join)

32. 34.300 137.200 ↓ 0.0 0 34,300

HashSetOp Except (cost=0.00..4.54 rows=100 width=36) (actual time=0.004..0.004 rows=0 loops=34,300)

33. 0.000 102.900 ↑ 50.0 4 34,300

Append (cost=0.00..4.04 rows=200 width=36) (actual time=0.001..0.003 rows=4 loops=34,300)

34. 34.300 34.300 ↓ 0.0 0 34,300

Subquery Scan on *SELECT* 1 (cost=0.00..1.52 rows=100 width=36) (actual time=0.001..0.001 rows=0 loops=34,300)

35. 0.000 0.000 ↓ 0.0 0 34,300

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.000..0.000 rows=0 loops=34,300)

36. 0.000 0.000 ↑ 1.0 1 34,300

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=34,300)

37. 34.300 68.600 ↑ 25.0 4 34,300

Subquery Scan on *SELECT* 2 (cost=0.00..1.52 rows=100 width=36) (actual time=0.001..0.002 rows=4 loops=34,300)

38. 34.300 34.300 ↑ 25.0 4 34,300

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.000..0.001 rows=4 loops=34,300)

39. 0.000 0.000 ↑ 1.0 1 34,300

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=34,300)

40. 34.300 102.900 ↓ 0.0 0 34,300

GroupAggregate (cost=0.00..1.26 rows=1 width=51) (actual time=0.003..0.003 rows=0 loops=34,300)

  • Group Key: a.cdama, a.cdprovee, a.cdbase
41. 68.600 68.600 ↓ 0.0 0 34,300

Seq Scan on asignacion a (cost=0.00..1.23 rows=1 width=23) (actual time=0.002..0.002 rows=0 loops=34,300)

  • 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: 13
42. 0.000 68.600 ↓ 0.0 0 34,300

GroupAggregate (cost=0.27..8.31 rows=1 width=41) (actual time=0.002..0.002 rows=0 loops=34,300)

  • Group Key: pmov.cdama, pmov.cdprovee
43. 68.600 68.600 ↓ 0.0 0 34,300

Index Only Scan using unique_proveedor_movil on proveedor_movil pmov (cost=0.27..8.29 rows=1 width=17) (actual time=0.002..0.002 rows=0 loops=34,300)

  • Index Cond: ((cdama = (pm.cdama)::text) AND (cdprovee = (pm.cdprovee)::text))
  • Heap Fetches: 92
Planning time : 1.998 ms
Execution time : 1,417.515 ms