From: Heikki Linnakangas <heikki@enterprisedb.com>
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)
Date: Thu, 18 Oct 2007 20:06:11 +0100
Message-ID: <4717AEA3.7030507@enterprisedb.com> (raw)
In-Reply-To: <4717914D.1050704@cbio.mskcc.org>
References: <4717914D.1050704@cbio.mskcc.org>
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
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: heikki@enterprisedb.com, major@cbio.mskcc.org
Subject: Re: How to improve speed of 3 table join &group (HUGE tables)
In-Reply-To: <4717AEA3.7030507@enterprisedb.com>
* 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