From: Luis Alberto Amigo Navarro <lamigo@atc.unican.es>
To: Masaru Sugawara <rk73@sea.plala.or.jp>
Cc: pgsql-sql@postgresql.org
Subject: Re: [HACKERS] please help on query
Date: Mon, 15 Jul 2002 09:45:36 +0200
Message-ID: <005101c22bd3$9ba152d0$cab990c1@atc.unican.es> (raw)
References: <Pine.LNX.4.44.0207121343260.8671-100000@server>
<019201c229b9$61acfa50$cab990c1@atc.unican.es>
<20020714211624.9C3E.RK73@sea.plala.or.jp>
----- 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
>
>
>
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: lamigo@atc.unican.es, rk73@sea.plala.or.jp
Subject: Re: [HACKERS] please help on query
In-Reply-To: <005101c22bd3$9ba152d0$cab990c1@atc.unican.es>
* 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