excessive performance difference
От | Luis Amigo |
---|---|
Тема | excessive performance difference |
Дата | |
Msg-id | 3C18A084.BD9E93B8@atc.unican.es обсуждение исходный текст |
Список | pgsql-novice |
We're running a query on psql7.2b3, in which planner is unable to find correct way to solve. This is only informative message If we run query19.old it takes more than 7 hours with this plan Aggregate (cost=17310310.04..17310310.04 rows=1 width=116) -> Nested Loop (cost=0.00..17310310.00 rows=15 width=116) -> Seq Scan on part (cost=0.00..140.00 rows=2000 width=36) -> Seq Scan on lineitem (cost=0.00..2325.79 rows=60279 width=80) If we use query19.sql it takes 6 secs and plan is Nested Loop (cost=9770.00..9770.02 rows=1 width=96) (actual time=6538.94..6539.09 rows=1 loops=1) -> Nested Loop (cost=6513.40..6513.41 rows=1 width=64) (actual time=4472.32..4472.41 rows=1 loops=1) -> Subquery Scan resultado (cost=3256.82..3256.82 rows=1 width=30) (actual time=2283.08..2283.11 rows=1 loops=1) -> Aggregate (cost=3256.82..3256.82 rows=1 width=30) (actual time=2283.03..2283.04 rows=1 loops=1) -> Hash Join (cost=175.00..3256.81 rows=1 width=30) (actual time=688.18..2282.70 rows=1 loops=1) -> Seq Scan on lineitem (cost=0.00..3079.28 rows=506 width=26) (actual time=6.29..2217.74 rows=492 loops=1) -> Hash (cost=175.00..175.00 rows=1 width=4) (actual time=55.20..55.20 rows=0 loops=1) -> Seq Scan on part (cost=0.00..175.00 rows=1 width=4) (actual time=42.12..55.11 rows=2 loops=1) -> Subquery Scan resultado2 (cost=3256.59..3256.59 rows=1 width=30) (actual time=2189.15..2189.18 rows=1 loops=1) -> Aggregate (cost=3256.59..3256.59 rows=1 width=30) (actual time=2189.10..2189.11 rows=1 loops=1) -> Hash Join (cost=175.00..3256.58 rows=1 width=30) (actual time=2188.99..2188.99 rows=0 loops=1) -> Seq Scan on lineitem (cost=0.00..3079.28 rows=460 width=26) (actual time=2.53..2124.05 rows=519 loops=1) -> Hash (cost=175.00..175.00 rows=1 width=4) (actual time=54.97..54.97 rows=0 loops=1) -> Seq Scan on part (cost=0.00..175.00 rows=1 width=4) (actual time=21.05..54.85 rows=3 loops=1) -> Subquery Scan resultado3 (cost=3256.59..3256.59 rows=1 width=30) (actual time=2066.41..2066.44 rows=1 loops=1) -> Aggregate (cost=3256.59..3256.59 rows=1 width=30) (actual time=2066.36..2066.37 rows=1 loops=1) -> Hash Join (cost=175.01..3256.59 rows=1 width=30) (actual time=2066.24..2066.24 rows=0 loops=1) -> Seq Scan on lineitem (cost=0.00..3079.28 rows=460 width=26) (actual time=2.25..2001.36 rows=483 loops=1) -> Hash (cost=175.00..175.00 rows=2 width=4) (actual time=55.48..55.48 rows=0 loops=1) -> Seq Scan on part (cost=0.00..175.00 rows=2 width=4) (actual time=17.21..55.34 rows=3 loops=1) explain SELECT sum(lineitem.extendedprice*(1-lineitem.discount))AS revenue FROM part, lineitem WHERE ( part.partkey=lineitem.partkey AND part.brand='Brand#12' AND part.container IN('SM CASE','SM BOX','SM PACK','SM PKG') AND lineitem.quantity>=1 AND lineitem.quantity<=1+10 AND part.size BETWEEN 1 AND 5 AND lineitem.shipmode IN ('AIR','AIR REG') AND lineitem.shipinstruct='DELIVER IN PERSON' ) OR ( part.partkey=lineitem.partkey AND part.brand='Brand#23' AND part.container IN('MED BAG','MED BOX','MED PACK','MED PKG') AND lineitem.quantity>=10 AND lineitem.quantity<=10+10 AND part.size BETWEEN 1 AND 10 AND lineitem.shipmode IN ('AIR','AIR REG') AND lineitem.shipinstruct='DELIVER IN PERSON' ) OR ( part.partkey=lineitem.partkey AND part.brand='Brand#34' AND part.container IN('LG CASE','LG BOX','LG PACK','LG PKG') AND lineitem.quantity>=20 AND lineitem.quantity<=20+10 AND part.size BETWEEN 1 AND 15 AND lineitem.shipmode IN ('AIR','AIR REG') AND lineitem.shipinstruct='DELIVER IN PERSON' ); SELECT (suma+suma1+suma2)AS revenue FROM (SELECT CASE WHEN sum(extendedprice*(1-discount))>0 THEN sum(extendedprice*(1-discount)) ELSE 0 END AS suma FROM part, lineitem WHERE ( part.partkey=lineitem.partkey AND part.brand='Brand#12' AND part.container IN('SM CASE','SM BOX','SM PACK','SM PKG') AND lineitem.quantity>=1 AND lineitem.quantity<=1+10 AND part.size BETWEEN 1 AND 5 AND lineitem.shipmode IN ('AIR','AIR REG') AND lineitem.shipinstruct='DELIVER IN PERSON' ))AS resultado, (SELECT CASE WHEN sum(extendedprice*(1-discount))>0 THEN sum(extendedprice*(1-discount)) ELSE 0 END AS suma1 FROM part, lineitem WHERE ( part.partkey=lineitem.partkey AND part.brand='Brand#23' AND part.container IN('MED BAG','MED BOX','MED PACK','MED PKG') AND lineitem.quantity>=10 AND lineitem.quantity<=20 AND part.size BETWEEN 1 AND 10 AND lineitem.shipmode IN ('AIR','AIR REG') AND lineitem.shipinstruct='DELIVER IN PERSON' ))AS resultado2, (SELECT CASE WHEN sum(extendedprice*(1-discount))>0 THEN sum(extendedprice*(1-discount)) ELSE 0 END AS suma2 FROM part, lineitem WHERE ( part.partkey=lineitem.partkey AND part.brand='Brand#34' AND part.container IN('LG CASE','LG BOX','LG PACK','LG PKG') AND lineitem.quantity>=20 AND lineitem.quantity<=30 AND part.size BETWEEN 1 AND 15 AND lineitem.shipmode IN ('AIR','AIR REG') AND lineitem.shipinstruct='DELIVER IN PERSON' ))AS resultado3;
В списке pgsql-novice по дате отправления:
Следующее
От: S P Arif Sahari WibowoДата:
Сообщение: Varying bit field: to set and query a particular bit