thread-index: AcgRuiYdb8aybo3iSdWAN8AcDX4C8w==
Message-ID: <4717AEA3.7030507@enterprisedb.com>
Date: Thu, 18 Oct 2007 20:06:11 +0100
Content-Class: urn:content-classes:message
Importance: normal
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.12 (X11/20070730)
MIME-Version: 1.0
To: "John Major" <major@cbio.mskcc.org>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: How to improve speed of 3 table join &group (HUGE tables)
References: <4717914D.1050704@cbio.mskcc.org>
In-Reply-To: <4717914D.1050704@cbio.mskcc.org>
Content-Type: text/plain;
	charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit

John Major wrote:
> ~there are indexes on all of the fields being joined (but not on
> library_id or clip_type ). ~Everything has been re-analyzed post index
> creation
> ~I've tried "set enable_seqscan=off" and set (join_table_order or
> something) = 1

Seqscanning and sorting a table is generally faster than a full scan of
the table using an index scan, unless the heap is roughly in the index
order. You probably need to CLUSTER the tables to use the indexes
effectively.

Are you sure you have an index on sequence_alignment.sequence_id? The
planner seems to choose a seqscan + sort, even though you've set
enable_seqscan=false.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com