From: ryan groth <postgres@cpusoftware.com>
To: pgsql-performance@postgresql.org
Subject: Joins and full index scans...mysql vs postgres?
Date: Wed, 22 Feb 2006 12:26:47 -0500
Message-ID: <E1FBxlH-0003Kt-9V@zeus.liquidweb.com> (raw)
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, ''
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: postgres@cpusoftware.com
Subject: Re: Joins and full index scans...mysql vs postgres?
In-Reply-To: <E1FBxlH-0003Kt-9V@zeus.liquidweb.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