pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCan 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>
2002-05-09 18:34 ` Re: Can this query be faster? Gabriel Dovalo Carril <dovalo@terra.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
* Re: Can this query be faster?
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>
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
* 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
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