select distinct uses index scan vs full table scan

From: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: select distinct uses index scan vs full table scan
Date: 2011-12-13 18:12:47
Message-ID: CAKuK5J12QokFh88tQz-oJMSiBg2QyjM7K7HLnbYi3Ze+Y5BtWQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I've got a 5GB table with about 12 million rows.
Recently, I had to select the distinct values from just one column.
The planner chose an index scan. The query took almost an hour.
When I forced index scan off, the query took 90 seconds (full table scan).

The planner estimated 70,000 unique values when, in fact, there are 12
million (the value for this row is *almost* but not quite unique).
What's more, despite bumping the statistics on that column up to 1000
and re-analyzing, the planner now thinks that there are 300,000 unique
values.
How can I tell the planner that a given column is much more unique
than, apparently, it thinks it is?
The column type is INET.
This is on PG 8.4.10 on Linux x86_64, with
81f4e6cd27d538bc27e9714a9173e4df353a02e5 applied.

--
Jon

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2011-12-13 19:57:57 Re: select distinct uses index scan vs full table scan
Previous Message Pavel Stehule 2011-12-13 14:42:58 Re: Postgres array parser