pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedJoins and full index scans...mysql vs postgres?
11+ messages / 8 participants
[nested] [flat]
* Joins and full index scans...mysql vs postgres?
@ 2006-02-22 17:26 ryan groth <postgres@cpusoftware.com>
0 siblings, 2 replies; 11+ messages in thread
From: ryan groth @ 2006-02-22 17:26 UTC (permalink / raw)
To: pgsql-performance
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, ''
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Joins and full index scans...mysql vs postgres?
@ 2006-02-22 17:52 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: ryan groth <postgres@cpusoftware.com>
1 sibling, 0 replies; 11+ messages in thread
From: Steinar H. Gunderson @ 2006-02-22 17:52 UTC (permalink / raw)
To: pgsql-performance
On Wed, Feb 22, 2006 at 12:26:47PM -0500, ryan groth wrote:
> Postgres Explain
We need to see EXPLAIN ANALYZE results here.
What's your work_mem set to?
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Joins and full index scans...mysql vs postgres?
@ 2006-02-22 18:11 ryan groth <postgres@cpusoftware.com>
0 siblings, 2 replies; 11+ messages in thread
From: ryan groth @ 2006-02-22 18:11 UTC (permalink / raw)
To: Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance
Does this work:
"Merge Left Join (cost=0.00..2656.36 rows=6528 width=1522) (actual
time=0.057..123.659 rows=6528 loops=1)"
" Merge Cond: ("outer".uid = "inner".uid)"
" -> Merge Left Join (cost=0.00..1693.09 rows=6528 width=1264)
(actual time=0.030..58.876 rows=6528 loops=1)"
" Merge Cond: ("outer".uid = "inner".user_id)"
" -> Index Scan using users_pkey on users (cost=0.00..763.81
rows=6528 width=100) (actual time=0.016..9.446 rows=6528 loops=1)"
" -> Index Scan using phorum_users_base_pkey on
phorum_users_base (cost=0.00..822.92 rows=9902 width=1168) (actual
time=0.007..15.674 rows=9845 loops=1)"
" -> Index Scan using useraux_pkey on useraux (cost=0.00..846.40
rows=7582 width=262) (actual time=0.007..11.935 rows=7529 loops=1)"
"Total runtime: 127.442 ms"
> On Wed, Feb 22, 2006 at 12:26:47PM -0500, ryan groth wrote:
> > Postgres Explain
>
> We need to see EXPLAIN ANALYZE results here.
>
> What's your work_mem set to?
>
> /* Steinar */
> --
> Homepage: http://www.sesse.net/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
--
> On Wed, Feb 22, 2006 at 12:26:47PM -0500, ryan groth wrote:
> > Postgres Explain
>
> We need to see EXPLAIN ANALYZE results here.
>
> What's your work_mem set to?
>
> /* Steinar */
> --
> Homepage: http://www.sesse.net/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
--
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Joins and full index scans...mysql vs postgres?
@ 2006-02-22 18:21 ryan groth <postgres@cpusoftware.com>
0 siblings, 0 replies; 11+ messages in thread
From: ryan groth @ 2006-02-22 18:21 UTC (permalink / raw)
To: ryan groth <postgres@cpusoftware.com>; Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance
workmem is set to the default, increasing it decreases performance.
> Does this work:
>
> "Merge Left Join (cost=0.00..2656.36 rows=6528 width=1522) (actual
> time=0.057..123.659 rows=6528 loops=1)"
> " Merge Cond: ("outer".uid = "inner".uid)"
> " -> Merge Left Join (cost=0.00..1693.09 rows=6528 width=1264)
> (actual time=0.030..58.876 rows=6528 loops=1)"
> " Merge Cond: ("outer".uid = "inner".user_id)"
> " -> Index Scan using users_pkey on users (cost=0.00..763.81
> rows=6528 width=100) (actual time=0.016..9.446 rows=6528 loops=1)"
> " -> Index Scan using phorum_users_base_pkey on
> phorum_users_base (cost=0.00..822.92 rows=9902 width=1168) (actual
> time=0.007..15.674 rows=9845 loops=1)"
> " -> Index Scan using useraux_pkey on useraux (cost=0.00..846.40
> rows=7582 width=262) (actual time=0.007..11.935 rows=7529 loops=1)"
> "Total runtime: 127.442 ms"
>
>
> > On Wed, Feb 22, 2006 at 12:26:47PM -0500, ryan groth wrote:
> > > Postgres Explain
> >
> > We need to see EXPLAIN ANALYZE results here.
> >
> > What's your work_mem set to?
> >
> > /* Steinar */
> > --
> > Homepage: http://www.sesse.net/
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
> >
> >
>
> --
>
>
> > On Wed, Feb 22, 2006 at 12:26:47PM -0500, ryan groth wrote:
> > > Postgres Explain
> >
> > We need to see EXPLAIN ANALYZE results here.
> >
> > What's your work_mem set to?
> >
> > /* Steinar */
> > --
> > Homepage: http://www.sesse.net/
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
> >
> >
>
> --
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
--
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Joins and full index scans...mysql vs postgres?
@ 2006-02-22 18:28 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: ryan groth <postgres@cpusoftware.com>
1 sibling, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2006-02-22 18:28 UTC (permalink / raw)
To: ryan groth <postgres@cpusoftware.com>; +Cc: Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance
On Wed, 22 Feb 2006, ryan groth wrote:
> Does this work:
>
> "Merge Left Join (cost=0.00..2656.36 rows=6528 width=1522) (actual
> time=0.057..123.659 rows=6528 loops=1)"
> " Merge Cond: ("outer".uid = "inner".uid)"
> " -> Merge Left Join (cost=0.00..1693.09 rows=6528 width=1264)
> (actual time=0.030..58.876 rows=6528 loops=1)"
> " Merge Cond: ("outer".uid = "inner".user_id)"
> " -> Index Scan using users_pkey on users (cost=0.00..763.81
> rows=6528 width=100) (actual time=0.016..9.446 rows=6528 loops=1)"
> " -> Index Scan using phorum_users_base_pkey on
> phorum_users_base (cost=0.00..822.92 rows=9902 width=1168) (actual
> time=0.007..15.674 rows=9845 loops=1)"
> " -> Index Scan using useraux_pkey on useraux (cost=0.00..846.40
> rows=7582 width=262) (actual time=0.007..11.935 rows=7529 loops=1)"
> "Total runtime: 127.442 ms"
Well, this implies the query took about 127 ms on the server side. Where
did the 1000 ms number come from (was that on a client, and if so, what
type)?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Joins and full index scans...mysql vs postgres?
@ 2006-02-22 18:52 ryan groth <postgres@cpusoftware.com>
0 siblings, 2 replies; 11+ messages in thread
From: ryan groth @ 2006-02-22 18:52 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; ryan groth <postgres@cpusoftware.com>; Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance
Hmm, it came from the timer on the pgadmin III sql query tool. I guess
the 1,000ms includes the round-trip? See the wierd thing is that
mysqlserver is running default configuration on a virtual machine
(P3/1.3GHZ conf'd for 128mb ram) over a 100m/b ethernet connection.
Postgres is running on a real P4/3.0ghz 4GB running localhost. Timings
from the mysql query tool indicate that the 6.5k record query runs in
"1.3346s (.3361s)" vs. the pgadmin query tool saying that the query runs
"997+3522 ms". Am I reading these numbers wrong? Are these numbers
reflective of application performance? Is there an optimization I am
missing?
Ryan
> On Wed, 22 Feb 2006, ryan groth wrote:
>
> > Does this work:
> >
> > "Merge Left Join (cost=0.00..2656.36 rows=6528 width=1522) (actual
> > time=0.057..123.659 rows=6528 loops=1)"
> > " Merge Cond: ("outer".uid = "inner".uid)"
> > " -> Merge Left Join (cost=0.00..1693.09 rows=6528 width=1264)
> > (actual time=0.030..58.876 rows=6528 loops=1)"
> > " Merge Cond: ("outer".uid = "inner".user_id)"
> > " -> Index Scan using users_pkey on users (cost=0.00..763.81
> > rows=6528 width=100) (actual time=0.016..9.446 rows=6528 loops=1)"
> > " -> Index Scan using phorum_users_base_pkey on
> > phorum_users_base (cost=0.00..822.92 rows=9902 width=1168) (actual
> > time=0.007..15.674 rows=9845 loops=1)"
> > " -> Index Scan using useraux_pkey on useraux (cost=0.00..846.40
> > rows=7582 width=262) (actual time=0.007..11.935 rows=7529 loops=1)"
> > "Total runtime: 127.442 ms"
>
> Well, this implies the query took about 127 ms on the server side. Where
> did the 1000 ms number come from (was that on a client, and if so, what
> type)?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
--
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Joins and full index scans...mysql vs postgres?
@ 2006-02-22 19:13 Scott Marlowe <smarlowe@g2switchworks.com>
parent: ryan groth <postgres@cpusoftware.com>
1 sibling, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2006-02-22 19:13 UTC (permalink / raw)
To: ryan groth <postgres@cpusoftware.com>; +Cc: Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance
On Wed, 2006-02-22 at 12:11, ryan groth wrote:
> Does this work:
>
> "Merge Left Join (cost=0.00..2656.36 rows=6528 width=1522) (actual
> time=0.057..123.659 rows=6528 loops=1)"
> " Merge Cond: ("outer".uid = "inner".uid)"
> " -> Merge Left Join (cost=0.00..1693.09 rows=6528 width=1264)
> (actual time=0.030..58.876 rows=6528 loops=1)"
> " Merge Cond: ("outer".uid = "inner".user_id)"
> " -> Index Scan using users_pkey on users (cost=0.00..763.81
> rows=6528 width=100) (actual time=0.016..9.446 rows=6528 loops=1)"
> " -> Index Scan using phorum_users_base_pkey on
> phorum_users_base (cost=0.00..822.92 rows=9902 width=1168) (actual
> time=0.007..15.674 rows=9845 loops=1)"
> " -> Index Scan using useraux_pkey on useraux (cost=0.00..846.40
> rows=7582 width=262) (actual time=0.007..11.935 rows=7529 loops=1)"
> "Total runtime: 127.442 ms"
In MySQL, have you tried writing a short perl or php script or even
timing the mysql client running in one shot mode (I assume it can do
that) from the outside to see how long it takes to actually run the
query AND retrieve the data?
My guess is most of the time for both queries will be taken in
delivering the data.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Joins and full index scans...mysql vs postgres?
@ 2006-02-22 23:18 PFC <lists@peufeu.com>
parent: ryan groth <postgres@cpusoftware.com>
1 sibling, 0 replies; 11+ messages in thread
From: PFC @ 2006-02-22 23:18 UTC (permalink / raw)
To: ryan groth <postgres@cpusoftware.com>; Stephan Szabo <sszabo@megazone.bigpanda.com>; Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance
> "997+3522 ms". Am I reading these numbers wrong? Are these numbers
> reflective of application performance? Is there an optimization I am
> missing?
It also reflects the time it takes to pgadmin to insert the results into
its GUI...
If you want to get an approximation of the time the server needs to
process your request, without the data marshalling time on the network and
anything, you can either use EXPLAIN ANALYZE (but mysql doesn't have it,
and the instrumentation adds overhead), or simply something like "SELECT
sum(1) FROM (query to benchmark)", which only returns 1 row, and the sum()
overhead is minimal, and it works on most databases. I find it useful
because in knowing which portion of the time is spent by the server
processing the query, or in data transfer, or in data decoding on the
client side, or simply in displaying...
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Joins and full index scans...mysql vs postgres?
@ 2006-02-22 23:58 Chris <dmagick@gmail.com>
parent: ryan groth <postgres@cpusoftware.com>
1 sibling, 0 replies; 11+ messages in thread
From: Chris @ 2006-02-22 23:58 UTC (permalink / raw)
To: ryan groth <postgres@cpusoftware.com>; +Cc: 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/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Joins and full index scans...mysql vs postgres?
@ 2006-02-23 02:52 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: ryan groth <postgres@cpusoftware.com>
1 sibling, 1 reply; 11+ messages in thread
From: Christopher Kings-Lynne @ 2006-02-23 02:52 UTC (permalink / raw)
To: ryan groth <postgres@cpusoftware.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance
The pgAdmin query tool is known to give an answer about 5x the real
answer - don't believe it!
ryan groth wrote:
> Hmm, it came from the timer on the pgadmin III sql query tool. I guess
> the 1,000ms includes the round-trip? See the wierd thing is that
> mysqlserver is running default configuration on a virtual machine
> (P3/1.3GHZ conf'd for 128mb ram) over a 100m/b ethernet connection.
> Postgres is running on a real P4/3.0ghz 4GB running localhost. Timings
> from the mysql query tool indicate that the 6.5k record query runs in
> "1.3346s (.3361s)" vs. the pgadmin query tool saying that the query runs
> "997+3522 ms". Am I reading these numbers wrong? Are these numbers
> reflective of application performance? Is there an optimization I am
> missing?
>
> Ryan
>
>
>> On Wed, 22 Feb 2006, ryan groth wrote:
>>
>>> Does this work:
>>>
>>> "Merge Left Join (cost=0.00..2656.36 rows=6528 width=1522) (actual
>>> time=0.057..123.659 rows=6528 loops=1)"
>>> " Merge Cond: ("outer".uid = "inner".uid)"
>>> " -> Merge Left Join (cost=0.00..1693.09 rows=6528 width=1264)
>>> (actual time=0.030..58.876 rows=6528 loops=1)"
>>> " Merge Cond: ("outer".uid = "inner".user_id)"
>>> " -> Index Scan using users_pkey on users (cost=0.00..763.81
>>> rows=6528 width=100) (actual time=0.016..9.446 rows=6528 loops=1)"
>>> " -> Index Scan using phorum_users_base_pkey on
>>> phorum_users_base (cost=0.00..822.92 rows=9902 width=1168) (actual
>>> time=0.007..15.674 rows=9845 loops=1)"
>>> " -> Index Scan using useraux_pkey on useraux (cost=0.00..846.40
>>> rows=7582 width=262) (actual time=0.007..11.935 rows=7529 loops=1)"
>>> "Total runtime: 127.442 ms"
>> Well, this implies the query took about 127 ms on the server side. Where
>> did the 1000 ms number come from (was that on a client, and if so, what
>> type)?
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Joins and full index scans...mysql vs postgres?
@ 2006-02-23 10:21 Andreas Pflug <pgadmin@pse-consulting.de>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 11+ messages in thread
From: Andreas Pflug @ 2006-02-23 10:21 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: ryan groth <postgres@cpusoftware.com>; Stephan Szabo <sszabo@megazone.bigpanda.com>; Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance
Christopher Kings-Lynne wrote:
> The pgAdmin query tool is known to give an answer about 5x the real
> answer - don't believe it!
Everybody please forget immediately the factor 5. It's no factor at all,
but the GUI update time that is *added*, which depends on rows*columns.
> ryan groth wrote:
>
>> the pgadmin query tool saying that the query runs
>> "997+3522 ms".
Means 997ms until all data is at the client (libpq reports the rowset),
the rest is GUI overhead.
Regards,
Andreas
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-02-23 10:21 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-22 17:26 Joins and full index scans...mysql vs postgres? ryan groth <postgres@cpusoftware.com>
2006-02-22 17:52 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-02-22 23:58 ` Chris <dmagick@gmail.com>
2006-02-22 18:11 Re: Joins and full index scans...mysql vs postgres? ryan groth <postgres@cpusoftware.com>
2006-02-22 18:28 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-02-22 19:13 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-02-22 18:21 Re: Joins and full index scans...mysql vs postgres? ryan groth <postgres@cpusoftware.com>
2006-02-22 18:52 Re: Joins and full index scans...mysql vs postgres? ryan groth <postgres@cpusoftware.com>
2006-02-22 23:18 ` PFC <lists@peufeu.com>
2006-02-23 02:52 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-02-23 10:21 ` Andreas Pflug <pgadmin@pse-consulting.de>
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