join super lento

Hola Lista
Les tengo el siguiente desafio pues no he podido dar con el tema, tengo dos
tablas

CREATE TABLE ath_tecnicosv2
(
descripcionmovimiento character varying(160),
fechamto date,
horamto character varying(8),
fechaact character varying(8),
horaact character varying(8),
usuario character varying(50),
identificacionusuario bigint,
tipomovimientosusuario character varying(25),
id bigserial NOT NULL
)
WITH (
OIDS=FALSE
);

-- tamaño: 1639200 registros

CREATE UNIQUE INDEX idx_u_ath_tecnicosv2_id
ON ath_tecnicosv2
USING btree
(id);

CREATE INDEX idx_ath_tecnicosv2_comp
ON ath_tecnicosv2
USING btree
(fechamto, identificacionusuario, descripcionmovimiento COLLATE
pg_catalog."default", horamto COLLATE pg_catalog."default", fechaact
COLLATE pg_catalog."default", horaact COLLATE pg_catalog."default", usuario
COLLATE pg_catalog."default", tipomovimientosusuario COLLATE
pg_catalog."default");

CREATE INDEX idx_ath_tecnicosv2_fecha2
ON public.ath_tecnicosv2
USING btree
(fechamto asc, identificacionusuario asc,id);

CREATE TABLE ath_cajerosv2
(
fecha date,
nombre character varying(60),
regionalath character varying(24),
regionaltdv character varying(54),
tiposolicitud character varying(10),
id_usuario character varying(4),
identificacion bigint,
usuario character varying(36),
cant bigint,
id bigserial NOT NULL
)
WITH (
OIDS=FALSE
);

-- tamaño: 132050 registros

CREATE UNIQUE INDEX idx_u_ath_cajerosv2_id
ON ath_cajerosv2
USING btree
(id);

CREATE INDEX idx_ath_cajerosv2_comp
ON ath_cajerosv2
USING btree
(fecha, identificacion, nombre COLLATE pg_catalog."default", regionalath
COLLATE pg_catalog."default", regionaltdv COLLATE pg_catalog."default",
tiposolicitud COLLATE pg_catalog."default");

CREATE INDEX idx_ath_cajerosv2_fecha2
ON public.ath_cajerosv2
USING btree
(fecha asc, identificacion asc,id);

-- consulta Básica Inicial:

select t.descripcionmovimiento, t.fechamto, t.horamto, t.fechaact,
t.horaact, t.usuario, t.identificacionusuario, t.tipomovimientosusuario,
c.fecha, c.nombre, c.regionalath, c.regionaltdv, c.tiposolicitud, 1 as cant
from public.ath_tecnicosv2 t
join public.ath_cajerosv2 c on t.identificacionusuario=c.identificacion and
t.fechamto=c.fecha
where fecha >= '20160218' and fechamto >= '20160218'
group by t.descripcionmovimiento, t.fechamto, t.horamto, t.fechaact,
t.horaact, t.usuario, t.identificacionusuario, t.tipomovimientosusuario,
c.fecha, c.nombre, c.regionalath, c.regionaltdv, c.tiposolicitud,1

-- plan de ejecucion

Group (cost=60388.03..65631.12 rows=749012 width=179)
-> Sort (cost=60388.03..60762.54 rows=749012 width=179)
Sort Key: t.descripcionmovimiento, t.fechamto, t.horamto,
t.fechaact, t.horaact, t.usuario, t.identificacionusuario,
t.tipomovimientosusuario, c.fecha, c.nombre, c.regionalath, c.regionaltdv,
c.tiposolicitud
-> Merge Join (cost=7.77..6784.44 rows=749012 width=179)
Merge Cond: ((c.fecha = t.fechamto) AND (c.identificacion =
t.identificacionusuario))
-> Index Only Scan using idx_ath_cajerosv2_comp on
ath_cajerosv2 c (cost=0.08..169.88 rows=9072 width=64)
Index Cond: (fecha >= '2016-02-18'::date)
-> Materialize (cost=0.11..2533.11 rows=94366 width=123)
-> Index Only Scan using idx_ath_tecnicosv2_comp on
ath_tecnicosv2 t (cost=0.11..2485.92 rows=94366 width=123)
Index Cond: (fechamto >= '2016-02-18'::date)

​
Alcanzamos a esperar 45 minutos por el resultado de la consulta y no había
terminado.

Entonces empezamos a revisar cada tabla: cada una por separado no se demora
la consulta:
-------------
-- tabla ath_tecnicosv2

select t.descripcionmovimiento, t.fechamto, t.horamto, t.fechaact,
t.horaact, t.usuario, t.identificacionusuario, t.tipomovimientosusuario
from public.ath_tecnicosv2 t
where t.fechamto between '20160218' and now()
order by t.identificacionusuario,t.fechamto

Sort (cost=7682.15..7729.33 rows=94366 width=123) (actual
time=192.756..216.720 rows=96462 loops=1)
Sort Key: identificacionusuario, fechamto
Sort Method: external merge Disk: 11744kB
-> Index Only Scan using idx_ath_tecnicosv2_comp on ath_tecnicosv2 t
(cost=0.11..2533.11 rows=94366 width=123) (actual time=1.382..60.523
rows=96462 loops=1)
Index Cond: ((fechamto >= '2016-02-18'::date) AND (fechamto <=
now()))
Heap Fetches: 0
Total runtime: 223.940 ms

​

-----------------------
-- tabla ath_cajerosv2

select c.fecha, c.nombre, c.regionalath, c.regionaltdv,
c.tiposolicitud,c.identificacion
from public.ath_cajerosv2 c
where fecha between '20160218' and now()
order by c.identificacion,c.fecha
plan 3

Sort (cost=293.69..298.22 rows=9072 width=64) (actual time=9.959..10.501
rows=9046 loops=1)
Sort Key: identificacion, fecha
Sort Method: quicksort Memory: 1641kB
-> Index Only Scan using idx_ath_cajerosv2_comp on ath_cajerosv2 c
(cost=0.08..174.42 rows=9072 width=64) (actual time=1.174..6.215 rows=9046
loops=1)
Index Cond: ((fecha >= '2016-02-18'::date) AND (fecha <= now()))
Heap Fetches: 0
Total runtime: 10.928 ms

​

por lo tanto el problema es el JOIN , realice un JOIN con solo los ID (no
se demora mucho):

----------------------

select t.id as tecnicos_id,c.id as cajeros_id
from public.ath_tecnicosv2 t
join public.ath_cajerosv2 c on t.identificacionusuario=c.identificacion and
t.fechamto=c.fecha
where fecha >= '20160218' and fechamto >= '20160218'
group by 1,2

--- plan 4

Group (cost=27378.71..28502.23 rows=749012 width=16) (actual
time=14665.728..20729.334 rows=9736174 loops=1)
-> Sort (cost=27378.71..27753.21 rows=749012 width=16) (actual
time=14665.725..18538.695 rows=9736174 loops=1)
Sort Key: t.id, c.id
Sort Method: external merge Disk: 247448kB
-> Merge Join (cost=4.34..5263.12 rows=749012 width=16) (actual
time=2.168..2993.563 rows=9736174 loops=1)
Merge Cond: ((c.fecha = t.fechamto) AND (c.identificacion =
t.identificacionusuario))
-> Index Only Scan using idx_ath_cajerosv2_fecha2 on
ath_cajerosv2 c (cost=0.08..100.58 rows=9072 width=20) (actual
time=1.027..12.524 rows=9046 loops=1)
Index Cond: (fecha >= '2016-02-18'::date)
Heap Fetches: 0
-> Materialize (cost=0.09..1081.08 rows=94366 width=20)
(actual time=1.131..1006.430 rows=9738392 loops=1)
-> Index Only Scan using idx_ath_tecnicosv2_fecha2 on
ath_tecnicosv2 t (cost=0.09..1033.90 rows=94366 width=20) (actual
time=1.128..20.970 rows=96459 loops=1)
Index Cond: (fechamto >= '2016-02-18'::date)
Heap Fetches: 0
Total runtime: 21264.042 ms

