From: Ksenia Marasanova <ksenia.marasanova@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: LEFT JOIN optimization
Date: Mon, 12 Sep 2005 00:47:57 +0300
Message-ID: <130df19305091114477e2e581b@mail.gmail.com> (raw)
In-Reply-To: <20050911210035.GC6026@ns.snowman.net>
References: <130df19305091110125a2d174f@mail.gmail.com>
<20050911210035.GC6026@ns.snowman.net>
2005/9/12, Stephen Frost <sfrost@snowman.net>:
> * Ksenia Marasanova (ksenia.marasanova@gmail.com) wrote:
> > Any tips are greatly appreciated.
>
> EXPLAIN ANALYZE of the same queries would be much more useful.
Thanks, here it is:
test=# explain analyze select * from user_ left join church on
user_.church_id = church.id;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=6.44..7626.69 rows=12763 width=325) (actual
time=388.573..2016.929 rows=12763 loops=1)
Hash Cond: ("outer".church_id = "inner".id)
-> Seq Scan on user_ (cost=0.00..7430.63 rows=12763 width=245)
(actual time=360.431..1120.012 rows=12763 loops=1)
-> Hash (cost=5.75..5.75 rows=275 width=80) (actual
time=27.985..27.985 rows=0 loops=1)
-> Seq Scan on church (cost=0.00..5.75 rows=275 width=80)
(actual time=0.124..26.953 rows=275 loops=1)
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;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Merge Right Join (cost=0.00..44675.77 rows=12763 width=325) (actual
time=0.808..2119.099 rows=12763 loops=1)
Merge Cond: ("outer".id = "inner".church_id)
-> Index Scan using chirch_pkey on church (cost=0.00..17.02
rows=275 width=80) (actual time=0.365..5.471 rows=275 loops=1)
-> Index Scan using user__church_id on user_ (cost=0.00..44500.34
rows=12763 width=245) (actual time=0.324..1243.348 rows=12763 loops=1)
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...
Thanks again,
--
Ksenia
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: ksenia.marasanova@gmail.com
Subject: Re: LEFT JOIN optimization
In-Reply-To: <130df19305091114477e2e581b@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