The following bug has been logged online: Bug reference: 5834 Logged by: Pasman Email address: pasma...@gmail.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