​
-----
Por lo tanto, reescribí la consulta, primero haciendo el JOIN entre ID y
luego vinculando las tablas con estos mismos IDS pero no funciono, se sigue
demorando mucho... si hago JOIN por las tablas por separado no se demoran:

select t.descripcionmovimiento, t.fechamto, t.horamto, t.fechaact,
t.horaact, t.usuario, t.identificacionusuario, t.tipomovimientosusuario
from (
select t.id as tecnicos_id,c.id as cajeros_id
from public.ath_tecnicosv2 t
join public.ath_cajerosv2 c on t.identificacionusuario=c.identificacion and
t.fechamto=c.fecha
where fecha >= '20160218' and fechamto >= '20160218'
group by 1,2
) as x
join public.ath_tecnicosv2 t on t.id=x.tecnicos_id

plan 5

Merge Join (cost=27378.79..81818.32 rows=749012 width=123) (actual
time=18134.833..28281.834 rows=9736174 loops=1)
Merge Cond: (t_1.id = t.id)
-> Group (cost=27378.71..28502.23 rows=749012 width=16) (actual
time=17462.700..23149.037 rows=9736174 loops=1)
-> Sort (cost=27378.71..27753.21 rows=749012 width=16) (actual
time=17462.698..21212.132 rows=9736174 loops=1)
Sort Key: t_1.id, c.id
Sort Method: external merge Disk: 247448kB
-> Merge Join (cost=4.34..5263.12 rows=749012 width=16)
(actual time=0.097..3588.954 rows=9736174 loops=1)
Merge Cond: ((c.fecha = t_1.fechamto) AND
(c.identificacion = t_1.identificacionusuario))
-> Index Only Scan using idx_ath_cajerosv2_fecha2 on
ath_cajerosv2 c (cost=0.08..100.58 rows=9072 width=20) (actual
time=0.057..14.490 rows=9046 loops=1)
Index Cond: (fecha >= '2016-02-18'::date)
Heap Fetches: 0
-> Materialize (cost=0.09..1081.08 rows=94366
width=20) (actual time=0.038..1253.519 rows=9738392 loops=1)
-> Index Only Scan using
idx_ath_tecnicosv2_fecha2 on ath_tecnicosv2 t_1 (cost=0.09..1033.90
rows=94366 width=20) (actual time=0.035..25.707 rows=96459 loops=1)
Index Cond: (fechamto >= '2016-02-18'::date)
Heap Fetches: 0
-> Index Scan using idx_u_ath_tecnicosv2_id on ath_tecnicosv2 t
(cost=0.09..46129.90 rows=1639202 width=131) (actual time=1.746..1950.852
rows=11281027 loops=1)
Total runtime: 28768.255 ms

​

-----------------

select
c.fecha, c.nombre, c.regionalath, c.regionaltdv, c.tiposolicitud
from (
select t.id as tecnicos_id,c.id as cajeros_id
from public.ath_tecnicosv2 t
join public.ath_cajerosv2 c on t.identificacionusuario=c.identificacion and
t.fechamto=c.fecha
where fecha >= '20160218' and fechamto >= '20160218'
group by 1,2
) as x
join public.ath_cajerosv2 c on c.id=x.cajeros_id

plan 6

