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


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