Skip site navigation (1) Skip section navigation (2)

Re: How to improve speed of 3 table join &group (HUGE tables)

From: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
To: "John Major" <major(at)cbio(dot)mskcc(dot)org>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: How to improve speed of 3 table join &group (HUGE tables)
Date: 2007-10-18 19:06:11
Message-ID: 4717AEA3.7030507@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: John MajorDate: 2007-10-18 19:46:19
Subject: Re: How to improve speed of 3 table join &group (HUGE tables)
Previous:From: Nis JørgensenDate: 2007-10-18 18:58:17
Subject: Re: How to improve speed of 3 table join &group (HUGE tables)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group