Re: [HACKERS] Neverending query on 6.5.2 over Solaris 2.5.1

En un mensaje anterior, Tom Lane escribió:
> > Explain shows (on both machines):
> 
> > Nested Loop  (cost=503.74 rows=1 width=74)
> >   ->  Nested Loop  (cost=500.89 rows=1 width=58)
> >         ->  Seq Scan on usuarios u  (cost=498.84 rows=1 width=30)
> >         ->  Index Scan using passwd_id_usr_key on passwd pas  
> > (cost=2.05 rows=10571 width=28)
> >   ->  Seq Scan on perfiles per  (cost=2.85 rows=56 width=16)
> 
> OK, that still looks a little bogus.  It's estimating it will only
> find one row in usarios that needs to be joined against the other
> two tables.  If that were true, then this plan is pretty reasonable,
> but I bet it's not true.  The only WHERE clause that can be used to
> eliminate usarios rows in advance of the join is (u.activa), and I'll
> bet you have more than one active user.

That's right!

> 
> Does the plan change if you do VACUUM ANALYZE instead of just a plain
> vacuum?

Sorry for not being clear enough, but that was what I did.

> 
> As an experiment you could try forbidding nestloop plans (start psql
> with environment variable PGOPTIONS="-fn") and see what sort of plan
> you get then and how long it really takes in comparison to the nestloop.

I took 30 seconds on the Sun, and explain shows:

NOTICE:  QUERY PLAN:

Merge Join  (cost=1314.02 rows=1 width=74) ->  Seq Scan  (cost=1297.56 rows=1 width=58)       ->  Sort  (cost=1297.56
rows=1width=58)             ->  Hash Join  (cost=1296.56 rows=1 width=58)                   ->  Seq Scan on passwd pas
(cost=447.84
 
rows=10571 width=28)                   ->  Hash  (cost=498.84 rows=1 width=30)                         ->  Seq Scan on
usuariosu  (cost=498.84 
 
rows=1 width=30) ->  Seq Scan  (cost=14.58 rows=56 width=16)       ->  Sort  (cost=14.58 rows=56 width=16)
-> Seq Scan on perfiles per  (cost=2.85 rows=56 width=16)
 

EXPLAIN

> > I'm running postmaster -N 8 -B 16 because whitout these postmaster 
> > wouldn't get all the shared memory it needed and won't start. Do you 
> > think that this may be in some way related?
> 
> Well, that's certainly costing you performance; 16 disk pages is not
> enough buffer space to avoid thrashing.  You need to increase your
> kernel's max-shared-memory-block-size (SHMMAX, I think) parameter
> so that you can run with a more reasonable -B setting.  A lot of
> kernels ship with SHMMAX settings that are ridiculously small for
> any modern machine.

Ok, I'll try to increase it.

Regards.



Fernando P. Schapachnik
Administración de la red
VIA Net Works Argentina SA
Diagonal Roque Sáenz Peña 971, 4º y 5º piso.
1035 - Capital Federal, Argentina. 
(54-11) 4323-3333
http://www.via-net-works.net.ar