Can this query be faster?
От | Gabriel Dovalo Carril |
---|---|
Тема | Can this query be faster? |
Дата | |
Msg-id | 3CDA5188.1DD844B5@terra.es обсуждение исходный текст |
Список | 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) | 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 Fromarticulos, 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=1100537331width=48) -> Merge Join (cost=310247.47..334885.36 rows=1100537331 width=48) -> Index Scan usingstocks_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.70rows=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 Scanon articulos (cost=0.00..262.38 rows=6138 width=24) EXPLAIN cad=#
В списке pgsql-sql по дате отправления: