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
