| 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 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | John Major | 2007-10-18 19:46:19 | Re: How to improve speed of 3 table join &group (HUGE tables) |
| Previous Message | Nis Jørgensen | 2007-10-18 18:58:17 | Re: How to improve speed of 3 table join &group (HUGE tables) |