Re: Can this query be faster?

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 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
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'
~
~
~