Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2B60C9FC1F1 for ; Thu, 18 Oct 2007 14:21:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 97684-09 for ; Thu, 18 Oct 2007 14:21:31 -0300 (ADT) X-Greylist: delayed 00:20:12.901929 by SQLgrey-1.7.5 Received: from cbio.mskcc.org (cbio.mskcc.org [140.163.254.35]) by postgresql.org (Postfix) with ESMTP id 275F89FC1D0 for ; Thu, 18 Oct 2007 14:21:32 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by cbio.mskcc.org (Postfix) with ESMTP id CBB4661AA8 for ; Thu, 18 Oct 2007 13:01:04 -0400 (EDT) Received: from cbio.mskcc.org ([127.0.0.1]) by localhost (cbio [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 12265-03-2 for ; Thu, 18 Oct 2007 13:01:04 -0400 (EDT) Received: from mrchips.local (mrchips.mskcc.org [172.21.218.242]) by cbio.mskcc.org (Postfix) with ESMTP id BB0FB3055D for ; Thu, 18 Oct 2007 13:01:01 -0400 (EDT) Message-ID: <4717914D.1050704@cbio.mskcc.org> Date: Thu, 18 Oct 2007 13:01:01 -0400 From: John Major User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: How to improve speed of 3 table join &group (HUGE tables) Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at cbio.mskcc.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.5 tagged_above=0 required=5 tests=AWL=0.499, BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200710/219 X-Sequence-Number: 27241 I am trying to join three quite large tables, and the query is unbearably slow(meaning I can't get results in more than a day of processing). I've tried the basic optimizations I understand, and nothing has improved the execute speed.... any help with this would be greatly appreciated The three tables are quite large: sequence_fragment = 4.5 million rows sequence_external_info = 10million rows sequence_alignment = 500 million rows The query I am attempting to run is this: select sf.library_id, fio.clip_type , count(distinct(sa.sequence_id)) from sequence_alignment sa, sequence_fragment sf, fragment_external_info fio where sf.seq_frag_id = fio.sequence_frag_id and sf.sequence_id = sa.sequence_id group by sf.library_id, fio.clip_type NOTES: ~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 The explain plan is as follows: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1443436673.93..1480593403.29 rows=54 width=16) -> Sort (cost=1443436673.93..1452725856.10 rows=3715672868 width=16) Sort Key: sf.library_id, fio.clip_type -> Merge Join (cost=263624049.25..319410068.18 rows=3715672868 width=16) Merge Cond: (sf.sequence_id = sa.sequence_id) -> Sort (cost=38102888.77..38128373.54 rows=10193906 width=16) Sort Key: sf.sequence_id -> Hash Join (cost=5305576.14..36080036.76 rows=10193906 width=16) Hash Cond: (fio.sequence_frag_id = sf.seq_frag_id) -> Index Scan using frag_ext_info_seq_frag_id on fragment_external_info fio (cost=0.00..30450510.27 rows=10193906 width=12) -> Hash (cost=5223807.54..5223807.54 rows=4453728 width=12) -> Index Scan using seq_frag_seqid_ind on sequence_fragment sf (cost=0.00..5223807.54 rows=4453728 width=12) -> Sort (cost=225521160.48..226688766.88 rows=467042560 width=4) Sort Key: sa.sequence_id -> Seq Scan on sequence_alignment sa (cost=100000000.00..110379294.60 rows=467042560 width=4) 15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] Thanks in advance! John Major