Thread

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

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-10-22T16:20:26Z

   Fernando Schapachnik <fpscha@ns1.via-net-works.net.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
   
   

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

   Fernando Schapachnik <fpscha@ns1.via-net-works.net.ar> — 1999-10-23T18:25:25Z

   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