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

Fernando Schapachnik <fpscha(at)ns1(dot)via-net-works(dot)net(dot)ar> writes:
>> 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:

Better, but still not good.

> 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)

It's still convinced it's only going to get one row out of usuarios.
Weird. I assume that your 'activa' field is 'bool'? I've been trying
to duplicate this misbehavior here, and as near as I can tell the system
handles selectivity estimates for boolean fields just fine. Whatever
percentage of 't' values was seen by the last VACUUM ANALYZE is exactly
what it uses.

I am using 6.5.2 and current sources, though, and in your original
message you said you were on 6.5.0. If that's right, seems like the
first thing to try is for you to update to 6.5.2, run another VACUUM
ANALYZE, and then see if you still get the same bogus row estimates.

The other odd thing about the above plan is that it's doing an
explicit sort on perfiles. Didn't you say that you had an index on
perfiles.id_perfil? It should be scanning that instead of doing
a sort, I'd think. (However, if there really are only 56 rows in
perfiles, it probably doesn't matter.)

regards, tom lane