Hash Join (cost=32278.13..47788.27 rows=749012 width=56) (actual
time=17682.541..29054.853 rows=9736174 loops=1)
Hash Cond: (x.cajeros_id = c.id)
-> Subquery Scan on x (cost=27378.71..31498.27 rows=749012 width=8)
(actual time=17588.940..24201.101 rows=9736174 loops=1)
-> Group (cost=27378.71..28502.23 rows=749012 width=16) (actual
time=17588.939..23095.463 rows=9736174 loops=1)
-> Sort (cost=27378.71..27753.21 rows=749012 width=16)
(actual time=17588.936..21161.819 rows=9736174 loops=1)
Sort Key: t.id, c_1.id
Sort Method: external merge Disk: 247448kB
-> Merge Join (cost=4.34..5263.12 rows=749012
width=16) (actual time=0.076..3827.427 rows=9736174 loops=1)
Merge Cond: ((c_1.fecha = t.fechamto) AND
(c_1.identificacion = t.identificacionusuario))
-> Index Only Scan using
idx_ath_cajerosv2_fecha2 on ath_cajerosv2 c_1 (cost=0.08..100.58 rows=9072
width=20) (actual time=0.039..16.084 rows=9046 loops=1)
Index Cond: (fecha >= '2016-02-18'::date)
Heap Fetches: 0
-> Materialize (cost=0.09..1081.08 rows=94366
width=20) (actual time=0.031..1317.914 rows=9738392 loops=1)
-> Index Only Scan using
idx_ath_tecnicosv2_fecha2 on ath_tecnicosv2 t (cost=0.09..1033.90
rows=94366 width=20) (actual time=0.029..23.961 rows=96459 loops=1)
Index Cond: (fechamto >=
'2016-02-18'::date)
Heap Fetches: 0
-> Hash (cost=2886.20..2886.20 rows=132050 width=64) (actual
time=92.976..92.976 rows=132050 loops=1)
Buckets: 8192 Batches: 4 Memory Usage: 3281kB
-> Seq Scan on ath_cajerosv2 c (cost=0.00..2886.20 rows=132050
width=64) (actual time=0.451..43.008 rows=132050 loops=1)
Total runtime: 29573.302 m

​

Entonces no se pueda ser!!!

incluso (a manera de comentario) replantee la consultas asi y tampoco:

with base1 as (
select t.descripcionmovimiento, t.fechamto, t.horamto, t.fechaact,
t.horaact, t.usuario, t.identificacionusuario, t.tipomovimientosusuario
from (
select t.identificacionusuario,t.fechamto from public.ath_tecnicosv2 t
where fechamto >= '20160218'
intersect
select c.identificacion,c.fecha from public.ath_cajerosv2 c where fecha >=
'20160218'
) as x
join public.ath_tecnicosv2 t on
(t.identificacionusuario,t.fechamto)=(x.identificacionusuario,x.fechamto)
), base2 as (
select x.identificacionusuario,x.fechamto,c.fecha, c.nombre, c.regionalath,
c.regionaltdv, c.tiposolicitud
from (
select t.identificacionusuario,t.fechamto from public.ath_tecnicosv2 t
where fechamto >= '20160218'
intersect
select c.identificacion,c.fecha from public.ath_cajerosv2 c where fecha >=
'20160218'
) as x
join public.ath_cajerosv2 c on
(c.identificacion,c.fecha)=(x.identificacionusuario,x.fechamto)
)
select u.descripcionmovimiento, u.fechamto, u.horamto, u.fechaact,
u.horaact, u.usuario, u.identificacionusuario, u.tipomovimientosusuario,
u.fecha, u.nombre, u.regionalath, u.regionaltdv, u.tiposolicitud, 1 as cant
from (
select
x.descripcionmovimiento, x.fechamto, x.horamto, x.fechaact, x.horaact,
x.usuario, x.identificacionusuario, x.tipomovimientosusuario, y.fecha,
y.nombre, y.regionalath, y.regionaltdv,
y.tiposolicitud,y.identificacionusuario as
identificacionusuario2,y.fechamto as fechamto2
from base1 as x
cross join base2 as y
) as u where u.identificacionusuario=u.identificacionusuario2 and
u.fechamto=u.fechamto2
order by 2,3,7

