Re: [HACKERS] please help on query

----- Original Message -----
From: "Masaru Sugawara" <rk73(at)sea(dot)plala(dot)or(dot)jp>
To: "Luis Alberto Amigo Navarro" <lamigo(at)atc(dot)unican(dot)es>
Cc: <pgsql-sql(at)postgresql(dot)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(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
>
>
>