select distinct uses index scan vs full table scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux