From: Dave Dutcher <dave@tridecap.com>
To: 'Jeroen Kleijer' <jeroen.kleijer@xs4all.nl>
To: 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> (raw)
In-Reply-To: <20070421201742.GB99389@penrose.jmkdomain.lan>
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dave@tridecap.com, jeroen.kleijer@xs4all.nl
Subject: Re: not using indexes on large table
In-Reply-To: <004601c785b7$9dda0950$0300000a@tridecap.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox