From lamigo@atc.unican.es Thu Jul  11 15:18:20 2002
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--





From mkoi-pg@aon.at Thu Jul  11 16:48:16 2002
Received: from email04.aon.at (WARSL402PIP5.highway.telekom.at [195.3.96.79])
	by postgresql.org (Postfix) with SMTP id 18D694759BF
	for <pgsql-sql@postgresql.org>; Thu, 11 Jul 2002 12:48:16 -0400 (EDT)
Received: (qmail 335918 invoked from network); 11 Jul 2002 16:48:20 -0000
Received: from m152p011.dipool.highway.telekom.at (HELO cantor)
	([62.46.8.235]) (envelope-sender <mkoi-pg@aon.at>)
	by qmail5rs.highway.telekom.at (qmail-ldap-1.03) with SMTP
	for <lamigo@atc.unican.es>; 11 Jul 2002 16:48:20 -0000
From: Manfred Koizar <mkoi-pg@aon.at>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: [HACKERS] please help on query
Date: Thu, 11 Jul 2002 18:47:03 +0200
Message-ID: <gu9riu8qp28ifp40qsva7b9puif47ihi5b@4ax.com>
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
In-Reply-To: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
X-Mailer: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200207/166
X-Sequence-Number: 8545

[moving to pgsql-sql]
On Thu, 11 Jul 2002 17:22:14 +0200, "Luis Alberto Amigo Navarro"
<lamigo@atc.unican.es> wrote:
>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=partsupp.partkey
>     AND lineitem.suppkey=partsupp.partkey
                                    ^^^^^^^
                                    suppkey ???
>     AND lineitem.shipdate>=('1994-01-01')::DATE
>     AND lineitem.shipdate<(('1994-01-01')::DATE+('1 year')::INTERVAL)::DATE
>     )
>  )
> AND supplier.nationkey=nation.nationkey
> AND nation.name='CANADA'
>ORDER BY
> supplier.name;

Luis,
rules of thumb: "Avoid subselects; use joins!" and "If you have to use
subselects, avoid IN, use EXISTS!"

