Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 56969475944 for ; Thu, 9 May 2002 07:14:02 -0400 (EDT) Received: from tsmtp8.mail.isp (mailhost2.teleline.es [195.235.113.141]) by postgresql.org (Postfix) with ESMTP id E5E0147593C for ; Thu, 9 May 2002 07:13:59 -0400 (EDT) Received: from teleline.es ([213.98.70.162]) by tsmtp8.mail.isp (Netscape Messaging Server 4.15 tsmtp8 Mar 14 2002 21:29:48) with ESMTP id GVUCHO00.3RA for ; Thu, 9 May 2002 13:13:00 +0200 Message-ID: <3CDA4BAB.F67D216E@teleline.es> Date: Thu, 09 May 2002 12:12:59 +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: Lista SQL de postgres Subject: Can this query be faster? Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200205/73 X-Sequence-Number: 7709 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 = prendas.codarticulo And prendas.codprenda = stocks.codprenda Group by articulos.codarticulo, articulos.descripcion Limit 10; +----------------------- Table 'articulos' has 4049 records Table 'prendas' has 29493 records Table 'stocks' has 90747 records cad=# \d articulos Table "articulos" Attribute | Type | Modifier --------------+---------------+----------------------------- codarticulo | char(20) | not null default '0' descripcion | varchar(40) | default ' ' refproveedor | char(20) | default ' ' codproveedor | char(5) | not null default ' ' venautoriz | boolean | not null default 't'::bool comautoriz | boolean | not null default 't'::bool codtallaje | char(2) | not null default ' ' codtarifa | char(2) | not null default ' ' apldtocli | boolean | not null default 'f'::bool precompra | numeric(30,6) | not null default 0.1 pretarifa | numeric(30,6) | not null default 0 premedio | numeric(30,6) | not null default 0 precosto | numeric(30,6) | not null default 0 pvpsegun | varchar(15) | not null default 'Indicado' impuesto | numeric(30,6) | not null default 0 codfamilia | char(5) | not null default ' ' ubicacion | char(10) | not null default ' ' observ | varchar(2048) | not null default ' ' preultcompra | numeric(30,6) | not null default 0 Indices: articulos_pkey, idxarticulos_codarticulo, idxarticulos_codfamilia, idxarticulos_codproveedor, idxarticulos_descripcion, idxarticulos_refproveedor cad=# \d prendas Table "prendas" Attribute | Type | Modifier -------------+----------+--------------------------------------------- codarticulo | char(20) | not null default '0' codcolor | char(3) | not null default '000' numtalla | char(2) | not null default '00' codprenda | integer | not null default nextval('codprenda'::text) Indices: idxprendas_codarticulo, idxprendas_codcolor, idxprendas_codprenda, idxprendas_numtalla, prendas_pkey cad=# \d stocks Table "stocks" Attribute | Type | Modifier ------------+----------+------------------------- stkreal | integer | not null default 0 stkpterec | integer | not null default 0 stkpteser | integer | not null default 0 stkminimo | integer | not null default 999999 codprenda | integer | not null default 0 codalmacen | char(10) | not null default ' ' Indices: idxstocks_codalmacen, idxstocks_codprenda, stocks_pkey cad=# Explain Select articulos.codarticulo, articulos.descripcion, sum(stocks.stkreal) as tot From articulos, prendas, stocks Where articulos.codarticulo = prendas.codarticulo and prendas.codprenda = stocks.codprenda Group by articulos.codarticulo, articulos.descripcion Limit 10; NOTICE: QUERY PLAN: Aggregate (cost=485333365.76..493587395.74 rows=110053733 width=48) -> Group (cost=485333365.76..490836052.41 rows=1100537331 width=48) -> Sort (cost=485333365.76..485333365.76 rows=1100537331 width=48) -> Merge Join (cost=310247.47..334885.36 rows=1100537331 width=48) -> Index Scan using stocks_pkey on stocks (cost=0.00..5233.94 rows=74469 width=8) -> Sort (cost=310247.47..310247.47 rows=1477846 width=40) -> Merge Join (cost=648.57..2429.70 rows=1477846 width=40) -> Index Scan using prendas_pkey on prendas (cost=0.00..1403.44 rows=24077 width=16) -> Sort (cost=648.57..648.57 rows=6138 width=24) -> Seq Scan on articulos (cost=0.00..262.38 rows=6138 width=24) EXPLAIN cad=#