Re: not using indexes on large table

> From: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of
> Jeroen Kleijer
>
> The problems comes when I try to do a query without using a
> where clause
> because by then, it completely discards the indexes and does
> a complete
> table scan which takes over half an hour! (40.710.725 rows, 1110258
> pages, 1715 seconds)
>
> I've tried several things but doing a query like:
> select distinct volume from project_access_times
> or
> select distinct qtree from project_access_times
> always result in a full sequential table scan even after a
> 'vacuum' and
> 'vacuum analyze'.

To my knowledge Postgres doesn't use indexes for distinct queries or
grouping. Also you are getting horrible IO performance. Our old slow test
machine can scan a table of 12 million rows in 100 seconds, and our
production server can do the same in 20 seconds. If possible, I would try
running the same thing on your local hard drive. That way you can see how
much the netapp and NFS are slowing you down. Although in the end if you
need very fast distinct queries, you will need to maintain a separate table.

Dave