pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gabriel Dovalo Carril <dovalo@teleline.es>
To: Lista SQL de postgres <pgsql-sql@postgresql.org>
Subject: Can this query be faster?
Date: Thu, 09 May 2002 12:12:59 +0200
Message-ID: <3CDA4BAB.F67D216E@teleline.es> (raw)

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



view thread (3+ messages)  latest in thread

Message-ID: <3CDA4BAB.F67D216E@teleline.es>
Permalink:  ../3CDA4BAB.F67D216E@teleline.es/
Also on:    postgresql.org/message-id/3CDA4BAB.F67D216E@teleline.es

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: dovalo@teleline.es
  Subject: Re: Can this query be faster?
  In-Reply-To: <3CDA4BAB.F67D216E@teleline.es>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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