X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 1AFB99DC89B for ; Wed, 22 Feb 2006 19:58:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04621-01 for ; Wed, 22 Feb 2006 19:58:46 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.202]) by postgresql.org (Postfix) with ESMTP id 8651F9DC868 for ; Wed, 22 Feb 2006 19:58:41 -0400 (AST) Received: by zproxy.gmail.com with SMTP id i11so1555229nzh for ; Wed, 22 Feb 2006 15:58:44 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=oZ3aMtcYZWBMA2SqGMFmAnkSBomE3FZ7HTwFHmvsRdPZnK+FGeP5QW5uoHX/V1yEvcTsYWqq8R7hDIUQxs+0EQfHmCB44IR45DH1Normq1lVmwqqjX2JQNlSiQEc6EJoT7aVtp5uP9kZnSDEJkrCe+N10vAuqWuaFdXo9/DS93g= Received: by 10.36.157.19 with SMTP id f19mr5665694nze; Wed, 22 Feb 2006 15:58:43 -0800 (PST) Received: from ?10.1.1.23? ( [203.217.18.65]) by mx.gmail.com with ESMTP id 37sm1263416nzf.2006.02.22.15.58.42; Wed, 22 Feb 2006 15:58:43 -0800 (PST) Message-ID: <43FCFAAF.1090404@gmail.com> Date: Thu, 23 Feb 2006 10:58:39 +1100 From: Chris User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: ryan groth CC: pgsql-performance@postgresql.org Subject: Re: Joins and full index scans...mysql vs postgres? References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.124 required=5 tests=[AWL=0.124] X-Spam-Score: 0.124 X-Spam-Level: X-Archive-Number: 200602/358 X-Sequence-Number: 17344 ryan groth wrote: > I am issing a query like this: > SELECT * > FROM users users > LEFT JOIN phorum_users_base ON users.uid = phorum_users_base.user_id > LEFT JOIN useraux ON useraux.uid = users.uid; > I'm not sure if postgres would rewrite your query to do the joins properly, though I guess someone else might've already suggested this :) I'm probably wrong but I read that as: join users -> phorum_users_base (ON users.uid = phorum_users_base.user_id) join phorum_users_base -> useraux (ON useraux.uid = users.uid) which won't be indexable because u.uid doesn't exist in phorum_users_base. Try SELECT * FROM users users LEFT JOIN phorum_users_base ON users.uid = phorum_users_base.user_id LEFT JOIN useraux ON useraux.uid = phorum_users_base.user_id or SELECT * FROM users u, phorum_users_base pub, useraux ua WHERE u.uid = pub.user_id AND au.uid = u.uid AND pub.user_id=au.uid; -- Postgresql & php tutorials http://www.designmagick.com/