Re: Optimizer problem in 8.1.6

Fernando Schapachnik <fernando(at)mecon(dot)gov(dot)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