Thread

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

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-10-21T19:31:50Z

   Fernando Schapachnik <fpscha@ns1.via-net-works.net.ar> writes:
   > 	I have 6.5.0 running over Solaris 2.5.1 SPARC. I have a 
   > database with 5 tables, 3 of them < 100 regs. and 2 ("usuarios" and 
   > "passwd") with >10000. When querying for:
   
   > SELECT u.nombre_cuenta, per.nombre, pas.clave_cifrada, 
   > pas.clave_plana, u.estado FROM usuarios u, perfiles per, passwd pas 
   > WHERE (u.perfil=per.id_perfil) and (u.id_usr=pas.id_usr) and 
   > (u.activa) \g 
   
   > 	postmaster starts eating a lot of CPU and it doesn't finish to 
   > process the query in +20 minutes.
   
   Have you vacuumed the database lately?  What does "explain ..." show
   for the query plan being used?
   
   You might be well advised to create indexes on usarios.id_usr and
   passwd.id_usr, if you don't have them already.  I'd expect this
   query to run reasonably quickly using a mergejoin, but mergejoin
   needs indexes on the fields being joined.  (The system will also
   consider doing an explicit sort and then a mergejoin, but obviously
   the sort step takes extra time.)
   
   If you haven't vacuumed since filling the tables then the optimizer
   may believe that the tables only contain a few rows, in which case
   it's likely to use a plain nested-loop join (ie, compare every usarios
   row to every passwd row to find matching id_usr fields).  That's nice
   and fast for little tables, but a big loser on big ones...
   
   			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-22T12:38:35Z

   En un mensaje anterior, Tom Lane escribió:
   > Fernando Schapachnik <fpscha@ns1.via-net-works.net.ar> writes:
   > > 	I have 6.5.0 running over Solaris 2.5.1 SPARC. I have a 
   > > database with 5 tables, 3 of them < 100 regs. and 2 ("usuarios" and 
   > > "passwd") with >10000. When querying for:
   > 
   > > SELECT u.nombre_cuenta, per.nombre, pas.clave_cifrada, 
   > > pas.clave_plana, u.estado FROM usuarios u, perfiles per, passwd pas 
   > > WHERE (u.perfil=per.id_perfil) and (u.id_usr=pas.id_usr) and 
   > > (u.activa) \g 
   > 
   > > 	postmaster starts eating a lot of CPU and it doesn't finish to 
   > > process the query in +20 minutes.
   > 
   > Have you vacuumed the database lately?  What does "explain ..." show
   
   I did this today. I also installed Postgres on a FreeBSD machine 
   (comparable -and low- load averages) and updated the version to 6.5.2.
   
   After vacuum:
   On the Sun: 1 minute.
   On the FreeBSD: 12 seconds.
   
   Explain shows (on both machines):
   
   operaciones=> explain SELECT u.nombre_cuenta, per.nombre, 
   pas.clave_cifrada, pas.clave_plana, u.estado FROM usuarios u, perfiles per,
   passwd pas WHERE (u.activa) and (u.perfil=per.id_perfil) and 
   (u.id_usr=pas.id_usr) \g
   NOTICE:  QUERY PLAN:
   
   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)
   
   EXPLAIN 
   > You might be well advised to create indexes on usarios.id_usr and
   > passwd.id_usr, if you don't have them already.  I'd expect this
   
   As usuarios.id_usr and passwd.id_usr are both serial, they have 
   indexes automatically created (I double checked that). PgAccess shows 
   that usuarios has no primary key (I don't know why) and that 
   usuarios_id_usr_key is an unique, no clustered index. Same on passwd.
   
   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?
   
   Thanks for your help!
   
   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