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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Demoor | 2011-01-13 13:57:29 | BUG #5835: PL/Python crashes |
Previous Message | Tom Lane | 2011-01-13 01:26:19 | Re: inheritance_planner() bug in 9.1 |