Received: from mps2.plala.or.jp (mpsb-nat02.plala.or.jp [202.212.114.145])
	by postgresql.org (Postfix) with ESMTP id 88286475481
	for <pgsql-sql@postgresql.org>; Mon, 15 Jul 2002 12:14:47 -0400 (EDT)
Received: from [192.168.0.4] ([218.47.116.68]) by mps2.plala.or.jp with ESMTP
	id <20020715161451.IWNF8255.mps2.plala.or.jp@[192.168.0.4]>;
	Tue, 16 Jul 2002 01:14:51 +0900
Date: Tue, 16 Jul 2002 01:15:35 +0900
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Subject: Re: [HACKERS] please help on query
Cc: pgsql-sql@postgresql.org
In-Reply-To: <005101c22bd3$9ba152d0$cab990c1@atc.unican.es>
References: <20020714211624.9C3E.RK73@sea.plala.or.jp>
	<005101c22bd3$9ba152d0$cab990c1@atc.unican.es>
Errors-To: rk73@sea.plala.or.jp
Message-Id: <20020716011524.3F70.RK73@sea.plala.or.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Mailer: Becky! ver. 2.05.03
X-Archive-Number: 200207/217
X-Sequence-Number: 8595

On Mon, 15 Jul 2002 09:45:36 +0200
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es> wrote:

> 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?


Sorry, I don't know the reason. 
I need more info. Can you show me the outputs of EXPLAIN ANALYZE ?


EXPLAIN ANALYZE
SELECT
        orders.orderkey
    FROM
        lineitem LEFT OUTER JOIN
        orders USING(orderkey)
    WHERE
        orders.orderkey IS NOT NULL
    GROUP BY
        orders.orderkey
    HAVING
        SUM(lineitem.quantity) > 300;



EXPLAIN ANALYZE
SELECT
      t2.*
FROM (SELECT
                  orders.orderkey
              FROM
                   lineitem LEFT OUTER JOIN
                   orders USING(orderkey)
             WHERE
                   orders.orderkey IS NOT NULL
            GROUP BY
                   orders.orderkey
            HAVING
                    SUM(lineitem.quantity) > 300
           ) AS t1 LEFT OUTER JOIN
           orders AS t2 USING(orderkey)
ORDER BY t2.custkey           


Regards,
Masaru Sugawara