explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lpLE

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

Nested Loop Left Join (cost=1,362,039.25..1,362,047.76 rows=170 width=1,466) (actual time=17.200..1,457.047 rows=34,300 loops=1)

2.          

CTE geojson

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

4. 1,417.555 1,417.555 ↓ 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.196..1,417.555 rows=34,300 loops=1)

5.          

CTE table_data

6. 108.871 1,377.675 ↓ 201.8 34,300 1

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

  • Filter: (((array_agg(c.cdcentro)) IS NULL) OR (array_length((array_agg(c.cdcentro)), 1) <> 0))
7. 14.602 411.304 ↓ 1.0 34,300 1

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

8. 7.803 259.502 ↓ 1.0 34,300 1

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

9. 17.805 80.199 ↓ 1.0 34,300 1

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

  • Hash Cond: ((pm.tpprovee)::text = (pt.tpprovee)::text)
10. 30.791 62.357 ↓ 1.0 34,300 1

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

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

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

12. 8.354 16.875 ↑ 1.0 33,534 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2889kB
13. 8.521 8.521 ↑ 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.521 rows=33,534 loops=1)

14. 0.016 0.037 ↓ 1.1 85 1

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

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

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

16. 34.300 171.500 ↓ 0.0 0 34,300

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

  • Group Key: a_1.cdama, a_1.cdprovee, a_1.cdusuario
17. 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
18. 34.289 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)

19. 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
20. 0.011 0.011 ↑ 1.0 1 1

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

  • Index Cond: (((cdama)::text = (a_1.cdama)::text) AND ((cdama)::text = (pm.cdama)::text) AND (rgpresta = a_1.rgpresta))
21. 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
22. 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
23. 34.285 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)

24. 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
25. 0.004 0.015 ↑ 1.0 1 1

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

26. 0.004 0.004 ↑ 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.004 rows=1 loops=1)

  • Index Cond: (((cdama)::text = (asig.cdama)::text) AND ((cdama)::text = (pm.cdama)::text) AND (rgpresta = asig.rgpresta))
27. 0.007 0.007 ↑ 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.007..0.007 rows=1 loops=1)

  • Index Cond: ((cdcliente = s_1.cdcliente) AND (cdama = (s_1.cdama)::text) AND (cdama = (pm.cdama)::text))
  • Heap Fetches: 0
28. 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
29. 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)

30. 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
31. 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 5))
  • Heap Fetches: 34058
32.          

SubPlan (for Index Only Scan)

33. 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
34.          

SubPlan (for Nested Loop Left Join)

35. 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)

36. 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)

37. 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)

38. 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)

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 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)

41. 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)

42. 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)

43. 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
44. 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
45. 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
46. 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: 0
47. 0.000 0.000 ↑ 1.0 1 34,300

CTE Scan on geojson (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=34,300)

Planning time : 2.153 ms
Execution time : 1,464.039 ms