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

BUG #5834: Planner - cost for hash scan too small

From: "Pasman" <pasman(dot)p(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5834: Planner - cost for hash scan too small
Date: 2011-01-13 09:07:47
Message-ID: 201101130907.p0D97lX2063378@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5834
Logged by:          Pasman
Email address:      pasman(dot)p(at)gmail(dot)com
PostgreSQL version: 8.4.6
Operating system:   WinXP sp2
Description:        Planner - cost for hash scan too small
Details: 

Postgres not estimate cost of creating hash index
when it plans hash join. Use case:

create table test1 as
select i,'aaaaaaaaaa'::text as t1 from generate_series(1,10000) g(i);
analyze test1;

create table test2 as
select i,'aaaaaaaaaa'::text as t2 from generate_series(1,10000) g(i);
analyze test2;

explain analyze 
select * from test1 natural join test2;

"Hash Join  (cost=280.00..685.00 rows=10000 width=26) (actual
time=48.914..125.526 rows=10000 loops=1)"
"  Hash Cond: (test1.i = test2.i)"
"  ->  Seq Scan on test1  (cost=0.00..155.00 rows=10000 width=15) (actual
time=0.032..21.693 rows=10000 loops=1)"
"  ->  Hash  (cost=155.00..155.00 rows=10000 width=15) (actual
time=48.835..48.835 rows=10000 loops=1)"
"        ->  Seq Scan on test2  (cost=0.00..155.00 rows=10000 width=15)
(actual time=0.018..24.045 rows=10000 loops=1)"
"Total runtime: 146.291 ms"


Cost for creating hash (155.00) is equal to 
cost of sequential scan on test2 but 
real time is 2 times bigger. 

I think that cost of Hash node ought to include costs of calculating hash
function and inserting tuple into index:

cost of seq scan 
+ numtuples*cpu_operator_cost 
+ numtuples*cpu_index_tuple_cost

Responses

pgsql-bugs by date

Next:From: Julien DemoorDate: 2011-01-13 13:57:29
Subject: BUG #5835: PL/Python crashes
Previous:From: Tom LaneDate: 2011-01-13 01:26:19
Subject: Re: inheritance_planner() bug in 9.1

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