Re: Optimizer problem in 8.1.6

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