Search Postgresql Archives

Re: Why isn't an index being used when selecting a distinct value?

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

 



"Keaton Adams" <kadams@xxxxxxxxxxx> writes:

> Version: Postgres 8.1.4
> Platform: RHEL
>
> Given this scenario with the indexes in place, when I ask for the distinct
> field1_id values, why does the optimizer choose a sequential scan instead of
> just reading from the kda_log_fid_cre_20080123_idx index? The time it takes
> to perform the sequential scan against 20+ million records is way too slow.

Try (temporarily) doing:

SET enable_seqscan = off;

> keaton=# explain select distinct field1_id into temp kda_temp from
> kda_log_20080213;

If the database is right that will be even slower. Using a full index scan
requires a lot of random access seeks, generally the larger the table the
*more* likely a sequential scan and sort is a better approach than using an
index.

If it's wrong and it's faster then you have to consider whether it's only
faster because you've read the table into cache already. Will it be in cache
in production? If so then you migth try raising effective_cache_size or
lowering random_page_cost.

Another thing to try is using GROUP BY instead of DISTINCT. This is one case
where the postgres optimizer doesn't handle the two equivalent cases in
exactly the same way and there are some plans available in one method that
aren't in the other. That's only likely to help if you have relative few
values of field1_id but it's worth trying.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux