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.