explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bmnl : Optimization for: plan #1ZOm

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.015 66.761 ↓ 6.5 13 1

Limit (cost=11,862.62..11,862.63 rows=2 width=96) (actual time=66.749..66.761 rows=13 loops=1)

2.          

Initplan (forLimit)

3. 0.011 10.231 ↑ 1.0 1 1

Subquery Scan on t1 (cost=1,830.88..1,830.91 rows=1 width=4) (actual time=10.230..10.231 rows=1 loops=1)

4. 0.010 10.220 ↑ 1.0 1 1

Aggregate (cost=1,830.88..1,830.89 rows=1 width=8) (actual time=10.220..10.220 rows=1 loops=1)

5. 0.017 10.210 ↑ 1.5 53 1

Unique (cost=1,829.53..1,829.92 rows=77 width=4) (actual time=10.173..10.210 rows=53 loops=1)

6. 0.057 10.193 ↓ 1.5 119 1

Sort (cost=1,829.53..1,829.72 rows=77 width=4) (actual time=10.172..10.193 rows=119 loops=1)

  • Sort Key: cfa_1.cli_id
  • Sort Method: quicksort Memory: 30kB
7. 0.107 10.136 ↓ 1.5 119 1

Nested Loop (cost=0.42..1,827.12 rows=77 width=4) (actual time=1.475..10.136 rows=119 loops=1)

8. 9.672 9.672 ↑ 1.0 119 1

Seq Scan on cfactura cfa_1 (cost=0.00..1,525.21 rows=122 width=20) (actual time=1.447..9.672 rows=119 loops=1)

  • Filter: ((cti_id = 20) AND (date(cfa_fecha) = '2019-01-07'::date))
  • Rows Removed by Filter: 31150
9. 0.357 0.357 ↑ 1.0 1 119

Index Scan using ccomproba_uk_llave on ccomproba ccm_1 (cost=0.42..2.46 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=119)

  • Index Cond: ((ccm_periodo = cfa_1.cfa_periodo) AND (cti_id = 20) AND (pve_id = cfa_1.pve_id) AND (ccm_numero = cfa_1.cfa_numero))
  • Filter: (ccm_estado = 2)
10. 0.090 56.515 ↑ 1.8 13 1

Sort (cost=10,031.72..10,031.78 rows=24 width=96) (actual time=56.514..56.515 rows=13 loops=1)

  • Sort Key: (random())
  • Sort Method: top-N heapsort Memory: 26kB
11. 0.045 56.425 ↓ 2.2 53 1

Subquery Scan on tt (cost=3,788.51..10,031.17 rows=24 width=96) (actual time=20.080..56.425 rows=53 loops=1)

12. 0.219 56.380 ↓ 2.2 53 1

Group (cost=3,788.51..10,030.87 rows=24 width=88) (actual time=20.077..56.380 rows=53 loops=1)

  • Group Key: cli.cli_id, tlf.tel_id
13.          

Initplan (forGroup)

14. 0.014 9.785 ↑ 1.0 1 1

Subquery Scan on t1_1 (cost=1,830.88..1,830.91 rows=1 width=4) (actual time=9.785..9.785 rows=1 loops=1)

15. 0.010 9.771 ↑ 1.0 1 1

Aggregate (cost=1,830.88..1,830.89 rows=1 width=8) (actual time=9.771..9.771 rows=1 loops=1)

16. 0.021 9.761 ↑ 1.5 53 1

Unique (cost=1,829.53..1,829.92 rows=77 width=4) (actual time=9.734..9.761 rows=53 loops=1)

17. 0.059 9.740 ↓ 1.5 119 1

Sort (cost=1,829.53..1,829.72 rows=77 width=4) (actual time=9.733..9.740 rows=119 loops=1)

  • Sort Key: cfa_6.cli_id
  • Sort Method: quicksort Memory: 30kB
18. 0.082 9.681 ↓ 1.5 119 1

Nested Loop (cost=0.42..1,827.12 rows=77 width=4) (actual time=1.189..9.681 rows=119 loops=1)

19. 9.242 9.242 ↑ 1.0 119 1

