Re: Optimizer problem in 8.1.6

En un mensaje anterior, Tom Lane escribió:
> Fernando Schapachnik <fernando(at)mecon(dot)gov(dot)ar> writes:
> > Now, combined (sorry for the convoluted query, it is build
> > automatically by an app).
>
> > EXPLAIN SELECT DISTINCT p.id
> > FROM partes_tecnicos p,
> > rel_usr_sector_parte_tecnico r, active_users u
> > WHERE ((r.id_parte_tecnico=p.id AND 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;
>
> Is this query really what you want to do? Because the OR overrides all
> the join conditions, meaning that rows having p.id_cola_por_ambito=1
> AND p.id_situacion!=6 must produce Cartesian products against every
> row in each of the other tables.

A rewritten query still exhibits the same behavior:

VACUUM verbose ANALYZE users;
[...]
INFO: analyzing "users"
INFO: "users": scanned 778 of 778 pages, containing 22320 live
rows and 3 dead rows; 3000 rows in sample, 22320 estimated total rows

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;

Unique (cost=0.00..19045387.60 rows=177 width=4) (actual
time=0.331..997.593 rows=209 loops=1)
-> Index Scan using partes_tecnicos_pkey on partes_tecnicos p
(cost=0.00..19045387.16 rows=177 width=4) (actual time=0.323..995.797
rows=209 loops=1)
Filter: ((id_situacion <> 6) AND (subplan))
SubPlan
-> Result (cost=8.07..90878.33 rows=4493367 width=4)
(actual time=0.028..3.250 rows=178 loops=254)
One-Time Filter: ($0 = 1)
-> Nested Loop (cost=8.07..90878.33 rows=4493367
width=4) (actual time=0.025..2.393 rows=216 loops=209)
-> Seq Scan on users u (cost=0.00..1002.92
rows=9747 width=0) (actual time=0.009..0.009 rows=1 loops=209)
Filter: (active AND ((field1 IS
NULL) OR (NOT field1)))
-> Materialize (cost=8.07..12.68 rows=461
width=4) (actual time=0.004..0.800 rows=216 loops=209)
-> Seq Scan on
rel_usr_sector_parte_tecnico r (cost=0.00..7.61 rows=461 width=4)
(actual time=0.008..2.128 rows=488 loops=1)
Total runtime: 998.552 ms
(12 rows)

Notice again the seq scan on users instead of using the index and the
very off estimate.

Thanks.

Fernando.