From: Scott Marlowe <scott.marlowe@gmail.com>
To: Uwe C. Schroeder <uwe@oss4u.com>
Cc: pgsql-admin@postgresql.org, Tena Sakai <tsakai@gallo.ucsf.edu>
Subject: Re: very, very slow performance
Date: Sat, 21 Feb 2009 01:57:56 -0700
Message-ID: <dcc563d10902210057k329f93bat76d07ba0016a24fe@mail.gmail.com> (raw)
In-Reply-To: <200902202145.49391.uwe@oss4u.com>
References: <FE44E0D7EAD2ED4BB2165071DB8E328C0378F760@egcrc-ex01.egcrc.org>
<dcc563d10902192148s7f781086p1c0a469e0ebc1dc5@mail.gmail.com>
<FE44E0D7EAD2ED4BB2165071DB8E328C0378F774@egcrc-ex01.egcrc.org>
<200902202145.49391.uwe@oss4u.com>
On Fri, Feb 20, 2009 at 10:45 PM, Uwe C. Schroeder <uwe@oss4u.com> wrote:
>
> On Friday 20 February 2009, Tena Sakai wrote:
>> Hi Scott,
>>
>> > What does explain and (it'll take a while to get
>> > it) explain analyze select ... have to say?
>>
>> ---------------------------------------------------------------------------
>>---------- Hash Join (cost=165264.65..55486119.31 rows=601095277 width=32)
>> Hash Cond: (genotype.allele1id = a1.alleleid)
>> -> Hash Join (cost=82632.33..34731274.54 rows=601095277 width=34)
>> Hash Cond: (genotype.allele2id = a2.alleleid)
>> -> Seq Scan on genotype (cost=0.00..13976429.77 rows=601095277
>> width=36) -> Hash (cost=42474.59..42474.59 rows=2447659 width=6) -> Seq
>> Scan on allele a2 (cost=0.00..42474.59 rows=2447659 width=6) -> Hash
>> (cost=42474.59..42474.59 rows=2447659 width=6)
>> -> Seq Scan on allele a1 (cost=0.00..42474.59 rows=2447659
>> width=6) (9 rows)
I was wrong about this query, it is constrained by the where clause.
I much prefer join on syntax as it's more obvious what's joining to
what. Pgsql is smart enough to reorder join clauses as long as it's
not contrained in by say, a left join, and even then there's some
wiggle room I think. Anyway...
> The above tells you that you don't have indices in place. Postgres chooses a
> seq scan - which as the name implies scans all the rows in sequencial order.
Well, he's grabbing everything, so I'm betting an index won't buy you
anything unless everything fits in memory and you set random_page_cost
low enough and shared_buffers and effective_cache high enough, then an
index will lose.
However, if you always access the tables in a given order, you can
cluster tables and get really fast results. I'd try clustering on an
index for each sub table, clustering on that, and adding order bys to
put the result sets into matching clustered index fields for each
joined table.
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-admin@postgresql.org
Cc: scott.marlowe@gmail.com, uwe@oss4u.com, tsakai@gallo.ucsf.edu
Subject: Re: very, very slow performance
In-Reply-To: <dcc563d10902210057k329f93bat76d07ba0016a24fe@mail.gmail.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