Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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: query19.sql
Description: text/plain (1.4 KB)
Attachment: query19.old
Description: text/plain (1.0 KB)

Responses

pgsql-novice by date

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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group