pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMany to many join seems slow?
7+ messages / 4 participants
[nested] [flat]
* Many to many join seems slow?
@ 2007-05-15 13:57 Drew Wilson <drewmwilson@gmail.com>
2007-05-15 14:05 ` Re: Many to many join seems slow? Alvaro Herrera <alvherre@commandprompt.com>
2007-05-15 16:00 ` Re: Many to many join seems slow? Daniel Cristian Cruz <danielcristian@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Drew Wilson @ 2007-05-15 13:57 UTC (permalink / raw)
To: pgsql-performance
I'm trying to debug a query that gets all the French translations for
all US string values. Ultimately, my goal is to rank them all by edit
distance, and only pick the top N.
However, I cannot get the basic many-to-many join to return all the
results in less than 3 seconds, which seems slow to me. (My
competition is an in-memory perl hash that runs on client machines
providing results in around 3 seconds, after a 30 second startup time.)
The simplified schema is :
source ->> translation_pair <<- translation
The keys are all sequence generated oids. I do wonder if the
performance would be better if I used the string values as keys to
get better data distribution. Would this help speed up performance?
There are 159283 rows in source
There are 1723935 rows in translation, of which 159686 are French
=# explain SELECT s.source_id, s.value AS sourceValue, t.value AS
translationValue
FROM
source s,
translation_pair tp,
translation t,
language l
WHERE
s.source_id = tp.source_id
AND tp.translation_id = t.translation_id
AND t.language_id = l.language_id
AND l.name = 'French' ;
QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------------
Merge Join (cost=524224.49..732216.29 rows=92447 width=97)
Merge Cond: (tp.source_id = s.source_id)
-> Sort (cost=524224.49..524455.60 rows=92447 width=55)
Sort Key: tp.source_id
-> Nested Loop (cost=1794.69..516599.30 rows=92447 width=55)
-> Nested Loop (cost=1794.69..27087.87 rows=86197
width=55)
-> Index Scan using language_name_key on
"language" l (cost=0.00..8.27 rows=1 width=4)
Index Cond: ((name)::text = 'French'::text)
-> Bitmap Heap Scan on translation t
(cost=1794.69..25882.43 rows=95774 width=59)
Recheck Cond: (t.language_id =
l.language_id)
-> Bitmap Index Scan on
translation_language_l_key (cost=0.00..1770.74 rows=95774 width=0)
Index Cond: (t.language_id =
l.language_id)
-> Index Scan using translation_pair_translation_id
on translation_pair tp (cost=0.00..5.67 rows=1 width=8)
Index Cond: (tp.translation_id = t.translation_id)
-> Index Scan using source_pkey on source s
(cost=0.00..206227.65 rows=159283 width=46)
(15 rows)
I'm running Postgres 8.2.3 on latest Mac OSX 10.4.x. The CPU is a
3Ghz Dual-Core Intel Xeon, w/ 5G ram. The drive is very fast although
I don't know the configuration (I think its an XRaid w/ 3 SAS/SCSI
70G Seagate drives).
The regular performance configurable values are:
work_mem 32MB
shared_buffers 32MB
max_fsm_pages 204800
max_fsm_relations 1000
Thanks for any advice,
Drew
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Many to many join seems slow?
2007-05-15 13:57 Many to many join seems slow? Drew Wilson <drewmwilson@gmail.com>
@ 2007-05-15 14:05 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-05-15 14:11 ` Re: Many to many join seems slow? Drew Wilson <drewmwilson@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Alvaro Herrera @ 2007-05-15 14:05 UTC (permalink / raw)
To: Drew Wilson <drewmwilson@gmail.com>; +Cc: pgsql-performance
Drew Wilson escribió:
> =# explain SELECT s.source_id, s.value AS sourceValue, t.value AS
> translationValue
> FROM
> source s,
> translation_pair tp,
> translation t,
> language l
> WHERE
> s.source_id = tp.source_id
> AND tp.translation_id = t.translation_id
> AND t.language_id = l.language_id
> AND l.name = 'French' ;
Please provide an EXPLAIN ANALYZE of the query.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Many to many join seems slow?
2007-05-15 13:57 Many to many join seems slow? Drew Wilson <drewmwilson@gmail.com>
2007-05-15 14:05 ` Re: Many to many join seems slow? Alvaro Herrera <alvherre@commandprompt.com>
@ 2007-05-15 14:11 ` Drew Wilson <drewmwilson@gmail.com>
2007-05-15 14:17 ` Re: Many to many join seems slow? Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Drew Wilson @ 2007-05-15 14:11 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-performance
> Please provide an EXPLAIN ANALYZE of the query.
Oops, sorry about that.
=# EXPLAIN ANALYZE SELECT s.source_id, s.value as sourceValue,
t.value as translationValue
-# FROM
-# source s,
-# translation_pair tp,
-# translation t,
-# language l
-# WHERE
-# s.source_id = tp.source_id
-# AND tp.translation_id = t.translation_id
-# AND t.language_id = l.language_id
-# AND l.name = 'French' ;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
----------------------------
Merge Join (cost=524224.49..732216.29 rows=92447 width=97) (actual
time=1088.871..1351.840 rows=170759 loops=1)
Merge Cond: (tp.source_id = s.source_id)
-> Sort (cost=524224.49..524455.60 rows=92447 width=55) (actual
time=1088.774..1113.483 rows=170759 loops=1)
Sort Key: tp.source_id
-> Nested Loop (cost=1794.69..516599.30 rows=92447
width=55) (actual time=23.252..929.847 rows=170759 loops=1)
-> Nested Loop (cost=1794.69..27087.87 rows=86197
width=55) (actual time=23.194..132.139 rows=159686 loops=1)
-> Index Scan using language_name_key on
"language" l (cost=0.00..8.27 rows=1 width=4) (actual
time=0.030..0.031 rows=1 loops=1)
Index Cond: ((name)::text = 'French'::text)
-> Bitmap Heap Scan on translation t
(cost=1794.69..25882.43 rows=95774 width=59) (actual
time=23.155..95.227 rows=159686 loops=1)
Recheck Cond: (t.language_id =
l.language_id)
-> Bitmap Index Scan on
translation_language_l_key (cost=0.00..1770.74 rows=95774 width=0)
(actual time=22.329..22.329 rows=159686 loops=1)
Index Cond: (t.language_id =
l.language_id)
-> Index Scan using translation_pair_translation_id
on translation_pair tp (cost=0.00..5.67 rows=1 width=8) (actual
time=0.004..0.004 rows=1 loops=159686)
Index Cond: (tp.translation_id = t.translation_id)
-> Index Scan using source_pkey on source s
(cost=0.00..206227.65 rows=159283 width=46) (actual
time=0.086..110.564 rows=186176 loops=1)
Total runtime: 1366.757 ms
(16 rows)
On May 15, 2007, at 7:05 AM, Alvaro Herrera wrote:
> Drew Wilson escribió:
>
>> =# explain SELECT s.source_id, s.value AS sourceValue, t.value AS
>> translationValue
>> FROM
>> source s,
>> translation_pair tp,
>> translation t,
>> language l
>> WHERE
>> s.source_id = tp.source_id
>> AND tp.translation_id = t.translation_id
>> AND t.language_id = l.language_id
>> AND l.name = 'French' ;
>
> Please provide an EXPLAIN ANALYZE of the query.
>
> --
> Alvaro Herrera http://
> www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Many to many join seems slow?
2007-05-15 13:57 Many to many join seems slow? Drew Wilson <drewmwilson@gmail.com>
2007-05-15 14:05 ` Re: Many to many join seems slow? Alvaro Herrera <alvherre@commandprompt.com>
2007-05-15 14:11 ` Re: Many to many join seems slow? Drew Wilson <drewmwilson@gmail.com>
@ 2007-05-15 14:17 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-05-15 16:45 ` Re: Many to many join seems slow? Drew Wilson <drewmwilson@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Heikki Linnakangas @ 2007-05-15 14:17 UTC (permalink / raw)
To: Drew Wilson <drewmwilson@gmail.com>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-performance
Drew Wilson wrote:
> Merge Join (cost=524224.49..732216.29 rows=92447 width=97) (actual
> time=1088.871..1351.840 rows=170759 loops=1)
> ...
> Total runtime: 1366.757 ms
It looks like the query actual runs in less than 3 seconds, but it takes
some time to fetch 170759 rows to the client.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Many to many join seems slow?
2007-05-15 13:57 Many to many join seems slow? Drew Wilson <drewmwilson@gmail.com>
2007-05-15 14:05 ` Re: Many to many join seems slow? Alvaro Herrera <alvherre@commandprompt.com>
2007-05-15 14:11 ` Re: Many to many join seems slow? Drew Wilson <drewmwilson@gmail.com>
2007-05-15 14:17 ` Re: Many to many join seems slow? Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-05-15 16:45 ` Drew Wilson <drewmwilson@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Drew Wilson @ 2007-05-15 16:45 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-performance
You're right. If I redirect output to /dev/null, the query completes
in 1.4s.
# \o /dev/null
# SELECT s.source_id, s.value as sourceValue, t.value as
translationValue...
...
Time: 1409.557 ms
#
That'll do for now.
Thanks,
Drew
On May 15, 2007, at 7:17 AM, Heikki Linnakangas wrote:
> Drew Wilson wrote:
>> Merge Join (cost=524224.49..732216.29 rows=92447 width=97)
>> (actual time=1088.871..1351.840 rows=170759 loops=1)
>> ...
>> Total runtime: 1366.757 ms
>
> It looks like the query actual runs in less than 3 seconds, but it
> takes some time to fetch 170759 rows to the client.
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Many to many join seems slow?
2007-05-15 13:57 Many to many join seems slow? Drew Wilson <drewmwilson@gmail.com>
@ 2007-05-15 16:00 ` Daniel Cristian Cruz <danielcristian@gmail.com>
2007-05-15 16:43 ` Re: Many to many join seems slow? Drew Wilson <drewmwilson@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Daniel Cristian Cruz @ 2007-05-15 16:00 UTC (permalink / raw)
To: Drew Wilson <drewmwilson@gmail.com>; +Cc: pgsql-performance
2007/5/15, Drew Wilson <drewmwilson@gmail.com>:
> =# explain SELECT s.source_id, s.value AS sourceValue, t.value AS
> translationValue
> FROM
> source s,
> translation_pair tp,
> translation t,
> language l
> WHERE
> s.source_id = tp.source_id
> AND tp.translation_id = t.translation_id
> AND t.language_id = l.language_id
> AND l.name = 'French' ;
>
> QUERY PLAN
> ------------------------------------------------------------------------
> -----------------------------------------------------
> Merge Join (cost=524224.49..732216.29 rows=92447 width=97)
This way you get all word matches for the French language. Shouldn't
it be all matches for a specific word (s.value = 'word' in WHERE)?
--
Daniel Cristian Cruz
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Many to many join seems slow?
2007-05-15 13:57 Many to many join seems slow? Drew Wilson <drewmwilson@gmail.com>
2007-05-15 16:00 ` Re: Many to many join seems slow? Daniel Cristian Cruz <danielcristian@gmail.com>
@ 2007-05-15 16:43 ` Drew Wilson <drewmwilson@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Drew Wilson @ 2007-05-15 16:43 UTC (permalink / raw)
To: Daniel Cristian Cruz <danielcristian@gmail.com>; +Cc: pgsql-performance
Yes, I'll be filtering by string value. However, I just wanted to see
how long it takes to scan all translations in a particular language.
Drew
On May 15, 2007, at 9:00 AM, Daniel Cristian Cruz wrote:
> 2007/5/15, Drew Wilson <drewmwilson@gmail.com>:
>> =# explain SELECT s.source_id, s.value AS sourceValue, t.value AS
>> translationValue
>> FROM
>> source s,
>> translation_pair tp,
>> translation t,
>> language l
>> WHERE
>> s.source_id = tp.source_id
>> AND tp.translation_id = t.translation_id
>> AND t.language_id = l.language_id
>> AND l.name = 'French' ;
>>
>> QUERY PLAN
>> ---------------------------------------------------------------------
>> ---
>> -----------------------------------------------------
>> Merge Join (cost=524224.49..732216.29 rows=92447 width=97)
>
> This way you get all word matches for the French language. Shouldn't
> it be all matches for a specific word (s.value = 'word' in WHERE)?
>
> --
> Daniel Cristian Cruz
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-05-15 16:45 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-15 13:57 Many to many join seems slow? Drew Wilson <drewmwilson@gmail.com>
2007-05-15 14:05 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-05-15 14:11 ` Drew Wilson <drewmwilson@gmail.com>
2007-05-15 14:17 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-05-15 16:45 ` Drew Wilson <drewmwilson@gmail.com>
2007-05-15 16:00 ` Daniel Cristian Cruz <danielcristian@gmail.com>
2007-05-15 16:43 ` Drew Wilson <drewmwilson@gmail.com>
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