Received: from bonito.atc.unican.es (bonito.atc.unican.es [193.144.185.218])
	by postgresql.org (Postfix) with ESMTP id 59061475DBA
	for <pgsql-hackers@postgresql.org>;
	Thu, 11 Jul 2002 11:18:20 -0400 (EDT)
Received: from ALIGOTE (aligote [193.144.185.202])
	by bonito.atc.unican.es (8.11.0/8.8.7) with SMTP id g6BFIQb08690
	for <pgsql-hackers@postgresql.org>; Thu, 11 Jul 2002 17:18:26 +0200
Message-ID: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
From: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
To: <pgsql-hackers@postgresql.org>
Subject: please help on query
Date: Thu, 11 Jul 2002 17:22:14 +0200
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_005B_01C228FF.7FFB6AB0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Archive-Number: 200207/460
X-Sequence-Number: 24834

This is a multi-part message in MIME format.

------=_NextPart_000_005B_01C228FF.7FFB6AB0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I can't improve performance on this query:

SELECT
 supplier.name,
 supplier.address
FROM
 supplier,
 nation
WHERE
 supplier.suppkey IN(
  SELECT
   partsupp.suppkey
  FROM
   partsupp
  WHERE
   partsupp.partkey IN(
    SELECT
     part.partkey
    FROM
     part
    WHERE
     part.name like 'forest%'
     )
   AND partsupp.availqty>(
    SELECT
     0.5*(sum(lineitem.quantity)::FLOAT)
    FROM
     lineitem
    WHERE
     lineitem.partkey=3Dpartsupp.partkey
     AND lineitem.suppkey=3Dpartsupp.partkey
     AND lineitem.shipdate>=3D('1994-01-01')::DATE
     AND lineitem.shipdate<(('1994-01-01')::DATE+('1 year')::INTERVAL)::DATE
     )
  )
 AND supplier.nationkey=3Dnation.nationkey
 AND nation.name=3D'CANADA'
ORDER BY
 supplier.name;


explain results:
NOTICE:  QUERY PLAN:

Sort  (cost=3D2777810917708.17..2777810917708.17 rows=3D200 width=3D81)
  ->  Nested Loop  (cost=3D0.00..2777810917700.53 rows=3D200 width=3D81)
        ->  Seq Scan on nation  (cost=3D0.00..1.31 rows=3D1 width=3D4)
        ->  Index Scan using snation_index on supplier  (cost=3D0.00..27778=
10917696.72 rows=3D200 width=3D77)
              SubPlan
                ->  Materialize  (cost=3D6944527291.72..6944527291.72 rows=
=3D133333 width=3D4)
                      ->  Seq Scan on partsupp  (cost=3D0.00..6944527291.72=
 rows=3D133333 width=3D4)
                            SubPlan
                              ->  Materialize  (cost=3D8561.00..8561.00 row=
s=3D1 width=3D4)
                                    ->  Seq Scan on part  (cost=3D0.00..856=
1.00 rows=3D1 width=3D4)
                              ->  Aggregate  (cost=3D119.61..119.61 rows=3D=
1 width=3D4)
                                    ->  Index Scan using lineitem_index on =
lineitem  (cost=3D0.00..119.61 rows=3D1 width=3D4)

partsupp::800000 tuples
            Table "partsupp"
   Column   |      Type      | Modifiers=20
------------+----------------+-----------
 partkey    | integer        | not null
 suppkey    | integer        | not null
 availqty   | integer        |=20
 supplycost | numeric(10,2)  |=20
 comment    | character(199) |=20
Primary key: partsupp_pkey
Triggers: RI_ConstraintTrigger_16597,
          RI_ConstraintTrigger_16603

tpch=3D# select attname,n_distinct,correlation from pg_stats where tablenam=
e=3D'partsupp';
  attname   | n_distinct | correlation=20
------------+------------+-------------
 partkey    |  -0.195588 |           1
 suppkey    |       9910 |  0.00868363
 availqty   |       9435 | -0.00788662
 supplycost |  -0.127722 |  -0.0116864
 comment    |         -1 |   0.0170702

I accept query changes, reordering, indexes ideas and horizontal partitioni=
ng
thanks in advance.
Regards




