[HACKERS] please help on query

[Luis Alberto Amigo Navarro]

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