From: John Major <major@cbio.mskcc.org>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to improve speed of 3 table join &group (HUGE tables)
Date: Thu, 18 Oct 2007 16:04:57 -0400
Message-ID: <4717BC69.2010809@cbio.mskcc.org> (raw)
In-Reply-To: <4717AEA3.7030507@enterprisedb.com>
References: <4717914D.1050704@cbio.mskcc.org>
<4717AEA3.7030507@enterprisedb.com>
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.
>
>
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: major@cbio.mskcc.org, heikki@enterprisedb.com
Subject: Re: How to improve speed of 3 table join &group (HUGE tables)
In-Reply-To: <4717BC69.2010809@cbio.mskcc.org>
* 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