Re: [HACKERS] please help on query

On Fri, 12 Jul 2002 17:32:50 +0200
"Luis Alberto Amigo Navarro" <lamigo(at)atc(dot)unican(dot)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(dot)ouhrabka(at)comgate(dot)cz>
> To: "Luis Alberto Amigo Navarro" <lamigo(at)atc(dot)unican(dot)es>
> Cc: "Manfred Koizar" <mkoi-pg(at)aon(dot)at>; <pgsql-sql(at)postgresql(dot)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