From: Chris <dmagick@gmail.com>
To: ryan groth <postgres@cpusoftware.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Joins and full index scans...mysql vs postgres?
Date: Thu, 23 Feb 2006 10:58:39 +1100
Message-ID: <43FCFAAF.1090404@gmail.com> (raw)
In-Reply-To: <E1FBxlH-0003Kt-9V@zeus.liquidweb.com>
References: <E1FBxlH-0003Kt-9V@zeus.liquidweb.com>
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/
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: dmagick@gmail.com, postgres@cpusoftware.com
Subject: Re: Joins and full index scans...mysql vs postgres?
In-Reply-To: <43FCFAAF.1090404@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