X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B5CE9D7EBD for ; Sun, 11 Sep 2005 18:48:00 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 49044-06 for ; Sun, 11 Sep 2005 21:47:55 +0000 (GMT) Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.193]) by svr1.postgresql.org (Postfix) with ESMTP id 9F7DDD7EE6 for ; Sun, 11 Sep 2005 18:47:55 -0300 (ADT) Received: by xproxy.gmail.com with SMTP id i31so2249314wxd for ; Sun, 11 Sep 2005 14:47:57 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=SBtm8ycA1++3fuXzars5+UvABHuGuHKwN8kXVycndeO4DF8l1mmMmbAqa+NufCack5Zqf5bH5NLWBKoyIPECc1LAewqpD2b/pTa2TaTLOKV6+VNQ/9s93jXxJke0kPXo0SOzeP/IGs4ZHViN2TUrsTVQZH7rSq9klruDIah/vOs= Received: by 10.70.96.19 with SMTP id t19mr63297wxb; Sun, 11 Sep 2005 14:47:57 -0700 (PDT) Received: by 10.70.100.7 with HTTP; Sun, 11 Sep 2005 14:47:57 -0700 (PDT) Message-ID: <130df19305091114477e2e581b@mail.gmail.com> Date: Mon, 12 Sep 2005 00:47:57 +0300 From: Ksenia Marasanova Reply-To: ksenia.marasanova@gmail.com To: pgsql-performance@postgresql.org Subject: Re: LEFT JOIN optimization In-Reply-To: <20050911210035.GC6026@ns.snowman.net> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <130df19305091110125a2d174f@mail.gmail.com> <20050911210035.GC6026@ns.snowman.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.482 required=5 tests=[AWL=0.458, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200509/132 X-Sequence-Number: 14440 2005/9/12, Stephen Frost : > * Ksenia Marasanova (ksenia.marasanova@gmail.com) wrote: > > Any tips are greatly appreciated. >=20 > EXPLAIN ANALYZE of the same queries would be much more useful. Thanks, here it is: test=3D# explain analyze select * from user_ left join church on user_.church_id =3D church.id; QUERY PLAN ---------------------------------------------------------------------------= -------------------------------------------- Hash Left Join (cost=3D6.44..7626.69 rows=3D12763 width=3D325) (actual time=3D388.573..2016.929 rows=3D12763 loops=3D1) Hash Cond: ("outer".church_id =3D "inner".id) -> Seq Scan on user_ (cost=3D0.00..7430.63 rows=3D12763 width=3D245) (actual time=3D360.431..1120.012 rows=3D12763 loops=3D1) -> Hash (cost=3D5.75..5.75 rows=3D275 width=3D80) (actual time=3D27.985..27.985 rows=3D0 loops=3D1) -> Seq Scan on church (cost=3D0.00..5.75 rows=3D275 width=3D80) (actual time=3D0.124..26.953 rows=3D275 loops=3D1) Total runtime: 2025.946 ms (6 rows) test=3D# set enable_seqscan=3D'false'; SET test=3D# explain analyze select * from user_ left join church on user_.church_id =3D church.id; =20 QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------------------------- Merge Right Join (cost=3D0.00..44675.77 rows=3D12763 width=3D325) (actual time=3D0.808..2119.099 rows=3D12763 loops=3D1) Merge Cond: ("outer".id =3D "inner".church_id) -> Index Scan using chirch_pkey on church (cost=3D0.00..17.02 rows=3D275 width=3D80) (actual time=3D0.365..5.471 rows=3D275 loops=3D1) -> Index Scan using user__church_id on user_ (cost=3D0.00..44500.34 rows=3D12763 width=3D245) (actual time=3D0.324..1243.348 rows=3D12763 loops= =3D1) 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, --=20 Ksenia