Re: excessive performance difference - Mailing list pgsql-general

Masaru Sugawara wrote:

>
> I found that the query, query19.old, has no hints to run the planner
> effectively.  I would think you need to change a bit like this:

Thank you for your answer Masaru, the only thing I wanted to communicate is that
query19.old works perfectly with pay bases, and someone could get stucked with the
same problem. What you are getting outside is same condition that is inside of
all, partkey is primary key, so it is not neccesary to create index.
query19.sql gives better performance than yours, it is pseudo intra-paralellism,
if anyone is interested here are the results

tpch=# \e /disco3/lamigo/tpch/consultas/query19.old
NOTICE:  QUERY PLAN:

Aggregate  (cost=6073.03..6073.03 rows=1 width=116) (actual time=6865.81..6865.81
rows=1 loops=1)
  ->  Hash Join  (cost=145.00..6073.03 rows=1 width=116) (actual
time=2057.06..6865.58 rows=1 loops=1)
        ->  Seq Scan on lineitem  (cost=0.00..2326.05 rows=60305 width=80) (actual
time=0.38..3244.77 rows=60279 loops=1)
        ->  Hash  (cost=140.00..140.00 rows=2000 width=36) (actual
time=132.55..132.55 rows=0 loops=1)
              ->  Seq Scan on part  (cost=0.00..140.00 rows=2000 width=36) (actual
time=13.66..98.65 rows=2000 loops=1)
Total runtime: 6867.08 msec

EXPLAIN
tpch=# \e /disco3/lamigo/tpch/consultas/query19.sql
NOTICE:  QUERY PLAN:

Nested Loop  (cost=9771.17..9771.19 rows=1 width=96) (actual time=6539.58..6539.74
rows=1 loops=1)
  ->  Nested Loop  (cost=6513.97..6513.98 rows=1 width=64) (actual
time=4254.88..4254.98 rows=1 loops=1)
        ->  Subquery Scan resultado2  (cost=3257.16..3257.16 rows=1 width=30)
(actual time=2185.23..2185.27 rows=1 loops=1)
              ->  Aggregate  (cost=3257.16..3257.16 rows=1 width=30) (actual
time=2185.19..2185.20 rows=1 loops=1)
                    ->  Hash Join  (cost=175.00..3257.16 rows=1 width=30) (actual
time=2185.07..2185.07 rows=0 loops=1)
                          ->  Seq Scan on lineitem  (cost=0.00..3079.86 rows=458
width=26) (actual time=2.50..2119.31 rows=519 loops=1)
                          ->  Hash  (cost=175.00..175.00 rows=1 width=4) (actual
time=55.40..55.40 rows=0 loops=1)
                                ->  Seq Scan on part  (cost=0.00..175.00 rows=1
width=4) (actual time=21.11..55.27 rows=3 loops=1)
        ->  Subquery Scan resultado3  (cost=3256.81..3256.81 rows=1 width=30)
(actual time=2069.55..2069.59 rows=1 loops=1)
              ->  Aggregate  (cost=3256.81..3256.81 rows=1 width=30) (actual
time=2069.51..2069.51 rows=1 loops=1)
                    ->  Hash Join  (cost=175.01..3256.81 rows=1 width=30) (actual
time=2069.39..2069.39 rows=0 loops=1)
                          ->  Seq Scan on lineitem  (cost=0.00..3079.86 rows=387
width=26) (actual time=2.24..2004.59 rows=483 loops=1)
                          ->  Hash  (cost=175.00..175.00 rows=2 width=4) (actual
time=55.00..55.00 rows=0 loops=1)
                                ->  Seq Scan on part  (cost=0.00..175.00 rows=2
width=4) (actual time=17.20..54.87 rows=3 loops=1)
  ->  Subquery Scan resultado  (cost=3257.19..3257.19 rows=1 width=30) (actual
time=2284.52..2284.55 rows=1 loops=1)
        ->  Aggregate  (cost=3257.19..3257.19 rows=1 width=30) (actual
time=2284.47..2284.48 rows=1 loops=1)
              ->  Hash Join  (cost=175.00..3257.19 rows=1 width=30) (actual
time=688.98..2284.13 rows=1 loops=1)
                    ->  Seq Scan on lineitem  (cost=0.00..3079.86 rows=464
width=26) (actual time=6.31..2219.45 rows=492 loops=1)
                    ->  Hash  (cost=175.00..175.00 rows=1 width=4) (actual
time=54.99..54.99 rows=0 loops=1)
                          ->  Seq Scan on part  (cost=0.00..175.00 rows=1 width=4)
(actual time=42.02..54.89 rows=2 loops=1)
Total runtime: 6541.95 msec

EXPLAIN
tpch=# create index index_partkey on lineitem (partkey int4_ops);
CREATE
tpch=# analyze;
ANALYZE
tpch=# \e /disco3/lamigo/tpch/consultas/query19.old
ANALYZE
tpch=# \i /disco3/lamigo/tpch/consultas/query19.old
psql:/disco3/lamigo/tpch/consultas/query19.old:35: NOTICE:  QUERY PLAN:

Aggregate  (cost=6183.50..6183.50 rows=1 width=116) (actual time=6816.22..6816.22
rows=1 loops=1)
  ->  Hash Join  (cost=145.00..6183.50 rows=1 width=116) (actual
time=2013.52..6815.98 rows=1 loops=1)
        ->  Seq Scan on lineitem  (cost=0.00..2326.05 rows=60305 width=80) (actual
time=0.38..3220.05 rows=60279 loops=1)
        ->  Hash  (cost=140.00..140.00 rows=2000 width=36) (actual
time=108.44..108.44 rows=0 loops=1)
              ->  Seq Scan on part  (cost=0.00..140.00 rows=2000 width=36) (actual
time=1.02..76.31 rows=2000 loops=1)
Total runtime: 6817.15 msec

Thank you and regards.
Luis Amigo
Universidad de Cantabria