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

En un mensaje anterior, Tom Lane escribió:
> 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.

I was using 6.5.0 on my first post, then I upgraded and all the vacuum
and explain commands where from 6.5.2. Here is my complete database
definition:

CREATE TABLE usuarios
(id_usr serial,
razon_social text NOT NULL,
nombre_cuenta text NOT NULL,
grupo int2 NOT NULL,
perfil int2 NOT NULL,
estado char(1) NOT NULL DEFAULT 'H' CHECK ((estado='H') or (estado='D')),
id_madre int4 NOT NULL,
fecha_creacion datetime DEFAULT CURRENT_DATE,
fecha_baja datetime,
gratuita bool DEFAULT 'f',
activa bool DEFAULT 't',
observaciones text
) \g

CREATE TABLE passwd
(id_usr serial,
clave_plana text NOT NULL,
clave_cifrada text NOT NULL
) \g

CREATE TABLE perfiles
(id_perfil serial,
nombre text NOT NULL,
descripcion text
) \g

CREATE TABLE grupos
(id_grupo serial,
nombre text NOT NULL,
descripcion text
) \g

CREATE TABLE cronometradas
(id_usr serial,
fecha_comienzo_cronometrado datetime DEFAULT CURRENT_DATE,
tipo_cronometrado int2,
max_segs_vida int4,
max_segs_consumo int4
) \g

CREATE TABLE tipos_cronometrado
(id_tipo_cronometrado serial,
nombre text NOT NULL,
descripcion text
) \g

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

It should, as it is serial. What does it mean when PgAccess says a table
doesn't has a primary key? Would it impact?

Again, thanks!

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