Fernando Schapachnik <fernando@mecon.gov.ar> writes:
> A rewritten query still exhibits the same behavior:
> EXPLAIN ANALYZE SELECT DISTINCT p.id
> FROM partes_tecnicos p
> WHERE
> p.id IN
> (SELECT r.id_parte_tecnico FROM
> rel_usr_sector_parte_tecnico r, active_users u
> WHERE (r.id_usr=u.id AND u.login='xxx' AND
> r.id_sector=p.id_sector_actual AND
> p.id_cola_por_ambito=1)
> OR p.id_cola_por_ambito=1)
> AND p.id_situacion!=6;
[ shrug... ] This is still telling the system to perform a
Cartesian-product join when p.id_cola_por_ambito=1.
A sane formulation of the query might look like
EXPLAIN ANALYZE SELECT DISTINCT p.id
FROM partes_tecnicos p
WHERE
(p.id_cola_por_ambito=1 OR
p.id IN
(SELECT r.id_parte_tecnico FROM
rel_usr_sector_parte_tecnico r, active_users u
WHERE (r.id_usr=u.id AND u.login='xxx' AND
r.id_sector=p.id_sector_actual)))
AND p.id_situacion!=6;
ie, get the constant term out of the sub-select. This is not exactly
the same thing though --- in particular, what do you intend should
happen if p.id has no matches whatsoever in r.id_parte_tecnico,
yet p.id_cola_por_ambito=1?
regards, tom lane