---

Sort (cost=8260.00..8260.48 rows=961 width=1010)
Sort Key: x.fechamto, x.horamto, x.identificacionusuario
CTE base1
-> Nested Loop (cost=0.19..3929.54 rows=21842 width=123)
-> Subquery Scan on x_1 (cost=0.08..1528.40 rows=533 width=12)
-> HashSetOp Intersect (cost=0.08..1526.27 rows=533
width=12)
-> Append (cost=0.08..1422.83 rows=103438 width=12)
-> Subquery Scan on "*SELECT* 2"
(cost=0.08..125.87 rows=9072 width=12)
-> Index Only Scan using
idx_ath_cajerosv2_fecha on ath_cajerosv2 c (cost=0.08..89.58 rows=9072
width=12)
Index Cond: (fecha >=
'2016-02-18'::date)
-> Subquery Scan on "*SELECT* 1"
(cost=0.09..1296.96 rows=94366 width=12)
-> Index Only Scan using
idx_ath_tecnicosv2_fecha on ath_tecnicosv2 t_1 (cost=0.09..919.50
rows=94366 width=12)
Index Cond: (fechamto >=
'2016-02-18'::date)
-> Index Only Scan using idx_ath_tecnicosv2_comp on
ath_tecnicosv2 t (cost=0.11..4.08 rows=107 width=123)
Index Cond: ((fechamto = x_1.fechamto) AND
(identificacionusuario = x_1.identificacionusuario))
CTE base2
-> Nested Loop (cost=0.17..2175.54 rows=1760 width=68)
-> Subquery Scan on x_2 (cost=0.08..1528.40 rows=533 width=12)
-> HashSetOp Intersect (cost=0.08..1526.27 rows=533
width=12)
-> Append (cost=0.08..1422.83 rows=103438 width=12)
-> Subquery Scan on "*SELECT* 2_1"
(cost=0.08..125.87 rows=9072 width=12)
-> Index Only Scan using
idx_ath_cajerosv2_fecha on ath_cajerosv2 c_2 (cost=0.08..89.58 rows=9072
width=12)
Index Cond: (fecha >=
'2016-02-18'::date)
-> Subquery Scan on "*SELECT* 1_1"
(cost=0.09..1296.96 rows=94366 width=12)
-> Index Only Scan using
idx_ath_tecnicosv2_fecha on ath_tecnicosv2 t_2 (cost=0.09..919.50
rows=94366 width=12)
Index Cond: (fechamto >=
'2016-02-18'::date)
-> Index Only Scan using idx_ath_cajerosv2_comp on ath_cajerosv2
c_1 (cost=0.08..1.15 rows=17 width=64)
Index Cond: ((fecha = x_2.fechamto) AND (identificacion =
x_2.identificacionusuario))
-> Hash Join (cost=22.88..2145.40 rows=961 width=1010)
Hash Cond: ((x.identificacionusuario = y.identificacionusuario) AND
(x.fechamto = y.fechamto))
-> CTE Scan on base1 x (cost=0.00..174.74 rows=21842 width=638)
-> Hash (cost=14.08..14.08 rows=1760 width=384)
-> CTE Scan on base2 y (cost=0.00..14.08 rows=1760
width=384)

​
He cambiado la estructura de las tablas: antes todos los campos eran del
tipo TEXT y por lo tanto la tablas tenían ademas tablas TOAST y supuse que
el JOIN con estas era lo que estaba penalizando.

Las especificaciones, es un PostgreSQL 9.4.5, corriendo en un CentOS 6 con
discos de estado solido. 12 GB de RAM, 12

Lista les agradezco sus comentratios el ideas de que puede ser el probelma.

--
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
Oracle Database 10g Administrator Certified Associate
EnterpriseDB Certified PostgreSQL 9.3 Associate