Seq Scan on cfactura cfa_6 (cost=0.00..1,525.21 rows=122 width=20) (actual time=1.176..9.242 rows=119 loops=1)

  • Filter: ((cti_id = 20) AND (date(cfa_fecha) = '2019-01-07'::date))
  • Rows Removed by Filter: 31150
20. 0.357 0.357 ↑ 1.0 1 119

Index Scan using ccomproba_uk_llave on ccomproba ccm_4 (cost=0.42..2.46 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=119)

  • Index Cond: ((ccm_periodo = cfa_6.cfa_periodo) AND (cti_id = 20) AND (pve_id = cfa_6.pve_id) AND (ccm_numero = cfa_6.cfa_numero))
  • Filter: (ccm_estado = 2)
21. 0.050 9.753 ↓ 2.2 53 1

Sort (cost=1,957.60..1,957.66 rows=24 width=20) (actual time=9.721..9.753 rows=53 loops=1)

  • Sort Key: cli.cli_id, tlf.tel_id
  • Sort Method: quicksort Memory: 29kB
22. 0.000 9.703 ↓ 2.2 53 1

Nested Loop Left Join (cost=1,830.38..1,957.05 rows=24 width=20) (actual time=9.192..9.703 rows=53 loops=1)

23. 0.012 9.550 ↓ 2.2 53 1

Nested Loop Left Join (cost=1,830.10..1,949.37 rows=24 width=20) (actual time=9.184..9.550 rows=53 loops=1)

24. 0.025 9.379 ↓ 2.2 53 1

Nested Loop (cost=1,829.81..1,940.55 rows=24 width=20) (actual time=9.178..9.379 rows=53 loops=1)

25. 0.024 9.195 ↑ 1.5 53 1

Unique (cost=1,829.53..1,829.92 rows=77 width=4) (actual time=9.159..9.195 rows=53 loops=1)

26. 0.047 9.171 ↓ 1.5 119 1

Sort (cost=1,829.53..1,829.72 rows=77 width=4) (actual time=9.158..9.171 rows=119 loops=1)

  • Sort Key: cfa.cli_id
  • Sort Method: quicksort Memory: 30kB
27. 0.089 9.124 ↓ 1.5 119 1

Nested Loop (cost=0.42..1,827.12 rows=77 width=4) (actual time=1.209..9.124 rows=119 loops=1)

28. 8.678 8.678 ↑ 1.0 119 1

Seq Scan on cfactura cfa (cost=0.00..1,525.21 rows=122 width=20) (actual time=1.198..8.678 rows=119 loops=1)

  • Filter: ((cti_id = 20) AND (date(cfa_fecha) = '2019-01-07'::date))
  • Rows Removed by Filter: 31150
29. 0.357 0.357 ↑ 1.0 1 119

Index Scan using ccomproba_uk_llave on ccomproba ccm (cost=0.42..2.46 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=119)

  • Index Cond: ((ccm_periodo = cfa.cfa_periodo) AND (cti_id = 20) AND (pve_id = cfa.pve_id) AND (ccm_numero = cfa.cfa_numero))
  • Filter: (ccm_estado = 2)
30. 0.159 0.159 ↑ 1.0 1 53

Index Scan using cliente_pkey on cliente cli (cost=0.28..1.42 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=53)

  • Index Cond: (cli_id = cfa.cli_id)
  • Filter: (cli_tipocli = 1)
31. 0.159 0.159 ↑ 1.0 1 53

Index Scan using pk_entidad on entidad ent (cost=0.28..0.36 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=53)

  • Index Cond: (ent_id = cli.ent_id)
32. 0.159 0.159 ↑ 1.0 1 53

Index Only Scan using pk_telefono on telefono tlf (cost=0.28..0.31 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=53)

  • Index Cond: (tel_id = ent.ent_telefono_principal)
  • Heap Fetches: 53
33.          

SubPlan (forGroup)

34. 0.159 15.158 ↑ 1.0 1 53

Aggregate (cost=137.88..137.89 rows=1 width=32) (actual time=0.286..0.286 rows=1 loops=53)

