Re: [HACKERS] Optimizer badness in 7.0 beta

Brian Hirt <bhirt@mobygames.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_unique1on 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