Re: Why order by column not using index with distinct keyword in select clause?

Arup Rakshit <ar(at)zeit(dot)io> writes:
> I have define a simple B Tree index on column *country* for users table. I don’t understand why the order by column not using the index scan when using *distinct* keyword in the select clause. Can anyone explain what is happening here?

Doesn't look complicated to me: the cost of the hashagg+sort plan is
283.85, while the cost of the indexscan plans is 886.27, and adding
a Uniq node on top of them would have been even more.

The critical point here seems to be that the hashagg node is able to
get rid of so many rows that the cost of sorting what remains is low.

Also notable is that the IOS isn't giving you any advantage over a
plain indexscan, which apparently is because none of the table's
pages are marked all-visible. If the table is static then a VACUUM
would help that.

regards, tom lane