Re: [HACKERS] please help on query

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.orderkeyIS 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