Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 32A5A475BC9 for ; Thu, 9 May 2002 15:39:02 -0400 (EDT) Received: from tsmtp3.ldap.isp (mailhost2.teleline.es [195.235.113.141]) by postgresql.org (Postfix) with ESMTP id 7BDD1475CA9 for ; Thu, 9 May 2002 15:36:09 -0400 (EDT) Received: from terra.es ([212.73.58.137]) by tsmtp3.ldap.isp (Netscape Messaging Server 4.15 tsmtp3 Mar 14 2002 21:29:48) with ESMTP id GVUZQK00.HFU; Thu, 9 May 2002 21:35:08 +0200 Message-ID: <3CDAC151.D1D2F6E2@terra.es> Date: Thu, 09 May 2002 20:34:57 +0200 From: Gabriel Dovalo Carril X-Mailer: Mozilla 4.76 [es] (X11; U; Linux 2.4.0-4GB i686) X-Accept-Language: en MIME-Version: 1.0 To: miguel@ipatimup.pt Cc: Lista SQL de postgres Subject: Re: Can this query be faster? References: <3CDA4BAB.F67D216E@teleline.es> <4253.193.136.62.21.1020965901.squirrel@www.ipatimup.up.pt> Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Archive-Number: 200205/81 X-Sequence-Number: 7717 Hi, Miguel Carvalho escribi=F3: >=20 > > Hi all, > > > > This query spends 24 seconds to obtain the > > result. What can be optimized? > > Every time I run this query I only need information > > related to 10 'articulos' and then I use 'OFFSET' clause > > to search forward or backward. > > > > +----------------------- > > Select articulos.codarticulo, articulos.descripcion, > > sum(stocks.stkreal) as tot > >>From articulos, prendas, stocks > > Where articulos.codarticulo =3D prendas.codarticulo > > And prendas.codprenda =3D stocks.codprenda > > Group by articulos.codarticulo, articulos.descripcion > > Limit 10; > > +----------------------- > Two things that came to mind: >=20 > - Have you run a vaccumm on the tables? Yes, I have. > - Why do you use a char(20) as the key instead of an integer? Because Alphanumeric codes and codes with 0's=20 in left size must be allowed > What are the keys for each table? Table articulos: (codarticulo) Table prendas: (codarticulo, codcolor, numtalla) field (codprenda) is an alternative key. There is=20 an unique index asociated to it. Table stocks: (codprenda, codalmacen) For example, we can have a t-shirt (articulo). Every diferent size and color of this t-shirt has a code associated (codprenda). Stock of t-shirt is distributed among diferent warehouses (almacen). What I need is to totalize stocks for every=20 single 'articulo'. >=20 > Please post the postgresql.conf file ( usaly on /usr/local/pgsql/data/ ) >=20 In my SuSE Linux 7.2 there is a 'postmaster.opts'. I think=20 this is the file you are asking for. I have no 'postgresql.conf' /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data -A 1 -B 2048 -b /usr/bin/postgres -i -N 1024 -S -o '-e' ~ ~ ~