pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Luis Alberto Amigo Navarro <lamigo@atc.unican.es>
To: Manfred Koizar <mkoi-pg@aon.at>
Cc: pgsql-sql@postgresql.org
Subject: Re: [HACKERS] please help on query
Date: Thu, 11 Jul 2002 19:40:46 +0200
Message-ID: <007c01c22902$16e699c0$cab990c1@atc.unican.es> (raw)
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
	<gu9riu8qp28ifp40qsva7b9puif47ihi5b@4ax.com>

I've tried
SELECT
 supplier.name,
 supplier.address
FROM
 supplier,
 nation,
 lineitem
WHERE
 EXISTS(
  SELECT
   partsupp.suppkey
  FROM
   partsupp,lineitem
  WHERE
   lineitem.partkey=partsupp.partkey
   AND lineitem.suppkey=partsupp.partkey
   AND lineitem.shipdate>=('1994-01-01')::DATE
   AND lineitem.shipdate<(('1994-01-01')::DATE+('1 year')::INTERVAL)::DATE
   AND EXISTS(
    SELECT
     part.partkey
    FROM
     part
    WHERE
     part.name like 'forest%'
     )
  GROUP BY partsupp.partkey,partsupp.suppkey
  HAVING min(availqty)>(0.5*(sum(lineitem.quantity)::FLOAT))
  )
 AND supplier.nationkey=nation.nationkey
 AND nation.name='CANADA'
ORDER BY
 supplier.name;

as you said and something is wrong
Sort  (cost=1141741215.35..1141741215.35 rows=2400490000 width=81)
  InitPlan
    ->  Aggregate  (cost=0.00..921773.85 rows=48 width=24)
          InitPlan
            ->  Seq Scan on part  (cost=0.00..8561.00 rows=1 width=4)
          ->  Group  (cost=0.00..921771.44 rows=481 width=24)
                ->  Result  (cost=0.00..921769.04 rows=481 width=24)
                      ->  Merge Join  (cost=0.00..921769.04 rows=481
width=24)
                            ->  Index Scan using partsupp_pkey on partsupp
(cost=0.00..98522.75 rows=800000 width=12)
                            ->  Index Scan using lsupp_index on lineitem
(cost=0.00..821239.91 rows=145 width=12)
  ->  Result  (cost=1.31..112888690.31 rows=2400490000 width=81)
        ->  Nested Loop  (cost=1.31..112888690.31 rows=2400490000 width=81)
              ->  Hash Join  (cost=1.31..490.31 rows=400 width=81)
                    ->  Seq Scan on supplier  (cost=0.00..434.00 rows=10000
width=77)
                    ->  Hash  (cost=1.31..1.31 rows=1 width=4)
                          ->  Seq Scan on nation  (cost=0.00..1.31 rows=1
width=4)
              ->  Seq Scan on lineitem  (cost=0.00..222208.25 rows=6001225
width=0)

where might be my mistake
Thanks and regards
----- Original Message -----
From: "Manfred Koizar" <mkoi-pg@aon.at>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, July 11, 2002 6:47 PM
Subject: Re: [HACKERS] please help on query


> [moving to pgsql-sql]
> On Thu, 11 Jul 2002 17:22:14 +0200, "Luis Alberto Amigo Navarro"
> <lamigo@atc.unican.es> wrote:
> >I can't improve performance on this query:
> >
> >SELECT
> > supplier.name,
> > supplier.address
> >FROM
> > supplier,
> > nation
> >WHERE
> > supplier.suppkey IN(
> >  SELECT
> >   partsupp.suppkey
> >  FROM
> >   partsupp
> >  WHERE
> >   partsupp.partkey IN(
> >    SELECT
> >     part.partkey
> >    FROM
> >     part
> >    WHERE
> >     part.name like 'forest%'
> >     )
> >   AND partsupp.availqty>(
> >    SELECT
> >     0.5*(sum(lineitem.quantity)::FLOAT)
> >    FROM
> >     lineitem
> >    WHERE
> >     lineitem.partkey=partsupp.partkey
> >     AND lineitem.suppkey=partsupp.partkey
>                                     ^^^^^^^
>                                     suppkey ???
> >     AND lineitem.shipdate>=('1994-01-01')::DATE
> >     AND lineitem.shipdate<(('1994-01-01')::DATE+('1
year')::INTERVAL)::DATE
> >     )
> >  )
> > AND supplier.nationkey=nation.nationkey
> > AND nation.name='CANADA'
> >ORDER BY
> > supplier.name;
>

view thread (18+ messages)  latest in thread

Message-ID: <007c01c22902$16e699c0$cab990c1@atc.unican.es>
Permalink:  ../007c01c22902$16e699c0$cab990c1@atc.unican.es/
Also on:    postgresql.org/message-id/007c01c22902$16e699c0$cab990c1@atc.unican.es

copy link · copy postgr.es

reply

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, mkoi-pg@aon.at
  Subject: Re: [HACKERS] please help on query
  In-Reply-To: <007c01c22902$16e699c0$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