From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: Luis Alberto Amigo Navarro <lamigo@atc.unican.es>
Cc: pgsql-sql@postgresql.org
Subject: Re: [HACKERS] please help on query
Date: Sun, 14 Jul 2002 21:23:28 +0900
Message-ID: <20020714211624.9C3E.RK73@sea.plala.or.jp> (raw)
In-Reply-To: <019201c229b9$61acfa50$cab990c1@atc.unican.es>
References: <Pine.LNX.4.44.0207121343260.8671-100000@server>
<019201c229b9$61acfa50$cab990c1@atc.unican.es>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rk73@sea.plala.or.jp, lamigo@atc.unican.es
Subject: Re: [HACKERS] please help on query
In-Reply-To: <20020714211624.9C3E.RK73@sea.plala.or.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox