From: "ryan groth" <postgres@cpusoftware.com>
To: pgsql-performance@postgresql.org
Subject: Joins and full index scans...mysql vs postgres?
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Message-Id: <E1FBxlH-0003Kt-9V@zeus.liquidweb.com>
Date: Wed, 22 Feb 2006 12:26:47 -0500

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;

The joins are all on the PKs of the tables. It takes 1000ms to run on
postgres. The identical mysql version runs in 230ms. The problem seems
to stem from postgres's insistence to do three complete table scans,
where mysql does one and joins 1:1 against the results of the first. I
have switched the joins to inner joins and the difference is negligible.
Here are the explains on both postgres and mysql. Is there a way to
optimize this basic query for postgres that I am missing?

Postgres Explain 

Merge Left Join  (cost=0.00..2656.36 rows=6528 width=1522)
Merge Cond: ("outer".uid = "inner".uid)
  ->  Merge Left Join  (cost=0.00..1693.09 rows=6528 width=1264)
        Merge Cond: ("outer".uid = "inner".user_id)
        ->  Index Scan using users_pkey on users  (cost=0.00..763.81
rows=6528 width=100)
        ->  Index Scan using phorum_users_base_pkey on phorum_users_base
 (cost=0.00..822.92 rows=9902 width=1168)
  ->  Index Scan using useraux_pkey on useraux  (cost=0.00..846.40
rows=7582 width=262)


MySQL Explain:

id,select_type,table,possible_keys,key,key_len,ref,rows,extra
1, 'PRIMARY', 'USERS', 'ALL', '', '', '', '', 6528, ''
1, 'PRIMARY', 'phorum_users_base', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
'wh2o.USERS.UID', 1, ''
1, 'PRIMARY', 'useraux', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
'wh2o.USERS.UID', 1, ''