Re: [HACKERS] Optimizer badness in 7.0 beta

Brian Hirt <bhirt(at)mobygames(dot)com> writes:
> -- PG 7.0 --
> NOTICE: QUERY PLAN:

> Sort (cost=383940.72..383940.72 rows=905 width=59)
> -> Seq Scan on game (cost=0.00..383896.28 rows=905 width=59)
> SubPlan
> -> Unique (cost=0.00..808.88 rows=0 width=4)
> -> Index Scan using game_developer_game_index on game_developer (cost=0.00..808.87 rows=4 width=4)

There's something very strange about this query plan --- why is the
estimated cost of the indexscan so high? If I do, say,

regression=# explain select distinct * from tenk1 where unique1 < 3;
NOTICE: QUERY PLAN:

Unique (cost=3.22..3.34 rows=0 width=148)
-> Sort (cost=3.22..3.22 rows=3 width=148)
-> Index Scan using tenk1_unique1 on tenk1 (cost=0.00..3.19 rows=3 width=148)

The tenk1 table from the regression database is only 10K rows, versus
15K in your table, but still I'd expect costs not a heck of a lot higher
than one page fetch per tuple retrieved. How is it coming up with a
cost of 800 to retrieve 4 tuples?

Could I trouble you for the exact declarations of the tables and indices
involved here? Also, what plan do you get from 7.0 if you do

set enable_indexscan = 'off';

before the EXPLAIN?

regards, tom lane