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: pgsql-hackers@postgresql.org
Subject: please help on query
Date: Thu, 11 Jul 2002 17:22:14 +0200
Message-ID: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es> (raw)

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


explain results:
NOTICE:  QUERY PLAN:

Sort  (cost=2777810917708.17..2777810917708.17 rows=200 width=81)
  ->  Nested Loop  (cost=0.00..2777810917700.53 rows=200 width=81)
        ->  Seq Scan on nation  (cost=0.00..1.31 rows=1 width=4)
        ->  Index Scan using snation_index on supplier  (cost=0.00..2777810917696.72 rows=200 width=77)
              SubPlan
                ->  Materialize  (cost=6944527291.72..6944527291.72 rows=133333 width=4)
                      ->  Seq Scan on partsupp  (cost=0.00..6944527291.72 rows=133333 width=4)
                            SubPlan
                              ->  Materialize  (cost=8561.00..8561.00 rows=1 width=4)
                                    ->  Seq Scan on part  (cost=0.00..8561.00 rows=1 width=4)
                              ->  Aggregate  (cost=119.61..119.61 rows=1 width=4)
                                    ->  Index Scan using lineitem_index on lineitem  (cost=0.00..119.61 rows=1 width=4)

partsupp::800000 tuples
            Table "partsupp"
   Column   |      Type      | Modifiers 
------------+----------------+-----------
 partkey    | integer        | not null
 suppkey    | integer        | not null
 availqty   | integer        | 
 supplycost | numeric(10,2)  | 
 comment    | character(199) | 
Primary key: partsupp_pkey
Triggers: RI_ConstraintTrigger_16597,
          RI_ConstraintTrigger_16603

tpch=# select attname,n_distinct,correlation from pg_stats where tablename='partsupp';
  attname   | n_distinct | correlation 
------------+------------+-------------
 partkey    |  -0.195588 |           1
 suppkey    |       9910 |  0.00868363
 availqty   |       9435 | -0.00788662
 supplycost |  -0.127722 |  -0.0116864
 comment    |         -1 |   0.0170702

I accept query changes, reordering, indexes ideas and horizontal partitioning
thanks in advance.
Regards

view thread (18+ messages)  latest in thread

Message-ID: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
Permalink:  ../005e01c228ee$bd1913e0$cab990c1@atc.unican.es/
Also on:    postgresql.org/message-id/005e01c228ee$bd1913e0$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, pgsql-hackers@postgresql.org
  Subject: Re: please help on query
  In-Reply-To: <005e01c228ee$bd1913e0$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