pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Drew Wilson <drewmwilson@gmail.com>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Many to many join seems slow?
Date: Tue, 15 May 2007 07:11:40 -0700
Message-ID: <2FD25590-AAE3-47AE-9E75-42D1361C38E2@gmail.com> (raw )
In-Reply-To: <20070515140505.GF6298@alvh.no-ip.org >
References: <49735678-25F5-48AA-B464-0864BAE56B55@gmail.com >
<20070515140505.GF6298@alvh.no-ip.org >
> 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.
view thread (7+ messages) latest in thread
Message-ID: <2FD25590-AAE3-47AE-9E75-42D1361C38E2@gmail.com>
Permalink: ../2FD25590-AAE3-47AE-9E75-42D1361C38E2@gmail.com/
Also on: postgresql.org/message-id/2FD25590-AAE3-47AE-9E75-42D1361C38E2@gmail.com
copy link · copy postgr.es
reply 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: drewmwilson@gmail.com, alvherre@commandprompt.com
Subject: Re: Many to many join seems slow?
In-Reply-To: <2FD25590-AAE3-47AE-9E75-42D1361C38E2@gmail.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