Skip site navigation (1) Skip section navigation (2)

Re: Joins and full index scans...mysql vs postgres?

From: Chris <dmagick(at)gmail(dot)com>
To: ryan groth <postgres(at)cpusoftware(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Joins and full index scans...mysql vs postgres?
Date: 2006-02-22 23:58:39
Message-ID: 43FCFAAF.1090404@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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/

In response to

pgsql-performance by date

Next:From: Christopher Kings-LynneDate: 2006-02-23 02:52:11
Subject: Re: Joins and full index scans...mysql vs postgres?
Previous:From: PFCDate: 2006-02-22 23:18:58
Subject: Re: Joins and full index scans...mysql vs postgres?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group