please help on query
| От | Luis Alberto Amigo Navarro |
|---|---|
| Тема | please help on query |
| Дата | |
| Msg-id | 005e01c228ee$bd1913e0$cab990c1@atc.unican.es обсуждение исходный текст |
| Ответы |
Re: please help on query
("J. R. Nield" <jrnield@usol.com>)
Re: please help on query (Hannu Krosing <hannu@tm.ee>) Re: please help on query (Hannu Krosing <hannu@tm.ee>) Re: please help on query (Hannu Krosing <hannu@tm.ee>) |
| Список | pgsql-hackers |
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;
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)
-> 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
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
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
В списке pgsql-hackers по дате отправления: