Re: [HACKERS] please help on query - Mailing list pgsql-sql
From | Luis Alberto Amigo Navarro |
---|---|
Subject | Re: [HACKERS] please help on query |
Date | |
Msg-id | 005101c22bd3$9ba152d0$cab990c1@atc.unican.es Whole thread Raw |
In response to | Re: [HACKERS] please help on query (Jakub Ouhrabka <jakub.ouhrabka@comgate.cz>) |
Responses |
Re: [HACKERS] please help on query
(Masaru Sugawara <rk73@sea.plala.or.jp>)
|
List | pgsql-sql |
----- 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 > > >