Let's try.  If partkey is unique in part, then
|   FROM partsupp
|   WHERE partsupp.partkey IN (SELECT part.partkey

can be replaced by
    FROM partsupp ps, part p
    WHERE ps.partkey = p.partkey

or
    partsupp ps INNER JOIN part p
      ON (ps.partkey = p.partkey AND p.name LIKE '...')

When we ignore "part" for now, your subselect boils down to

|  SELECT partsupp.suppkey
|  FROM partsupp
|  WHERE partsupp.availqty > (
|    SELECT 0.5*(sum(lineitem.quantity)::FLOAT)
|    FROM lineitem
|    WHERE lineitem.partkey=partsupp.partkey
|      AND lineitem.suppkey=partsupp.suppkey
|      AND lineitem.shipdate BETWEEN ... AND ...
|     )

which can be rewritten to (untested)

  SELECT ps.suppkey
  FROM partsupp ps, lineitem li
  WHERE li.partkey=ps.partkey
    AND li.suppkey=ps.suppkey
    AND lineitem.shipdate BETWEEN ... AND ...
  GROUP BY ps.partkey, ps.suppkey
  HAVING min(ps.availqty) > 0.5*(sum(lineitem.quantity)::FLOAT)
         ^^^
         As all ps.availqty are equal in one group, you can as well
use max() or avg().

Now we have left only one IN:
| WHERE supplier.suppkey IN (
|  SELECT partsupp.suppkey FROM partsupp WHERE <condition> )

Being to lazy to find out, if this can be rewritten to a join, let`s
apply rule 2 here:

  WHERE EXISTS (
    SELECT ... FROM partsupp ps
    WHERE supplier.suppkey = ps.suppkey
      AND <condition> )

HTH, but use with a grain of salt ...

>Sort  (cost=2777810917708.17..2777810917708.17 rows=200 width=81)
             ^^^^^^^^^^^^^^^^
BTW, how many years are these? :-)

Servus
 Manfred



From lamigo@atc.unican.es Thu Jul  11 17:36:38 2002
Received: from bonito.atc.unican.es (bonito.atc.unican.es [193.144.185.218])
	by postgresql.org (Postfix) with ESMTP id EF36B475C1E
	for <pgsql-sql@postgresql.org>; Thu, 11 Jul 2002 13:36:38 -0400 (EDT)
Received: from ALIGOTE (aligote [193.144.185.202])
	by bonito.atc.unican.es (8.11.0/8.8.7) with SMTP id g6BHaYb09187;
	Thu, 11 Jul 2002 19:36:34 +0200
Message-ID: <007c01c22902$16e699c0$cab990c1@atc.unican.es>
From: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
To: "Manfred Koizar" <mkoi-pg@aon.at>
Cc: <pgsql-sql@postgresql.org>
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
	<gu9riu8qp28ifp40qsva7b9puif47ihi5b@4ax.com>
Subject: Re: [HACKERS] please help on query
Date: Thu, 11 Jul 2002 19:40:46 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
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/182
X-Sequence-Number: 8561

I've tried
SELECT
 supplier.name,
 supplier.address
FROM
 supplier,
 nation,
 lineitem
WHERE
 EXISTS(
  SELECT
   partsupp.suppkey
  FROM
   partsupp,lineitem
  WHERE
   lineitem.partkey=partsupp.partkey
   AND lineitem.suppkey=partsupp.partkey
   AND lineitem.shipdate>=('1994-01-01')::DATE
   AND lineitem.shipdate<(('1994-01-01')::DATE+('1 year')::INTERVAL)::DATE
   AND EXISTS(
    SELECT
     part.partkey
    FROM
     part
    WHERE
     part.name like 'forest%'
     )
  GROUP BY partsupp.partkey,partsupp.suppkey
  HAVING min(availqty)>(0.5*(sum(lineitem.quantity)::FLOAT))
  )
 AND supplier.nationkey=nation.nationkey
 AND nation.name='CANADA'
ORDER BY
 supplier.name;

as you said and something is wrong
Sort  (cost=1141741215.35..1141741215.35 rows=2400490000 width=81)
  InitPlan
    ->  Aggregate  (cost=0.00..921773.85 rows=48 width=24)
          InitPlan
            ->  Seq Scan on part  (cost=0.00..8561.00 rows=1 width=4)
          ->  Group  (cost=0.00..921771.44 rows=481 width=24)
                ->  Result  (cost=0.00..921769.04 rows=481 width=24)
                      ->  Merge Join  (cost=0.00..921769.04 rows=481
width=24)
                            ->  Index Scan using partsupp_pkey on partsupp
(cost=0.00..98522.75 rows=800000 width=12)
                            ->  Index Scan using lsupp_index on lineitem
(cost=0.00..821239.91 rows=145 width=12)
  ->  Result  (cost=1.31..112888690.31 rows=2400490000 width=81)
        ->  Nested Loop  (cost=1.31..112888690.31 rows=2400490000 width=81)
              ->  Hash Join  (cost=1.31..490.31 rows=400 width=81)
                    ->  Seq Scan on supplier  (cost=0.00..434.00 rows=10000
width=77)
                    ->  Hash  (cost=1.31..1.31 rows=1 width=4)
                          ->  Seq Scan on nation  (cost=0.00..1.31 rows=1
width=4)
              ->  Seq Scan on lineitem  (cost=0.00..222208.25 rows=6001225
width=0)

where might be my mistake
Thanks and regards
----- Original Message -----
From: "Manfred Koizar" <mkoi-pg@aon.at>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, July 11, 2002 6:47 PM
Subject: Re: [HACKERS] please help on query


> [moving to pgsql-sql]
> On Thu, 11 Jul 2002 17:22:14 +0200, "Luis Alberto Amigo Navarro"
> <lamigo@atc.unican.es> wrote:
> >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=partsupp.partkey
> >     AND lineitem.suppkey=partsupp.partkey
>                                     ^^^^^^^
>                                     suppkey ???
> >     AND lineitem.shipdate>=('1994-01-01')::DATE
> >     AND lineitem.shipdate<(('1994-01-01')::DATE+('1
year')::INTERVAL)::DATE
> >     )
> >  )
> > AND supplier.nationkey=nation.nationkey
> > AND nation.name='CANADA'
> >ORDER BY
> > supplier.name;
>






From jrnield@usol.com Thu Jul  11 18:07:52 2002
Received: from hades.usol.com (hades.usol.com [208.232.58.41])
	by postgresql.org (Postfix) with ESMTP id D5054474E4E
	for <pgsql-hackers@postgresql.org>;
	Thu, 11 Jul 2002 14:07:52 -0400 (EDT)
Received: from 08-039.024.popsite.net (08-039.024.popsite.net [66.19.4.39])
	by hades.usol.com (8.11.6/8.11.6) with ESMTP id g6BI7n111965;
	Thu, 11 Jul 2002 14:07:49 -0400
Subject: Re: please help on query
From: "J. R. Nield" <jrnield@usol.com>
To: Luis Alberto Amigo Navarro <lamigo@atc.unican.es>
Cc: PostgreSQL Hacker <pgsql-hackers@postgresql.org>
In-Reply-To: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Mailer: Ximian Evolution 1.0.3 (1.0.3-6) 
Date: 11 Jul 2002 14:06:47 -0400
Message-Id: <1026410810.1199.8.camel@localhost.localdomain>
Mime-Version: 1.0
X-Archive-Number: 200207/465
X-Sequence-Number: 24839

On Thu, 2002-07-11 at 11:22, Luis Alberto Amigo Navarro wrote:
> I can't improve performance on this query:

Blame Canada!

-- 
J. R. Nield
jrnield@usol.com






From akopciuch@olympusproject.org Thu Jul  11 18:14:00 2002
Received: from mail.nucleus.com (mail.nucleus.com [207.34.93.23])
	by postgresql.org (Postfix) with ESMTP id D42CB474E4E
	for <pgsql-hackers@postgresql.org>;
	Thu, 11 Jul 2002 14:14:00 -0400 (EDT)
Received: from kaitlynn (unverified [209.115.166.163]) by mail.nucleus.com
	(Vircom SMTPRS 1.4.231) with ESMTP id <B0057405715@mail.nucleus.com>;
	Thu, 11 Jul 2002 12:13:53 -0600
Content-Type: text/plain;
  charset="utf-8"
From: Andy Kopciuch <akopciuch@olympusproject.org>
Reply-To: akopciuch@olympusproject.org
Organization: Olympus Project
To: "J. R. Nield" <jrnield@usol.com>
Subject: Re: please help on query
Date: Thu, 11 Jul 2002 12:09:52 -0600
X-Mailer: KMail [version 1.4.5]
Cc: PostgreSQL Hacker <pgsql-hackers@postgresql.org>
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
	<1026410810.1199.8.camel@localhost.localdomain>
In-Reply-To: <1026410810.1199.8.camel@localhost.localdomain>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200207111209.52653.akopciuch@olympusproject.org>
X-Archive-Number: 200207/466
X-Sequence-Number: 24840

On Thursday 11 July 2002 12:06, J. R. Nield wrote:
> On Thu, 2002-07-11 at 11:22, Luis Alberto Amigo Navarro wrote:
> > I can't improve performance on this query:
>
> Blame Canada!

Whatever ... 

How's that silver medal down there in the states?

;-)




From mkoi-pg@aon.at Thu Jul  11 18:47:27 2002
Received: from email04.aon.at (WARSL402PIP5.highway.telekom.at [195.3.96.79])
	by postgresql.org (Postfix) with SMTP id 33152474E4E
	for <pgsql-sql@postgresql.org>; Thu, 11 Jul 2002 14:47:27 -0400 (EDT)
Received: (qmail 409734 invoked from network); 11 Jul 2002 18:46:16 -0000
Received: from m150p015.dipool.highway.telekom.at (HELO cantor)
	([62.46.8.175]) (envelope-sender <mkoi-pg@aon.at>)
	by qmail5rs.highway.telekom.at (qmail-ldap-1.03) with SMTP
	for <lamigo@atc.unican.es>; 11 Jul 2002 18:46:16 -0000
From: Manfred Koizar <mkoi-pg@aon.at>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: [HACKERS] please help on query
Date: Thu, 11 Jul 2002 20:44:58 +0200
Message-ID: <47jriu8760vo9n9a4ffvtl165ebe7rvksj@4ax.com>
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
	<gu9riu8qp28ifp40qsva7b9puif47ihi5b@4ax.com>
	<007c01c22902$16e699c0$cab990c1@atc.unican.es>
In-Reply-To: <007c01c22902$16e699c0$cab990c1@atc.unican.es>
X-Mailer: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200207/168
X-Sequence-Number: 8547

On Thu, 11 Jul 2002 19:40:46 +0200, "Luis Alberto Amigo Navarro"
<lamigo@atc.unican.es> wrote:
>I've tried
[reformatted to fit on one page]
| SELECT supplier.name, supplier.address
| FROM supplier, nation, lineitem
You already found out that you do not need lineitem here.

| WHERE EXISTS(
|   SELECT partsupp.suppkey
|   FROM partsupp,lineitem
|   WHERE
|    lineitem.partkey=partsupp.partkey
|    AND lineitem.suppkey=partsupp.partkey
I still don't believe this  suppkey=partkey

|    AND lineitem.shipdate [...]
|    AND EXISTS( SELECT part.partkey
|                FROM part WHERE part.name like 'forest%')
This subselect gives either true or false, but in any case always the
same result.  You might want to add a condition
	AND part.partkey=partsupp.partkey

Are you sure partkey is not unique?  If it is unique you can replace
this subselect by a join.

|   GROUP BY partsupp.partkey,partsupp.suppkey
|   HAVING min(availqty)>(0.5*(sum(lineitem.quantity)::FLOAT))
|   )
|  AND supplier.nationkey=nation.nationkey
|  AND nation.name='CANADA'
| ORDER BY supplier.name;

>as you said and something is wrong
>Sort  (cost=1141741215.35..1141741215.35 rows=2400490000 width=81)

The cost is now only 1141741215.35 compared to 2777810917708.17
before;  this is an improvement factor of more than 2000.  So what's
your problem? ;-)

Servus
 Manfred



From hannu@tm.ee Thu Jul  11 19:46:41 2002
Received: from taru.tm.ee (unknown [213.180.2.168])
	by postgresql.org (Postfix) with ESMTP id 1E5AD475914
	for <pgsql-hackers@postgresql.org>;
	Thu, 11 Jul 2002 15:46:41 -0400 (EDT)
Received: (from hannu@localhost) by taru.tm.ee (8.11.6/8.11.6) id
 g6BKm8L18764;
	Thu, 11 Jul 2002 22:48:08 +0200
X-Authentication-Warning: taru.tm.ee: hannu set sender to hannu@tm.ee using -f
Subject: Re: please help on query
From: Hannu Krosing <hannu@tm.ee>
To: Luis Alberto Amigo Navarro <lamigo@atc.unican.es>
Cc: pgsql-hackers@postgresql.org
In-Reply-To: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Mailer: Ximian Evolution 1.0.3.99 
Date: 11 Jul 2002 22:48:08 +0200
Message-Id: <1026420488.18194.18.camel@taru.tm.ee>
Mime-Version: 1.0
X-Archive-Number: 200207/468
X-Sequence-Number: 24842

On Thu, 2002-07-11 at 17:22, Luis Alberto Amigo Navarro wrote:
> I can't improve performance on this query:

You could try rewriting the IN's into = joins
or even use explicit INNER JOIN syntax to force certain plans

with a select inside another and depending on value of partsupp.partkey
it is really hard for optimiser to do anything else than to perform the
query for each row.

But it may help to rewrite

  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=partsupp.partkey
     AND lineitem.suppkey=partsupp.partkey
     AND lineitem.shipdate>=('1994-01-01')::DATE
     AND lineitem.shipdate<(('1994-01-01')::DATE+('1
year')::INTERVAL)::DATE
     )
  )

into

  SELECT
    partsupp.suppkey
  FROM
   partsupp,
   (SELECT part.partkey as partkey
      FROM part
     WHERE part.name like 'forest%'
    ) fp,
   (SELECT 0.5*(sum(lineitem.quantity)::FLOAT) as halfsum,
           partkey
      FROM lineitem
    WHERE
     lineitem.partkey=partsupp.partkey
     AND lineitem.suppkey=partsupp.partkey
     AND lineitem.shipdate>=('1994-01-01')::DATE
     AND lineitem.shipdate<(('1994-01-01')::DATE+('1
year')::INTERVAL)::DATE
    ) li
  WHERE partsupp.partkey = fp.partkey 
   AND partsupp.partkey  = li.partkey 
   AND partsupp.availqty > halfsum

if "lineitem" is significantly smaller than "partsupp"



But you really should tell us more, like how many lines does lineitem
and other tables have,  

----------
Hannu




From hannu@tm.ee Thu Jul  11 19:49:25 2002
Received: from taru.tm.ee (unknown [213.180.2.168])
	by postgresql.org (Postfix) with ESMTP id 794CE475A1B
	for <pgsql-hackers@postgresql.org>;
	Thu, 11 Jul 2002 15:49:25 -0400 (EDT)
Received: (from hannu@localhost) by taru.tm.ee (8.11.6/8.11.6) id
 g6BKp4n18775;
	Thu, 11 Jul 2002 22:51:04 +0200
X-Authentication-Warning: taru.tm.ee: hannu set sender to hannu@tm.ee using -f
Subject: Re: please help on query
From: Hannu Krosing <hannu@tm.ee>
To: Luis Alberto Amigo Navarro <lamigo@atc.unican.es>
Cc: pgsql-hackers@postgresql.org
In-Reply-To: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Mailer: Ximian Evolution 1.0.3.99 
Date: 11 Jul 2002 22:51:04 +0200
Message-Id: <1026420664.18193.21.camel@taru.tm.ee>
Mime-Version: 1.0
X-Archive-Number: 200207/469
X-Sequence-Number: 24843

On Thu, 2002-07-11 at 17:22, Luis Alberto Amigo Navarro wrote:
> I can't improve performance on this query:

You may also want to rewrite

lineitem.shipdate<(('1994-01-01')::DATE+('1 year')::INTERVAL)::DATE

into

lineitem.shipdate<(('1995-01-01')::DATE

if you can, as probably the optimiser will not recognize it else as a
constant and won't use index on lineitem.shipdate.

----------------
Hannu





From hannu@tm.ee Thu Jul  11 20:06:36 2002
Received: from taru.tm.ee (unknown [213.180.2.168])
	by postgresql.org (Postfix) with ESMTP id E23174759AF
	for <pgsql-hackers@postgresql.org>;
	Thu, 11 Jul 2002 16:06:36 -0400 (EDT)
Received: (from hannu@localhost) by taru.tm.ee (8.11.6/8.11.6) id
 g6BL8F418798;
	Thu, 11 Jul 2002 23:08:15 +0200
X-Authentication-Warning: taru.tm.ee: hannu set sender to hannu@tm.ee using -f
Subject: Re: please help on query
From: Hannu Krosing <hannu@tm.ee>
To: Luis Alberto Amigo Navarro <lamigo@atc.unican.es>
Cc: pgsql-hackers@postgresql.org
In-Reply-To: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Mailer: Ximian Evolution 1.0.3.99 
Date: 11 Jul 2002 23:08:15 +0200
Message-Id: <1026421695.18194.24.camel@taru.tm.ee>
Mime-Version: 1.0
X-Archive-Number: 200207/470
X-Sequence-Number: 24844

On Thu, 2002-07-11 at 17:22, Luis Alberto Amigo Navarro wrote:
> I can't improve performance on this query:


This _may_ work.

SELECT
    supplier.name,
    supplier.address
  FROM
    supplier,
    nation,
 WHERE supplier.suppkey IN (
    SELECT part.partkey
      FROM part
     WHERE part.name like 'forest%'
     INNER JOIN partsupp ON part.partkey=partsupp.partkey
     INNER JOIN (
         SELECT 0.5*(sum(lineitem.quantity)::FLOAT) as halfsum
           FROM lineitem
          WHERE lineitem.partkey=partsupp.partkey
            AND shipdate >= '1994-01-01'
            AND shipdate <  '1995-01-01'
     ) li ON partsupp.availqty > halfsum
  )
  AND supplier.nationkey=nation.nationkey
  AND nation.name='CANADA'
ORDER BY supplier.name;

---------------
Hannu




From lamigo@atc.unican.es Fri Jul  12 11:33:15 2002
Received: from bonito.atc.unican.es (bonito.atc.unican.es [193.144.185.218])
	by postgresql.org (Postfix) with ESMTP id BE18C47589D
	for <pgsql-sql@postgresql.org>; Fri, 12 Jul 2002 07:33:15 -0400 (EDT)
Received: from ALIGOTE (aligote [193.144.185.202])
	by bonito.atc.unican.es (8.11.0/8.8.7) with SMTP id g6CBXIb12841;
	Fri, 12 Jul 2002 13:33:18 +0200
Message-ID: <012101c22998$8bbe5670$cab990c1@atc.unican.es>
From: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
To: "Manfred Koizar" <mkoi-pg@aon.at>
Cc: <pgsql-sql@postgresql.org>
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
	<gu9riu8qp28ifp40qsva7b9puif47ihi5b@4ax.com>
	<007c01c22902$16e699c0$cab990c1@atc.unican.es>
	<47jriu8760vo9n9a4ffvtl165ebe7rvksj@4ax.com>
Subject: Re: [HACKERS] please help on query
Date: Fri, 12 Jul 2002 13:37:47 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
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/178
X-Sequence-Number: 8557




> The cost is now only 1141741215.35 compared to 2777810917708.17
> before;  this is an improvement factor of more than 2000.  So what's
> your problem? ;-)
>
> Servus
>  Manfred
>
In fact planner is estimating incredibly badly, it took only 833msecs now
runs perfectly

I'm going to keep on asking about another query:

SELECT
 customer.name,
 customer.custkey,
 orders.orderkey,
 orders.orderdate,
 orders.totalprice,
 sum(lineitem.quantity)
FROM
 customer,
 orders,
 lineitem
WHERE
 exists(
  SELECT
   lineitem.orderkey
  FROM
   lineitem
  WHERE
   lineitem.orderkey=orders.orderkey
  GROUP BY
   lineitem.orderkey HAVING
   sum(lineitem.quantity)>300
  )
 AND customer.custkey=orders.custkey
 AND orders.orderkey=lineitem.orderkey
GROUP BY
 customer.name,
 customer.custkey,
 orders.orderkey,
 orders.orderdate,
 orders.totalprice

ORDER BY
 orders.totalprice DESC,
 orders.orderdate;

NOTICE:  QUERY PLAN:

Sort  (cost=26923941.97..26923941.97 rows=300061 width=66)
  ->  Aggregate  (cost=26851634.86..26896644.05 rows=300061 width=66)
        ->  Group  (cost=26851634.86..26889142.52 rows=3000612 width=66)
              ->  Sort  (cost=26851634.86..26851634.86 rows=3000612
width=66)
                    ->  Hash Join  (cost=26107574.81..26457309.10
rows=3000612 width=66)
                          ->  Seq Scan on lineitem  (cost=0.00..222208.25
rows=6001225 width=8)
                          ->  Hash  (cost=26105699.81..26105699.81
rows=750000 width=58)
                                ->  Hash Join  (cost=7431.00..26105699.81
rows=750000 width=58)
                                      ->  Seq Scan on orders
(cost=0.00..26083268.81 rows=750000 width=25)
                                            SubPlan
                                              ->  Aggregate
(cost=0.00..17.35 rows=1 width=8)
                                                    ->  Group
(cost=0.00..17.34 rows=5 width=8)
                                                          ->  Index Scan
using lineitem_pkey on lineitem  (cost=0.00..17.33 rows=5 width=8)
                                      ->  Hash  (cost=7056.00..7056.00
rows=150000 width=33)
                                            ->  Seq Scan on customer
(cost=0.00..7056.00 rows=150000 width=33)

again:
orders 1500000 tuples
lineitem 6000000 tuples there are 1 to 7 lineitems per orderkey
Customer 150000 tuples

select attname,n_distinct,correlation from pg_stats where
tablename='lineitem';
    attname    | n_distinct | correlation
---------------+------------+-------------
 orderkey      |  -0.199847 |           1
 partkey       |     196448 |   0.0223377
 suppkey       |       9658 | -0.00822751
 linenumber    |          7 |     0.17274
 quantity      |         50 |   0.0150153
 extendedprice |      25651 | -0.00790245
 discount      |         11 |    0.103761
 tax           |          9 |   0.0993771
 returnflag    |          3 |    0.391434
 linestatus    |          2 |    0.509791
 shipdate      |       2440 |   0.0072777
 commitdate    |       2497 |  0.00698162
 receiptdate   |       2416 |  0.00726686
 shipinstruct  |          4 |    0.241511
 shipmode      |          7 |    0.138432
 comment       |     275488 |   0.0188006
(16 rows)

select attname,n_distinct,correlation from pg_stats where
tablename='orders';
    attname    | n_distinct | correlation
---------------+------------+-------------
 orderkey      |         -1 |   -0.999925
 custkey       |      76309 |  0.00590596
 orderstatus   |          3 |    0.451991
 totalprice    |         -1 | -0.00768806
 orderdate     |       2431 |  -0.0211354
 orderpriority |          5 |    0.182489
 clerk         |       1009 |  0.00546939
 shippriority  |          1 |           1
 comment       |  -0.750125 |  -0.0123887

Customer
  attname   | n_distinct | correlation
------------+------------+-------------
 custkey    |         -1 |           1
 name       |         -1 |           1
 address    |         -1 | -0.00510274
 nationkey  |         25 |   0.0170533
 phone      |         -1 |  -0.0227816
 acctbal    |   -0.83444 | -0.00220958
 mktsegment |          5 |    0.205013
 comment    |         -1 |   0.0327827

This query takes 12 minutes to run and returns about 50 customers.
lineitem.quantity takes values from 1 to 50, so 300 per orderkey is very
restrictive

May someone help on improving performance?
Again thanks in advance
Regards





From jakub.ouhrabka@comgate.cz Fri Jul  12 11:50:31 2002
Received: from mx.smartnet.cz (mx.smartnet.cz [193.86.76.44])
	by postgresql.org (Postfix) with ESMTP id BCCEB4750F5
	for <pgsql-sql@postgresql.org>; Fri, 12 Jul 2002 07:50:31 -0400 (EDT)
Received: from kuba (helo=localhost)
	by mx.smartnet.cz with local-esmtp (Exim 3.35 #1 (Debian))
	id 17SywP-0002GX-00; Fri, 12 Jul 2002 13:50:29 +0200
Date: Fri, 12 Jul 2002 13:50:29 +0200 (CEST)
From: Jakub Ouhrabka <jakub.ouhrabka@comgate.cz>
X-X-Sender: kuba@server
To: Luis Alberto Amigo Navarro <lamigo@atc.unican.es>
Cc: Manfred Koizar <mkoi-pg@aon.at>, <pgsql-sql@postgresql.org>
Subject: Re: [HACKERS] please help on query
In-Reply-To: <012101c22998$8bbe5670$cab990c1@atc.unican.es>
Message-ID: <Pine.LNX.4.44.0207121343260.8671-100000@server>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200207/181
X-Sequence-Number: 8560

hi,

avoid subselect: create a temp table and use join...

CREATE TEMP TABLE tmp AS
   SELECT
    lineitem.orderkey
   FROM
    lineitem
   WHERE
    lineitem.orderkey=orders.orderkey
   GROUP BY
    lineitem.orderkey HAVING
    sum(lineitem.quantity)>300;

CREATE INDEX tmp_idx ON tmp (orderkey);

 SELECT
  customer.name,
  customer.custkey,
  orders.orderkey,
  orders.orderdate,
  orders.totalprice,
  sum(lineitem.quantity)
 FROM
  customer,
  orders,
  lineitem,
  tmp
 WHERE
  orders.orderkey=tmp.orderkey
  AND customer.custkey=orders.custkey
  AND orders.orderkey=lineitem.orderkey
 GROUP BY
  customer.name,
  customer.custkey,
  orders.orderkey,
  orders.orderdate,
  orders.totalprice
 ORDER BY
  orders.totalprice DESC,
  orders.orderdate;


may be the index is not necessary...

kuba


> I'm going to keep on asking about another query:
>
> SELECT
>  customer.name,
>  customer.custkey,
>  orders.orderkey,
>  orders.orderdate,
>  orders.totalprice,
>  sum(lineitem.quantity)
> FROM
>  customer,
>  orders,
>  lineitem
> WHERE
>  exists(
>   SELECT
>    lineitem.orderkey
>   FROM
>    lineitem
>   WHERE
>    lineitem.orderkey=orders.orderkey
>   GROUP BY
>    lineitem.orderkey HAVING
>    sum(lineitem.quantity)>300
>   )
>  AND customer.custkey=orders.custkey
>  AND orders.orderkey=lineitem.orderkey
> GROUP BY
>  customer.name,
>  customer.custkey,
>  orders.orderkey,
>  orders.orderdate,
>  orders.totalprice
>
> ORDER BY
>  orders.totalprice DESC,
>  orders.orderdate;
>
> NOTICE:  QUERY PLAN:
>
> Sort  (cost=26923941.97..26923941.97 rows=300061 width=66)
>   ->  Aggregate  (cost=26851634.86..26896644.05 rows=300061 width=66)
>         ->  Group  (cost=26851634.86..26889142.52 rows=3000612 width=66)
>               ->  Sort  (cost=26851634.86..26851634.86 rows=3000612
> width=66)
>                     ->  Hash Join  (cost=26107574.81..26457309.10
> rows=3000612 width=66)
>                           ->  Seq Scan on lineitem  (cost=0.00..222208.25
> rows=6001225 width=8)
>                           ->  Hash  (cost=26105699.81..26105699.81
> rows=750000 width=58)
>                                 ->  Hash Join  (cost=7431.00..26105699.81
> rows=750000 width=58)
>                                       ->  Seq Scan on orders
> (cost=0.00..26083268.81 rows=750000 width=25)
>                                             SubPlan
>                                               ->  Aggregate
> (cost=0.00..17.35 rows=1 width=8)
>                                                     ->  Group
> (cost=0.00..17.34 rows=5 width=8)
>                                                           ->  Index Scan
> using lineitem_pkey on lineitem  (cost=0.00..17.33 rows=5 width=8)
>                                       ->  Hash  (cost=7056.00..7056.00
> rows=150000 width=33)
>                                             ->  Seq Scan on customer
> (cost=0.00..7056.00 rows=150000 width=33)
>
> again:
> orders 1500000 tuples
> lineitem 6000000 tuples there are 1 to 7 lineitems per orderkey
> Customer 150000 tuples
>
> select attname,n_distinct,correlation from pg_stats where
> tablename='lineitem';
>     attname    | n_distinct | correlation
> ---------------+------------+-------------
>  orderkey      |  -0.199847 |           1
>  partkey       |     196448 |   0.0223377
>  suppkey       |       9658 | -0.00822751
>  linenumber    |          7 |     0.17274
>  quantity      |         50 |   0.0150153
>  extendedprice |      25651 | -0.00790245
>  discount      |         11 |    0.103761
>  tax           |          9 |   0.0993771
>  returnflag    |          3 |    0.391434
>  linestatus    |          2 |    0.509791
>  shipdate      |       2440 |   0.0072777
>  commitdate    |       2497 |  0.00698162
>  receiptdate   |       2416 |  0.00726686
>  shipinstruct  |          4 |    0.241511
>  shipmode      |          7 |    0.138432
>  comment       |     275488 |   0.0188006
> (16 rows)
>
> select attname,n_distinct,correlation from pg_stats where
> tablename='orders';
>     attname    | n_distinct | correlation
> ---------------+------------+-------------
>  orderkey      |         -1 |   -0.999925
>  custkey       |      76309 |  0.00590596
>  orderstatus   |          3 |    0.451991
>  totalprice    |         -1 | -0.00768806
>  orderdate     |       2431 |  -0.0211354
>  orderpriority |          5 |    0.182489
>  clerk         |       1009 |  0.00546939
>  shippriority  |          1 |           1
>  comment       |  -0.750125 |  -0.0123887
>
> Customer
>   attname   | n_distinct | correlation
> ------------+------------+-------------
>  custkey    |         -1 |           1
>  name       |         -1 |           1
>  address    |         -1 | -0.00510274
>  nationkey  |         25 |   0.0170533
>  phone      |         -1 |  -0.0227816
>  acctbal    |   -0.83444 | -0.00220958
>  mktsegment |          5 |    0.205013
>  comment    |         -1 |   0.0327827
>
> This query takes 12 minutes to run and returns about 50 customers.
> lineitem.quantity takes values from 1 to 50, so 300 per orderkey is very
> restrictive
>
> May someone help on improving performance?
> Again thanks in advance
> Regards
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>




From jouh8664@ss1000.ms.mff.cuni.cz Fri Jul  12 11:52:34 2002
Received: from mx.smartnet.cz (mx.smartnet.cz [193.86.76.44])
	by postgresql.org (Postfix) with ESMTP id AC87A47589D
	for <pgsql-sql@postgresql.org>; Fri, 12 Jul 2002 07:52:34 -0400 (EDT)
Received: from kuba (helo=localhost)
	by mx.smartnet.cz with local-esmtp (Exim 3.35 #1 (Debian))
	id 17SyyT-0002H5-00
	for <pgsql-sql@postgresql.org>; Fri, 12 Jul 2002 13:52:37 +0200
Date: Fri, 12 Jul 2002 13:52:37 +0200 (CEST)
From: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
X-X-Sender: kuba@server
To: pgsql-sql@postgresql.org
Subject: Re: [HACKERS] please help on query
Message-ID: <Pine.LNX.4.44.0207121351390.8739-100000@server>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200207/179
X-Sequence-Number: 8558

hi,

avoid subselect: create a temp table and use join...

CREATE TEMP TABLE tmp AS
   SELECT
    lineitem.orderkey
   FROM
    lineitem
   WHERE
    lineitem.orderkey=orders.orderkey
   GROUP BY
    lineitem.orderkey HAVING
    sum(lineitem.quantity)>300;

CREATE INDEX tmp_idx ON tmp (orderkey);

 SELECT
  customer.name,
  customer.custkey,
  orders.orderkey,
  orders.orderdate,
  orders.totalprice,
  sum(lineitem.quantity)
 FROM
  customer,
  orders,
  lineitem,
  tmp
 WHERE
  orders.orderkey=tmp.orderkey
  AND customer.custkey=orders.custkey
  AND orders.orderkey=lineitem.orderkey
 GROUP BY
  customer.name,
  customer.custkey,
  orders.orderkey,
  orders.orderdate,
  orders.totalprice
 ORDER BY
  orders.totalprice DESC,
  orders.orderdate;


may be the index is not necessary...

kuba


> I'm going to keep on asking about another query:
>
> SELECT
>  customer.name,
>  customer.custkey,
>  orders.orderkey,
>  orders.orderdate,
>  orders.totalprice,
>  sum(lineitem.quantity)
> FROM
>  customer,
>  orders,
>  lineitem
> WHERE
>  exists(
>   SELECT
>    lineitem.orderkey
>   FROM
>    lineitem
>   WHERE
>    lineitem.orderkey=orders.orderkey
>   GROUP BY
>    lineitem.orderkey HAVING
>    sum(lineitem.quantity)>300
>   )
>  AND customer.custkey=orders.custkey
>  AND orders.orderkey=lineitem.orderkey
> GROUP BY
>  customer.name,
>  customer.custkey,
>  orders.orderkey,
>  orders.orderdate,
>  orders.totalprice
>
> ORDER BY
>  orders.totalprice DESC,
>  orders.orderdate;
>
> NOTICE:  QUERY PLAN:
>
> Sort  (cost=26923941.97..26923941.97 rows=300061 width=66)
>   ->  Aggregate  (cost=26851634.86..26896644.05 rows=300061 width=66)
>         ->  Group  (cost=26851634.86..26889142.52 rows=3000612 width=66)
>               ->  Sort  (cost=26851634.86..26851634.86 rows=3000612
> width=66)
>                     ->  Hash Join  (cost=26107574.81..26457309.10
> rows=3000612 width=66)
>                           ->  Seq Scan on lineitem  (cost=0.00..222208.25
> rows=6001225 width=8)
>                           ->  Hash  (cost=26105699.81..26105699.81
> rows=750000 width=58)
>                                 ->  Hash Join  (cost=7431.00..26105699.81
> rows=750000 width=58)
>                                       ->  Seq Scan on orders
> (cost=0.00..26083268.81 rows=750000 width=25)
>                                             SubPlan
>                                               ->  Aggregate
> (cost=0.00..17.35 rows=1 width=8)
>                                                     ->  Group
> (cost=0.00..17.34 rows=5 width=8)
>                                                           ->  Index Scan
> using lineitem_pkey on lineitem  (cost=0.00..17.33 rows=5 width=8)
>                                       ->  Hash  (cost=7056.00..7056.00
> rows=150000 width=33)
>                                             ->  Seq Scan on customer
> (cost=0.00..7056.00 rows=150000 width=33)
>
> again:
> orders 1500000 tuples
> lineitem 6000000 tuples there are 1 to 7 lineitems per orderkey
> Customer 150000 tuples
>
> select attname,n_distinct,correlation from pg_stats where
> tablename='lineitem';
>     attname    | n_distinct | correlation
> ---------------+------------+-------------
>  orderkey      |  -0.199847 |           1
>  partkey       |     196448 |   0.0223377
>  suppkey       |       9658 | -0.00822751
>  linenumber    |          7 |     0.17274
>  quantity      |         50 |   0.0150153
>  extendedprice |      25651 | -0.00790245
>  discount      |         11 |    0.103761
>  tax           |          9 |   0.0993771
>  returnflag    |          3 |    0.391434
>  linestatus    |          2 |    0.509791
>  shipdate      |       2440 |   0.0072777
>  commitdate    |       2497 |  0.00698162
>  receiptdate   |       2416 |  0.00726686
>  shipinstruct  |          4 |    0.241511
>  shipmode      |          7 |    0.138432
>  comment       |     275488 |   0.0188006
> (16 rows)
>
> select attname,n_distinct,correlation from pg_stats where
> tablename='orders';
>     attname    | n_distinct | correlation
> ---------------+------------+-------------
>  orderkey      |         -1 |   -0.999925
>  custkey       |      76309 |  0.00590596
>  orderstatus   |          3 |    0.451991
>  totalprice    |         -1 | -0.00768806
>  orderdate     |       2431 |  -0.0211354
>  orderpriority |          5 |    0.182489
>  clerk         |       1009 |  0.00546939
>  shippriority  |          1 |           1
>  comment       |  -0.750125 |  -0.0123887
>
> Customer
>   attname   | n_distinct | correlation
> ------------+------------+-------------
>  custkey    |         -1 |           1
>  name       |         -1 |           1
>  address    |         -1 | -0.00510274
>  nationkey  |         25 |   0.0170533
>  phone      |         -1 |  -0.0227816
>  acctbal    |   -0.83444 | -0.00220958
>  mktsegment |          5 |    0.205013
>  comment    |         -1 |   0.0327827
>
> This query takes 12 minutes to run and returns about 50 customers.
> lineitem.quantity takes values from 1 to 50, so 300 per orderkey is very
> restrictive
>
> May someone help on improving performance?
> Again thanks in advance
> Regards
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>





From lamigo@atc.unican.es Fri Jul  12 15:28:08 2002
Received: from bonito.atc.unican.es (bonito.atc.unican.es [193.144.185.218])
	by postgresql.org (Postfix) with ESMTP id 6C7FB475D64
	for <pgsql-sql@postgresql.org>; Fri, 12 Jul 2002 11:28:08 -0400 (EDT)
Received: from ALIGOTE (aligote [193.144.185.202])
	by bonito.atc.unican.es (8.11.0/8.8.7) with SMTP id g6CFSBb13876;
	Fri, 12 Jul 2002 17:28:11 +0200
Message-ID: <019201c229b9$61acfa50$cab990c1@atc.unican.es>
From: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
To: "Jakub Ouhrabka" <jakub.ouhrabka@comgate.cz>
Cc: <pgsql-sql@postgresql.org>
References: <Pine.LNX.4.44.0207121343260.8671-100000@server>
Subject: Re: [HACKERS] please help on query
Date: Fri, 12 Jul 2002 17:32:50 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
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/189
X-Sequence-Number: 8568

Lineitem is being modified on run time, so creating a temp table don't
solves my problem
The time of creating this table is the same of performing the subselect (or
so I think), it could be done creating a new table, and a new trigger, but
there are already triggers to calculate
lineitem.extendedprice=part.retailprice*lineitem.quantity*(1+taxes)*(1-disco
unt) and to calculate orderstatus in order with linestatus and to calculate
orders.totalprice as sum(extendedprice) where
lineitem.orderkey=new.orderkey. A new trigger in order to insert orderkey if
sum(quantity) where orderkey=new.orderkey might be excessive.
Any other idea?
Thanks And Regards

----- Original Message -----
From: "Jakub Ouhrabka" <jakub.ouhrabka@comgate.cz>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: "Manfred Koizar" <mkoi-pg@aon.at>; <pgsql-sql@postgresql.org>
Sent: Friday, July 12, 2002 1:50 PM
Subject: Re: [SQL] [HACKERS] please help on query


> hi,
>
> avoid subselect: create a temp table and use join...
>
> CREATE TEMP TABLE tmp AS
>    SELECT
>     lineitem.orderkey
>    FROM
>     lineitem
>    WHERE
>     lineitem.orderkey=orders.orderkey
>    GROUP BY
>     lineitem.orderkey HAVING
>     sum(lineitem.quantity)>300;
>
> CREATE INDEX tmp_idx ON tmp (orderkey);
>
>  SELECT
>   customer.name,
>   customer.custkey,
>   orders.orderkey,
>   orders.orderdate,
>   orders.totalprice,
>   sum(lineitem.quantity)
>  FROM
>   customer,
>   orders,
>   lineitem,
>   tmp
>  WHERE
>   orders.orderkey=tmp.orderkey
>   AND customer.custkey=orders.custkey
>   AND orders.orderkey=lineitem.orderkey
>  GROUP BY
>   customer.name,
>   customer.custkey,
>   orders.orderkey,
>   orders.orderdate,
>   orders.totalprice
>  ORDER BY
>   orders.totalprice DESC,
>   orders.orderdate;
>
>
> may be the index is not necessary...
>
> kuba
>
>
> > I'm going to keep on asking about another query:
> >
> > SELECT
> >  customer.name,
> >  customer.custkey,
> >  orders.orderkey,
> >  orders.orderdate,
> >  orders.totalprice,
> >  sum(lineitem.quantity)
> > FROM
> >  customer,
> >  orders,
> >  lineitem
> > WHERE
> >  exists(
> >   SELECT
> >    lineitem.orderkey
> >   FROM
> >    lineitem
> >   WHERE
> >    lineitem.orderkey=orders.orderkey
> >   GROUP BY
> >    lineitem.orderkey HAVING
> >    sum(lineitem.quantity)>300
> >   )
> >  AND customer.custkey=orders.custkey
> >  AND orders.orderkey=lineitem.orderkey
> > GROUP BY
> >  customer.name,
> >  customer.custkey,
> >  orders.orderkey,
> >  orders.orderdate,
> >  orders.totalprice
> >
> > ORDER BY
> >  orders.totalprice DESC,
> >  orders.orderdate;
> >
> > NOTICE:  QUERY PLAN:
> >
> > Sort  (cost=26923941.97..26923941.97 rows=300061 width=66)
> >   ->  Aggregate  (cost=26851634.86..26896644.05 rows=300061 width=66)
> >         ->  Group  (cost=26851634.86..26889142.52 rows=3000612 width=66)
> >               ->  Sort  (cost=26851634.86..26851634.86 rows=3000612
> > width=66)
> >                     ->  Hash Join  (cost=26107574.81..26457309.10
> > rows=3000612 width=66)
> >                           ->  Seq Scan on lineitem
(cost=0.00..222208.25
> > rows=6001225 width=8)
> >                           ->  Hash  (cost=26105699.81..26105699.81
> > rows=750000 width=58)
> >                                 ->  Hash Join
(cost=7431.00..26105699.81
> > rows=750000 width=58)
> >                                       ->  Seq Scan on orders
> > (cost=0.00..26083268.81 rows=750000 width=25)
> >                                             SubPlan
> >                                               ->  Aggregate
> > (cost=0.00..17.35 rows=1 width=8)
> >                                                     ->  Group
> > (cost=0.00..17.34 rows=5 width=8)
> >                                                           ->  Index Scan
> > using lineitem_pkey on lineitem  (cost=0.00..17.33 rows=5 width=8)
> >                                       ->  Hash  (cost=7056.00..7056.00
> > rows=150000 width=33)
> >                                             ->  Seq Scan on customer
> > (cost=0.00..7056.00 rows=150000 width=33)
> >
> > again:
> > orders 1500000 tuples
> > lineitem 6000000 tuples there are 1 to 7 lineitems per orderkey
> > Customer 150000 tuples
> >
> > select attname,n_distinct,correlation from pg_stats where
> > tablename='lineitem';
> >     attname    | n_distinct | correlation
> > ---------------+------------+-------------
> >  orderkey      |  -0.199847 |           1
> >  partkey       |     196448 |   0.0223377
> >  suppkey       |       9658 | -0.00822751
> >  linenumber    |          7 |     0.17274
> >  quantity      |         50 |   0.0150153
> >  extendedprice |      25651 | -0.00790245
> >  discount      |         11 |    0.103761
> >  tax           |          9 |   0.0993771
> >  returnflag    |          3 |    0.391434
> >  linestatus    |          2 |    0.509791
> >  shipdate      |       2440 |   0.0072777
> >  commitdate    |       2497 |  0.00698162
> >  receiptdate   |       2416 |  0.00726686
> >  shipinstruct  |          4 |    0.241511
> >  shipmode      |          7 |    0.138432
> >  comment       |     275488 |   0.0188006
> > (16 rows)
> >
> > select attname,n_distinct,correlation from pg_stats where
> > tablename='orders';
> >     attname    | n_distinct | correlation
> > ---------------+------------+-------------
> >  orderkey      |         -1 |   -0.999925
> >  custkey       |      76309 |  0.00590596
> >  orderstatus   |          3 |    0.451991
> >  totalprice    |         -1 | -0.00768806
> >  orderdate     |       2431 |  -0.0211354
> >  orderpriority |          5 |    0.182489
> >  clerk         |       1009 |  0.00546939
> >  shippriority  |          1 |           1
> >  comment       |  -0.750125 |  -0.0123887
> >
> > Customer
> >   attname   | n_distinct | correlation
> > ------------+------------+-------------
> >  custkey    |         -1 |           1
> >  name       |         -1 |           1
> >  address    |         -1 | -0.00510274
> >  nationkey  |         25 |   0.0170533
> >  phone      |         -1 |  -0.0227816
> >  acctbal    |   -0.83444 | -0.00220958
> >  mktsegment |          5 |    0.205013
> >  comment    |         -1 |   0.0327827
> >
> > This query takes 12 minutes to run and returns about 50 customers.
> > lineitem.quantity takes values from 1 to 50, so 300 per orderkey is very
> > restrictive
> >
> > May someone help on improving performance?
> > Again thanks in advance
> > Regards
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>





From rk73@sea.plala.or.jp Sun Jul  14 12:22:43 2002
Received: from mps2.plala.or.jp (mpsb-nat02.plala.or.jp [202.212.114.145])
	by postgresql.org (Postfix) with ESMTP id 76877475473
	for <pgsql-sql@postgresql.org>; Sun, 14 Jul 2002 08:22:43 -0400 (EDT)
Received: from [192.168.0.4] ([218.47.117.177]) by mps2.plala.or.jp with ESMTP
	id <20020714122245.NYAI8255.mps2.plala.or.jp@[192.168.0.4]>;
	Sun, 14 Jul 2002 21:22:45 +0900
Date: Sun, 14 Jul 2002 21:23:28 +0900
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Subject: Re: [HACKERS] please help on query
Cc: pgsql-sql@postgresql.org
In-Reply-To: <019201c229b9$61acfa50$cab990c1@atc.unican.es>
References: <Pine.LNX.4.44.0207121343260.8671-100000@server>
	<019201c229b9$61acfa50$cab990c1@atc.unican.es>
Errors-To: rk73@sea.plala.or.jp
Message-Id: <20020714211624.9C3E.RK73@sea.plala.or.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Mailer: Becky! ver. 2.05.03
X-Archive-Number: 200207/204
X-Sequence-Number: 8583

On Fri, 12 Jul 2002 17:32:50 +0200
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es> wrote:


> Lineitem is being modified on run time, so creating a temp table don't
> solves my problem
> The time of creating this table is the same of performing the subselect (or
> so I think), it could be done creating a new table, and a new trigger, but
> there are already triggers to calculate
> lineitem.extendedprice=part.retailprice*lineitem.quantity*(1+taxes)*(1-disco
> unt) and to calculate orderstatus in order with linestatus and to calculate
> orders.totalprice as sum(extendedprice) where
> lineitem.orderkey=new.orderkey. A new trigger in order to insert orderkey if
> sum(quantity) where orderkey=new.orderkey might be excessive.
> Any other idea?
> Thanks And Regards
> 
> ----- Original Message -----
> From: "Jakub Ouhrabka" <jakub.ouhrabka@comgate.cz>
> To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
> Cc: "Manfred Koizar" <mkoi-pg@aon.at>; <pgsql-sql@postgresql.org>
> Sent: Friday, July 12, 2002 1:50 PM
> Subject: Re: [SQL] [HACKERS] please help on query
> 
> >
> > avoid subselect: create a temp table and use join...
> >
> > CREATE TEMP TABLE tmp AS
> >    SELECT
> >     lineitem.orderkey
> >    FROM
> >     lineitem
> >    WHERE
> >     lineitem.orderkey=orders.orderkey
> >    GROUP BY
> >     lineitem.orderkey HAVING
> >     sum(lineitem.quantity)>300;


Hi,

I'm not sure whether its performance can be improved or not.  But I feel
there is a slight chance to reduce the total number of the tuples which 
Planner must think.

BTW, how much time does the following query take in your situation, 
and how many rows does it retrieve ?


EXPLAIN ANALYZE
SELECT
        lineitem.orderkey
    FROM
        lineitem
    GROUP BY
        lineitem.orderkey
    HAVING
        SUM(lineitem.quantity) > 300;



Regards,
Masaru Sugawara





From lamigo@atc.unican.es Mon Jul  15 07:40:54 2002
Received: from bonito.atc.unican.es (bonito.atc.unican.es [193.144.185.218])
	by postgresql.org (Postfix) with ESMTP id 3A75647545D
	for <pgsql-sql@postgresql.org>; Mon, 15 Jul 2002 03:40:54 -0400 (EDT)
Received: from ALIGOTE (aligote [193.144.185.202])
	by bonito.atc.unican.es (8.11.0/8.8.7) with SMTP id g6F7enb29850;
	Mon, 15 Jul 2002 09:40:49 +0200
Message-ID: <005101c22bd3$9ba152d0$cab990c1@atc.unican.es>
From: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
To: "Masaru Sugawara" <rk73@sea.plala.or.jp>
Cc: <pgsql-sql@postgresql.org>
References: <Pine.LNX.4.44.0207121343260.8671-100000@server>
	<019201c229b9$61acfa50$cab990c1@atc.unican.es>
	<20020714211624.9C3E.RK73@sea.plala.or.jp>
Subject: Re: [HACKERS] please help on query
Date: Mon, 15 Jul 2002 09:45:36 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
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/210
X-Sequence-Number: 8589


----- Original Message -----
From: "Masaru Sugawara" <rk73@sea.plala.or.jp>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: <pgsql-sql@postgresql.org>
Sent: Sunday, July 14, 2002 2:23 PM
Subject: Re: [SQL] [HACKERS] please help on query


This is the output:

Aggregate  (cost=0.00..647161.10 rows=600122 width=8) (actual
time=4959.19..347328.83 rows=62 loops=1)
  ->  Group  (cost=0.00..632158.04 rows=6001225 width=8) (actual
time=10.79..274259.16 rows=6001225 loops=1)
        ->  Index Scan using lineitem_pkey on lineitem
(cost=0.00..617154.97 rows=6001225 width=8) (actual time=10.77..162439.11
rows=6001225 loops=1)
Total runtime: 347330.28 msec

it is returning all rows in lineitem. Why is it using index?
Thanks and regards


> On Fri, 12 Jul 2002 17:32:50 +0200
> "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es> wrote:
>
>
> > Lineitem is being modified on run time, so creating a temp table don't
> > solves my problem
> > The time of creating this table is the same of performing the subselect
(or
> > so I think), it could be done creating a new table, and a new trigger,
but
> > there are already triggers to calculate
> >
lineitem.extendedprice=part.retailprice*lineitem.quantity*(1+taxes)*(1-disco
> > unt) and to calculate orderstatus in order with linestatus and to
calculate
> > orders.totalprice as sum(extendedprice) where
> > lineitem.orderkey=new.orderkey. A new trigger in order to insert
orderkey if
> > sum(quantity) where orderkey=new.orderkey might be excessive.
> > Any other idea?
> > Thanks And Regards
> >
> > ----- Original Message -----
> > From: "Jakub Ouhrabka" <jakub.ouhrabka@comgate.cz>
> > To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
> > Cc: "Manfred Koizar" <mkoi-pg@aon.at>; <pgsql-sql@postgresql.org>
> > Sent: Friday, July 12, 2002 1:50 PM
> > Subject: Re: [SQL] [HACKERS] please help on query
> >
> > >
> > > avoid subselect: create a temp table and use join...
> > >
> > > CREATE TEMP TABLE tmp AS
> > >    SELECT
> > >     lineitem.orderkey
> > >    FROM
> > >     lineitem
> > >    WHERE
> > >     lineitem.orderkey=orders.orderkey
> > >    GROUP BY
> > >     lineitem.orderkey HAVING
> > >     sum(lineitem.quantity)>300;
>
>
> Hi,
>
> I'm not sure whether its performance can be improved or not.  But I feel
> there is a slight chance to reduce the total number of the tuples which
> Planner must think.
>
> BTW, how much time does the following query take in your situation,
> and how many rows does it retrieve ?
>
>
> EXPLAIN ANALYZE
> SELECT
>         lineitem.orderkey
>     FROM
>         lineitem
>     GROUP BY
>         lineitem.orderkey
>     HAVING
>         SUM(lineitem.quantity) > 300;
>
>
>
> Regards,
> Masaru Sugawara
>
>
>





From rk73@sea.plala.or.jp Mon Jul  15 16:14:47 2002
Received: from mps2.plala.or.jp (mpsb-nat02.plala.or.jp [202.212.114.145])
	by postgresql.org (Postfix) with ESMTP id 88286475481
	for <pgsql-sql@postgresql.org>; Mon, 15 Jul 2002 12:14:47 -0400 (EDT)
Received: from [192.168.0.4] ([218.47.116.68]) by mps2.plala.or.jp with ESMTP
	id <20020715161451.IWNF8255.mps2.plala.or.jp@[192.168.0.4]>;
	Tue, 16 Jul 2002 01:14:51 +0900
Date: Tue, 16 Jul 2002 01:15:35 +0900
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Subject: Re: [HACKERS] please help on query
Cc: pgsql-sql@postgresql.org
In-Reply-To: <005101c22bd3$9ba152d0$cab990c1@atc.unican.es>
References: <20020714211624.9C3E.RK73@sea.plala.or.jp>
	<005101c22bd3$9ba152d0$cab990c1@atc.unican.es>
Errors-To: rk73@sea.plala.or.jp
Message-Id: <20020716011524.3F70.RK73@sea.plala.or.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Mailer: Becky! ver. 2.05.03
X-Archive-Number: 200207/217
X-Sequence-Number: 8595

On Mon, 15 Jul 2002 09:45:36 +0200
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es> wrote:

> This is the output:
> 
> Aggregate  (cost=0.00..647161.10 rows=600122 width=8) (actual
> time=4959.19..347328.83 rows=62 loops=1)
>   ->  Group  (cost=0.00..632158.04 rows=6001225 width=8) (actual
> time=10.79..274259.16 rows=6001225 loops=1)
>         ->  Index Scan using lineitem_pkey on lineitem
> (cost=0.00..617154.97 rows=6001225 width=8) (actual time=10.77..162439.11
> rows=6001225 loops=1)
> Total runtime: 347330.28 msec
> 
> it is returning all rows in lineitem. Why is it using index?


Sorry, I don't know the reason. 
I need more info. Can you show me the outputs of EXPLAIN ANALYZE ?


EXPLAIN ANALYZE
SELECT
        orders.orderkey
    FROM
        lineitem LEFT OUTER JOIN
        orders USING(orderkey)
    WHERE
        orders.orderkey IS NOT NULL
    GROUP BY
        orders.orderkey
    HAVING
        SUM(lineitem.quantity) > 300;



EXPLAIN ANALYZE
SELECT
      t2.*
FROM (SELECT
                  orders.orderkey
              FROM
                   lineitem LEFT OUTER JOIN
                   orders USING(orderkey)
             WHERE
                   orders.orderkey IS NOT NULL
            GROUP BY
                   orders.orderkey
            HAVING
                    SUM(lineitem.quantity) > 300
           ) AS t1 LEFT OUTER JOIN
           orders AS t2 USING(orderkey)
ORDER BY t2.custkey           


Regards,
Masaru Sugawara





From lamigo@atc.unican.es Tue Jul  16 08:46:16 2002
Received: from bonito.atc.unican.es (bonito.atc.unican.es [193.144.185.218])
	by postgresql.org (Postfix) with ESMTP id 80B40475425
	for <pgsql-sql@postgresql.org>; Tue, 16 Jul 2002 04:46:16 -0400 (EDT)
Received: from ALIGOTE (aligote [193.144.185.202])
	by bonito.atc.unican.es (8.11.0/8.8.7) with SMTP id g6G8k8b03000;
	Tue, 16 Jul 2002 10:46:08 +0200
Message-ID: <00fc01c22ca5$ea5c78f0$cab990c1@atc.unican.es>
From: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
To: "Masaru Sugawara" <rk73@sea.plala.or.jp>
Cc: <pgsql-sql@postgresql.org>
References: <20020714211624.9C3E.RK73@sea.plala.or.jp>
	<005101c22bd3$9ba152d0$cab990c1@atc.unican.es>
	<20020716011524.3F70.RK73@sea.plala.or.jp>
Subject: Re: [HACKERS] please help on query
Date: Tue, 16 Jul 2002 10:51:03 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
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/234
X-Sequence-Number: 8612


----- Original Message -----
From: "Masaru Sugawara" <rk73@sea.plala.or.jp>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, July 15, 2002 6:15 PM
Subject: Re: [SQL] [HACKERS] please help on query



>
> Sorry, I don't know the reason.
> I need more info. Can you show me the outputs of EXPLAIN ANALYZE ?
>
Here it is:


>
> EXPLAIN ANALYZE
> SELECT
>         orders.orderkey
>     FROM
>         lineitem LEFT OUTER JOIN
>         orders USING(orderkey)
>     WHERE
>         orders.orderkey IS NOT NULL
>     GROUP BY
>         orders.orderkey
>     HAVING
>         SUM(lineitem.quantity) > 300;
>
Aggregate  (cost=1257368.92..1287375.04 rows=600122 width=12) (actual
time=1236941.71..1454824.56 rows=62 loops=1)
  ->  Group  (cost=1257368.92..1272371.98 rows=6001225 width=12) (actual
time=1233968.87..1385034.91 rows=6001225 loops=1)
        ->  Sort  (cost=1257368.92..1257368.92 rows=6001225 width=12)
(actual time=1233968.82..1276147.37 rows=6001225 loops=1)
              ->  Hash Join  (cost=166395.00..520604.08 rows=6001225
width=12) (actual time=59061.21..773997.08 rows=6001225 loops=1)
                    ->  Seq Scan on lineitem  (cost=0.00..195405.25
rows=6001225 width=8) (actual time=20.66..115511.34 rows=6001225 loops=1)
                    ->  Hash  (cost=162645.00..162645.00 rows=1500000
width=4) (actual time=59032.16..59032.16 rows=0 loops=1)
                          ->  Seq Scan on orders  (cost=0.00..162645.00
rows=1500000 width=4) (actual time=17.33..44420.10 rows=1500000 loops=1)
Total runtime: 1454929.11 msec




>
>
> EXPLAIN ANALYZE
> SELECT
>       t2.*
> FROM (SELECT
>                   orders.orderkey
>               FROM
>                    lineitem LEFT OUTER JOIN
>                    orders USING(orderkey)
>              WHERE
>                    orders.orderkey IS NOT NULL
>             GROUP BY
>                    orders.orderkey
>             HAVING
>                     SUM(lineitem.quantity) > 300
>            ) AS t1 LEFT OUTER JOIN
>            orders AS t2 USING(orderkey)
> ORDER BY t2.custkey
>

Sort  (cost=1739666.43..1739666.43 rows=600122 width=119) (actual
time=1538897.23..1538897.47 rows=62 loops=1)
  ->  Merge Join  (cost=1344971.49..1682069.98 rows=600122 width=119)
(actual time=1440886.58..1538886.03 rows=62 loops=1)
        ->  Index Scan using orders_pkey on orders t2  (cost=0.00..324346.65
rows=1500000 width=115) (actual time=32.80..87906.98 rows=1455276 loops=1)
        ->  Sort  (cost=1344971.49..1344971.49 rows=600122 width=12) (actual
time=1439550.31..1439550.73 rows=62 loops=1)
              ->  Subquery Scan t1  (cost=1257368.92..1287375.04 rows=600122
width=12) (actual time=1222560.86..1439549.36 rows=62 loops=1)
                    ->  Aggregate  (cost=1257368.92..1287375.04 rows=600122
width=12) (actual time=1222560.84..1439548.42 rows=62 loops=1)
                          ->  Group  (cost=1257368.92..1272371.98
rows=6001225 width=12) (actual time=1219607.04..1369327.42 rows=6001225
loops=1)
                                ->  Sort  (cost=1257368.92..1257368.92
rows=6001225 width=12) (actual time=1219607.00..1261208.08 rows=6001225
loops=1)
                                      ->  Hash Join
(cost=166395.00..520604.08 rows=6001225 width=12) (actual
time=65973.31..769253.41 rows=6001225 loops=1)
                                            ->  Seq Scan on lineitem
(cost=0.00..195405.25 rows=6001225 width=8) (actual time=20.07..115247.61
rows=6001225 loops=1)
                                            ->  Hash
(cost=162645.00..162645.00 rows=1500000 width=4) (actual
time=65943.80..65943.80 rows=0 loops=1)
                                                  ->  Seq Scan on orders
(cost=0.00..162645.00 rows=1500000 width=4) (actual time=39.04..52049.90
rows=1500000 loops=1)
Total runtime: 1539010.00 msec



Thanks and regards





From rk73@sea.plala.or.jp Wed Jul  17 13:27:51 2002
Received: from mps4.plala.or.jp (mpsb-nat02.plala.or.jp [202.212.114.145])
	by postgresql.org (Postfix) with ESMTP id 7F78647585D
	for <pgsql-sql@postgresql.org>; Wed, 17 Jul 2002 09:27:51 -0400 (EDT)
Received: from [192.168.0.4] ([218.47.225.244]) by mps4.plala.or.jp with ESMTP
	id <20020717132753.STY18760.mps4.plala.or.jp@[192.168.0.4]>;
	Wed, 17 Jul 2002 22:27:53 +0900
Date: Wed, 17 Jul 2002 22:28:37 +0900
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Subject: Re: [HACKERS] please help on query
Cc: pgsql-sql@postgresql.org
In-Reply-To: <00fc01c22ca5$ea5c78f0$cab990c1@atc.unican.es>
References: <20020716011524.3F70.RK73@sea.plala.or.jp>
	<00fc01c22ca5$ea5c78f0$cab990c1@atc.unican.es>
Errors-To: rk73@sea.plala.or.jp
Message-Id: <20020717215801.79DF.RK73@sea.plala.or.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Mailer: Becky! ver. 2.05.03
X-Archive-Number: 200207/266
X-Sequence-Number: 8644

On Tue, 16 Jul 2002 10:51:03 +0200
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es> wrote:


> Aggregate  (cost=1257368.92..1287375.04 rows=600122 width=12) (actual
> time=1236941.71..1454824.56 rows=62 loops=1)
>   ->  Group  (cost=1257368.92..1272371.98 rows=6001225 width=12) (actual
> time=1233968.87..1385034.91 rows=6001225 loops=1)
>         ->  Sort  (cost=1257368.92..1257368.92 rows=6001225 width=12)
> (actual time=1233968.82..1276147.37 rows=6001225 loops=1)
>               ->  Hash Join  (cost=166395.00..520604.08 rows=6001225
> width=12) (actual time=59061.21..773997.08 rows=6001225 loops=1)
>                     ->  Seq Scan on lineitem  (cost=0.00..195405.25
> rows=6001225 width=8) (actual time=20.66..115511.34 rows=6001225 loops=1)
>                     ->  Hash  (cost=162645.00..162645.00 rows=1500000
> width=4) (actual time=59032.16..59032.16 rows=0 loops=1)
>                           ->  Seq Scan on orders  (cost=0.00..162645.00
> rows=1500000 width=4) (actual time=17.33..44420.10 rows=1500000 loops=1)
> Total runtime: 1454929.11 msec


Hmm, does each of the three tables have some indices like the following?
If not so, could you execute EXPLAIN ANALYZE after creating the indices.


create index idx_lineitem_orderkey on lineitem(orderkey);
create index idx_orders_orderkey on orders(orderkey);
create index idx_orders_custkey on orders(custkey);
create index idx_customer_custkey on customer(custkey);


Regards,
Masaru Sugawara