Re: Many to many join seems slow? - Mailing list pgsql-performance
From | Drew Wilson |
---|---|
Subject | Re: Many to many join seems slow? |
Date | |
Msg-id | 2FD25590-AAE3-47AE-9E75-42D1361C38E2@gmail.com Whole thread Raw |
In response to | Re: Many to many join seems slow? (Alvaro Herrera <alvherre@commandprompt.com>) |
Responses |
Re: Many to many join seems slow?
(Heikki Linnakangas <heikki@enterprisedb.com>)
|
List | 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.
pgsql-performance by date: