"Ron Mayer" <[EMAIL PROTECTED]> writes: > I notice that I get different plans when I run the > following two queries that I thought would be > identical. > > select distinct test_col from mytable; > select test_col from mytable group by test_col; > > Any reason why it favors one in one case but not the other?
I think "distinct" just doesn't know about hash aggregates yet. That's partly an oversight and partly of a "feature" in that it gives a convenient way to write a query which avoids them. I think it's also partly that "distinct" is trickier to fix because it's the same codepath as "distinct on" which is decidedly more complex than a simple "distinct". > d=# explain analyze select distinct test_col from mytable; > > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------ > Unique (cost=0.00..14927.69 rows=27731 width=4) (actual time=0.144..915.214 > rows=208701 loops=1) > -> Index Scan using "mytable(test_col)" on mytable (cost=0.00..14160.38 > rows=306925 width=4) (actual time=0.140..575.580 rows=306925 loops=1) > Total runtime: 1013.657 ms > (3 rows) I assume you have random_page_cost dialled way down? The costs seem too low for the default random_page_cost. This query would usually generate a sort rather than an index scan. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
