Message-ID: <4717BC69.2010809@cbio.mskcc.org>
Date: Thu, 18 Oct 2007 16:04:57 -0400
From: John Major <major@cbio.mskcc.org>
User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728)
MIME-Version: 1.0
To: Heikki Linnakangas <heikki@enterprisedb.com>
CC: pgsql-performance@postgresql.org
Subject: Re: How to improve speed of 3 table join &group (HUGE tables)
References: <4717914D.1050704@cbio.mskcc.org>
	<4717AEA3.7030507@enterprisedb.com>
In-Reply-To: <4717AEA3.7030507@enterprisedb.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Hekki-

When I turn seq_scan off for the new query:

explain
select sf.library_id, fio.clip_type , count(sf.sequence_id)
   from    sequence_fragment sf, fragment_external_info fio
   where sf.seq_frag_id = fio.sequence_frag_id
   and  sf.sequence_id IN
       (SELECT sequence_id from sequence_alignment)
   group by sf.library_id, fio.clip_type

The index is used... but the cost gets worse!
it goes from:
11831119
-TO-
53654888

Actually... The new query executes in ~ 15 minutes... which is good 
enough for me for now.

Thanks Nis!

john


Heikki Linnakangas wrote:
> 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.
>
>