Обсуждение: Can this query be faster?

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)       |
notnull default ' 'venautoriz   | boolean       | not null default 't'::boolcomautoriz   | boolean       | not null
default't'::boolcodtallaje   | char(2)       | not null default ' 'codtarifa    | char(2)       | not null default '
'apldtocli   | boolean       | not null default 'f'::boolprecompra    | numeric(30,6) | not null default 0.1pretarifa
| numeric(30,6) | not null default 0premedio     | numeric(30,6) | not null default 0precosto     | numeric(30,6) | not
nulldefault 0pvpsegun     | varchar(15)   | not null default 'Indicado'impuesto     | numeric(30,6) | not null default
0codfamilia  | 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  |
notnull 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 0stkpterec  | integer  | not
nulldefault 0stkpteser  | integer  | not null default 0stkminimo  | integer  | not null default 999999codprenda  |
integer | not null default 0codalmacen | 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=1100537331width=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=#

Hi,

Miguel Carvalho escribió:
>
> > 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;
> > +-----------------------
> Two things that came to mind:
>
>  - 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
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 an unique index
asociatedto 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
single 'articulo'.


>
>  Please post the postgresql.conf file ( usaly on /usr/local/pgsql/data/ )
>

In my SuSE Linux 7.2 there is a 'postmaster.opts'. I think
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'
~
~
~