Re: Optimizer problem in 8.1.6

En un mensaje anterior, Michael Glaesemann escribió:
>
> On Jun 22, 2007, at 10:16 , Fernando Schapachnik wrote:
>
> >EXPLAIN SELECT DISTINCT p.id
>
> Can you provide EXPLAIN ANALYZE? I suspect that when you rewrote the
> query it changed how the planner took into account the statistics. If
> your statistics are off, perhaps this changes how the planner
> rewrites the query.

Sure. The DB is VACUUM'ed daily, and the users database only received
a few updates per day.

This is from the rewrote one:

---------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=18.65..2838.38 rows=268 width=4) (actual
time=0.265..1503.554 rows=209 loops=1)
-> Nested Loop (cost=18.65..2529.51 rows=123548 width=4) (actual
time=0.257..1127.666 rows=101992 loops=1)
-> Index Scan using partes_tecnicos_pkey on partes_tecnicos
p (cost=0.00..39.89 rows=268 width=4) (actual time=0.025..2.115
rows=209 loops=1)
Filter: ((id_cola_por_ambito = 1) AND (id_situacion <>
6))
-> Materialize (cost=18.65..23.26 rows=461 width=0) (actual
time=0.005..1.817 rows=488 loops=209)
-> Nested Loop (cost=0.00..18.19 rows=461 width=0)
(actual time=0.209..5.670 rows=488 loops=1)
-> Index Scan using active_users on users u
(cost=0.00..5.97 rows=1 width=0) (actual time=0.141..0.147 rows=1
loops=1)
Index Cond: ("login" =
'xxx'::text)
Filter: (active AND ((field1 IS NULL)
OR (NOT field1)))
-> Seq Scan on rel_usr_sector_parte_tecnico r
(cost=0.00..7.61 rows=461 width=0) (actual time=0.053..1.995 rows=488
loops=1)
Total runtime: 1504.500 ms
(11 rows)

The original one is taking a *lot* of time (more than an hour by now).

Thanks!

Fernando.