35. 0.055 14.999 ↓ 2.0 2 53

Nested Loop Semi Join (cost=0.99..137.87 rows=1 width=4) (actual time=0.268..0.283 rows=2 loops=53)

36. 2.650 2.650 ↓ 3.5 77 53

Index Scan using cfactura_llave_idx1 on cfactura cfa_2 (cost=0.29..25.95 rows=22 width=8) (actual time=0.006..0.050 rows=77 loops=53)

  • Index Cond: (cli_id = cli.cli_id)
37. 3.622 12.294 ↓ 0.0 0 4,098

Nested Loop (cost=0.70..4.89 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=4,098)

38. 8.196 8.196 ↓ 0.0 0 4,098

Index Scan using cfactura_pkey on cfactura cfa_3 (cost=0.29..2.41 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=4,098)

  • Index Cond: (cfa_id = cfa_2.cfa_id)
  • Filter: ((cti_id = 20) AND (date(cfa_fecha) = '2019-01-07'::date))
  • Rows Removed by Filter: 1
39. 0.476 0.476 ↑ 1.0 1 119

Index Scan using ccomproba_uk_llave on ccomproba ccm_2 (cost=0.42..2.46 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=119)

  • Index Cond: ((ccm_periodo = cfa_3.cfa_periodo) AND (cti_id = 20) AND (pve_id = cfa_3.pve_id) AND (ccm_numero = cfa_3.cfa_numero))
  • Filter: (ccm_estado = 2)
40. 0.159 21.465 ↑ 1.0 1 53

Aggregate (cost=122.19..122.20 rows=1 width=32) (actual time=0.405..0.405 rows=1 loops=53)

41. 0.161 21.306 ↓ 2.0 2 53

Nested Loop (cost=1.57..122.18 rows=1 width=4) (actual time=0.382..0.402 rows=2 loops=53)

42. 0.000 20.935 ↓ 2.0 2 53

Nested Loop Semi Join (cost=1.28..121.83 rows=1 width=4) (actual time=0.379..0.395 rows=2 loops=53)

  • Join Filter: (cfa_4.cfa_id = cfa_5.cfa_id)
43. 1.927 12.402 ↓ 3.8 57 53

Nested Loop (cost=0.57..76.88 rows=15 width=12) (actual time=0.027..0.234 rows=57 loops=53)

44. 2.279 2.279 ↓ 3.5 77 53

Index Scan using cfactura_llave_idx1 on cfactura cfa_4 (cost=0.29..25.95 rows=22 width=4) (actual time=0.004..0.043 rows=77 loops=53)

  • Index Cond: (cli_id = cli.cli_id)
45. 8.196 8.196 ↑ 1.0 1 4,098

Index Scan using dguiarem_llave_cfactura on dguiarem dgui (cost=0.29..2.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=4,098)

  • Index Cond: (cfa_id = cfa_4.cfa_id)
46. 2.589 9.027 ↓ 0.0 0 3,009

Nested Loop (cost=0.70..2.84 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=3,009)

47. 6.018 6.018 ↓ 0.0 0 3,009

Index Scan using cfactura_pkey on cfactura cfa_5 (cost=0.29..0.37 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=3,009)

  • Index Cond: (cfa_id = dgui.cfa_id)
  • Filter: ((cti_id = 20) AND (date(cfa_fecha) = '2019-01-07'::date))
  • Rows Removed by Filter: 1
48. 0.420 0.420 ↑ 1.0 1 105

Index Scan using ccomproba_uk_llave on ccomproba ccm_3 (cost=0.42..2.46 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=105)

  • Index Cond: ((ccm_periodo = cfa_5.cfa_periodo) AND (cti_id = 20) AND (pve_id = cfa_5.pve_id) AND (ccm_numero = cfa_5.cfa_numero))
  • Filter: (ccm_estado = 2)
49. 0.210 0.210 ↑ 1.0 1 105

Index Scan using cguiarem_pkey on cguiarem cgui (cost=0.29..0.34 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=105)

  • Index Cond: (cgui_id = dgui.cgui_id)