From: Stephen Frost <sfrost@snowman.net>
To: Ksenia Marasanova <ksenia.marasanova@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: LEFT JOIN optimization
Date: Sun, 11 Sep 2005 20:34:50 -0400
Message-ID: <20050912003450.GD6026@ns.snowman.net> (raw)
In-Reply-To: <130df19305091114477e2e581b@mail.gmail.com>
References: <130df19305091110125a2d174f@mail.gmail.com>
<20050911210035.GC6026@ns.snowman.net>
<130df19305091114477e2e581b@mail.gmail.com>
* Ksenia Marasanova (ksenia.marasanova@gmail.com) wrote:
> test=# explain analyze select * from user_ left join church on
> user_.church_id = church.id;
[...]
> Total runtime: 2025.946 ms
> (6 rows)
>
> test=# set enable_seqscan='false';
> SET
> test=# explain analyze select * from user_ left join church on
> user_.church_id = church.id;
>
[...]
> Total runtime: 2131.364 ms
> (5 rows)
>
>
> I followed some tips on the web and vacuum-ed database, I think the
> query is faster now, almost acceptable, but still interesting to know
> if it possible to optimize it...
I have to say that it does seem a bit slow for only 12,000 rows..
What's the datatype of user_.church_id and church.id? Are you sure you
really want all 12,000 rows every time you run that query? Perhaps
there's a 'where' clause you could apply with an associated index to
limit the query to just what you actually need?
You'll noticed from above, though, that the non-index scan is faster.
I'd expect that when using a left-join query: you have to go through the
entire table on an open left-join like that, a sequencial scan is going
to be the best way to do that. The fact that church.id is hashed makes
the solution the planner came up with almost certainly the best one
possible.
Are you sure a left-join is what you want? Sounds like maybe you've
moved (for some reason) from a regular join to a left join with a
filtering in the application which is probably a bad move... If you can
use at least some filtering in the database I expect that'd help..
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 188B) download
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: sfrost@snowman.net, ksenia.marasanova@gmail.com
Subject: Re: LEFT JOIN optimization
In-Reply-To: <20050912003450.GD6026@ns.snowman.net>
* 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