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=1 width=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 usuarios u (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