From: "Dave Dutcher" <dave@tridecap.com>
To: "'Jeroen Kleijer'" <jeroen.kleijer@xs4all.nl>,
	<pgsql-performance@postgresql.org>
Subject: Re: not using indexes on large table
Date: Mon, 23 Apr 2007 09:56:46 -0500
Message-ID: <004601c785b7$9dda0950$0300000a@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <20070421201742.GB99389@penrose.jmkdomain.lan>
Thread-Index: AceEVmIGdXnViYLAQKydBrh8C5WOXwBX6UGQ
Importance: Normal

> From: pgsql-performance-owner@postgresql.org=20
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of=20
> Jeroen Kleijer
>
> The problems comes when I try to do a query without using a=20
> where clause
> because by then, it completely discards the indexes and does=20
> a complete
> table scan which takes over half an hour! (40.710.725 rows, 1110258
> pages, 1715 seconds)
>=20
> 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=20
> '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