> From: pgsql-performance-owner@xxxxxxxxxxxxxx > [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] 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