------=_NextPart_000_005B_01C228FF.7FFB6AB0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2716.2200" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I can't improve performance on this=20
query:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial=20
size=3D2>SELECT<BR>&nbsp;supplier.name,<BR>&nbsp;supplier.address<BR>FROM<B=
R>&nbsp;supplier,<BR>&nbsp;nation<BR>WHERE<BR>&nbsp;supplier.suppkey=20
IN(<BR>&nbsp;&nbsp;SELECT<BR>&nbsp;&nbsp;&nbsp;partsupp.suppkey<BR>&nbsp;&n=
bsp;FROM<BR>&nbsp;&nbsp;&nbsp;partsupp<BR>&nbsp;&nbsp;WHERE<BR>&nbsp;&nbsp;=
&nbsp;partsupp.partkey=20
IN(<BR>&nbsp;&nbsp;&nbsp;&nbsp;SELECT<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;part=
.partkey<BR>&nbsp;&nbsp;&nbsp;&nbsp;FROM<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;p=
art<BR>&nbsp;&nbsp;&nbsp;&nbsp;WHERE<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;part.=
name=20
like 'forest%'<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)<BR>&nbsp;&nbsp;&nbsp;AND=
=20
partsupp.availqty&gt;(<BR>&nbsp;&nbsp;&nbsp;&nbsp;SELECT<BR>&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;0.5*(sum(lineitem.quantity)::FLOAT)<BR>&nbsp;&nbsp;&nbsp;&nb=
sp;FROM<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;lineitem<BR>&nbsp;&nbsp;&nbsp;&nbs=
p;WHERE<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;lineitem.partkey=3Dpartsupp.partke=
y<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND=20
lineitem.suppkey=3Dpartsupp.partkey<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND=20
lineitem.shipdate&gt;=3D('1994-01-01')::DATE<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;AND=20
lineitem.shipdate&lt;(('1994-01-01')::DATE+('1=20
year')::INTERVAL)::DATE<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)<BR>&nbsp;&nbsp;)=
<BR>&nbsp;AND=20
supplier.nationkey=3Dnation.nationkey<BR>&nbsp;AND nation.name=3D'CANADA'<B=
R>ORDER=20
BY<BR>&nbsp;supplier.name;<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>explain results:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>NOTICE:&nbsp; QUERY PLAN:</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Sort&nbsp; (cost=3D2777810917708.17..27778=
10917708.17=20
rows=3D200 width=3D81)<BR>&nbsp; -&gt;&nbsp; Nested Loop&nbsp;=20
(cost=3D0.00..2777810917700.53 rows=3D200=20
width=3D81)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq S=
can on=20
nation&nbsp; (cost=3D0.00..1.31 rows=3D1=20
width=3D4)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index =
Scan=20
using snation_index on supplier&nbsp; (cost=3D0.00..2777810917696.72 rows=
=3D200=20
width=3D77)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;=20
SubPlan<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;=20
-&gt;&nbsp; Materialize&nbsp; (cost=3D6944527291.72..6944527291.72 rows=3D1=
33333=20
width=3D4)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
-&gt;&nbsp; Seq Scan on partsupp&nbsp; (cost=3D0.00..6944527291.72 rows=3D1=
33333=20
width=3D4)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;=20
SubPlan<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
-&gt;&nbsp; Materialize&nbsp; (cost=3D8561.00..8561.00 rows=3D1=20
width=3D4)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
=20
-&gt;&nbsp; Seq Scan on part&nbsp; (cost=3D0.00..8561.00 rows=3D1=20
width=3D4)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
-&gt;&nbsp; Aggregate&nbsp; (cost=3D119.61..119.61 rows=3D1=20
width=3D4)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
=20
-&gt;&nbsp; Index Scan using lineitem_index on lineitem&nbsp; (cost=3D0.00.=
.119.61=20
rows=3D1 width=3D4)<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>partsupp::800000 tuples</FONT></DIV>
<DIV><FONT face=3DArial=20
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 Table=20
"partsupp"<BR>&nbsp;&nbsp; Column&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;=20
Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Modifiers=20
<BR>------------+----------------+-----------<BR>&nbsp;partkey&nbsp;&nbsp;&=
nbsp;=20
| integer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | not=20
null<BR>&nbsp;suppkey&nbsp;&nbsp;&nbsp; |=20
integer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | not=20
null<BR>&nbsp;availqty&nbsp;&nbsp; |=20
integer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | <BR>&nbsp;supplycost |=
=20
numeric(10,2)&nbsp; | <BR>&nbsp;comment&nbsp;&nbsp;&nbsp; | character(199) =
|=20
<BR>Primary key: partsupp_pkey<BR>Triggers:=20
RI_ConstraintTrigger_16597,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;=20
RI_ConstraintTrigger_16603<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>tpch=3D# select attname,n_distinct,correla=
tion from=20
pg_stats where tablename=3D'partsupp';<BR>&nbsp; attname&nbsp;&nbsp; | n_di=
stinct=20
| correlation=20
<BR>------------+------------+-------------<BR>&nbsp;partkey&nbsp;&nbsp;&nb=
sp;=20
|&nbsp; -0.195588 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;=20
1<BR>&nbsp;suppkey&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
9910=20
|&nbsp; 0.00868363<BR>&nbsp;availqty&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9435 | -0.00788662<BR>&nbsp;supplycos=
t=20
|&nbsp; -0.127722 |&nbsp; -0.0116864<BR>&nbsp;comment&nbsp;&nbsp;&nbsp;=20
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -1 |&nbsp;&nbsp;=20
0.0170702<BR></DIV></FONT>
<DIV><FONT face=3DArial size=3D2>I accept query changes, reordering, indexe=
s ideas=20
and horizontal partitioning</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>thanks in advance.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Regards</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;</DIV></FONT>
<DIV><FONT face=3DArial size=3D2>&nbsp;</DIV></FONT></BODY></HTML>

------=_NextPart_000_005B_01C228FF.7FFB6AB0--