Обсуждение: Many to many join seems slow?
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
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.
> 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.
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
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
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
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