excessive performance difference

From: Luis Amigo <lamigo(at)atc(dot)unican(dot)es>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>, pgsql-general(at)postgresql(dot)org
Subject: excessive performance difference
Date: 2001-12-13 12:35:16
Message-ID: 3C18A084.BD9E93B8@atc.unican.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general 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)

Attachment Content-Type Size
query19.old text/plain 1.0 KB
query19.sql text/plain 1.4 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Carlo Perassi 2001-12-13 13:28:02 Re: exit status code table?
Previous Message Sanjin 2001-12-13 10:52:24 Insert character codes in PosgreSQL with SQL command

Browse pgsql-novice by date

  From Date Subject
Next Message S P Arif Sahari Wibowo 2001-12-13 15:46:04 Varying bit field: to set and query a particular bit
Previous Message Josh Berkus 2001-12-12 20:51:22 Re: Storing number '001' ?