Re: Optimizer problem in 8.1.6 - Mailing list pgsql-general

En un mensaje anterior, Tom Lane escribió:
> Fernando Schapachnik <fernando@mecon.gov.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.