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