pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Can this query be faster?
3+ messages / 2 participants
[nested] [flat]

* Can this query be faster?
@ 2002-05-09 10:12  Gabriel Dovalo Carril <dovalo@teleline.es>
  0 siblings, 1 reply; 3+ messages in thread

From: Gabriel Dovalo Carril @ 2002-05-09 10:12 UTC (permalink / raw)
  To: pgsql-sql

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=#



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Can this query be faster?
@ 2002-05-09 10:38  Gabriel Dovalo Carril <dovalo@terra.es>
  0 siblings, 0 replies; 3+ messages in thread

From: Gabriel Dovalo Carril @ 2002-05-09 10:38 UTC (permalink / raw)
  To: pgsql-sql


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=#



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Can this query be faster?
@ 2002-05-09 18:34  Gabriel Dovalo Carril <dovalo@terra.es>
  parent: Gabriel Dovalo Carril <dovalo@teleline.es>
  0 siblings, 0 replies; 3+ messages in thread

From: Gabriel Dovalo Carril @ 2002-05-09 18:34 UTC (permalink / raw)
  To: miguel@ipatimup.pt; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-05-09 18:34 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-09 10:12 Can this query be faster? Gabriel Dovalo Carril <dovalo@teleline.es>
2002-05-09 18:34 ` Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-09 10:38 Can this query be faster? Gabriel Dovalo Carril <dovalo@terra.es>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox