From: | Rich Doughty <rich(at)opusvl(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Outer join query plans and performance |
Date: | 2005-10-24 12:42:16 |
Message-ID: | 435CD6A8.2060208@opusvl.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm having some significant performance problems with left join. Can
anyone give me any pointers as to why the following 2 query plans are so
different?
EXPLAIN SELECT *
FROM
tokens.ta_tokens t LEFT JOIN
tokens.ta_tokenhist h1 ON t.token_id = h1.token_id LEFT JOIN
tokens.ta_tokenhist h2 ON t.token_id = h2.token_id
WHERE
h1.histdate = 'now';
Nested Loop Left Join (cost=0.00..68778.43 rows=2215 width=1402)
-> Nested Loop (cost=0.00..55505.62 rows=2215 width=714)
-> Index Scan using idx_tokenhist__histdate on ta_tokenhist h1 (cost=0.00..22970.70 rows=5752 width=688)
Index Cond: (histdate = '2005-10-24 13:28:38.411844'::timestamp without time zone)
-> Index Scan using ta_tokens_pkey on ta_tokens t (cost=0.00..5.64 rows=1 width=26)
Index Cond: ((t.token_id)::integer = ("outer".token_id)::integer)
-> Index Scan using fkx_tokenhist__tokens on ta_tokenhist h2 (cost=0.00..5.98 rows=1 width=688)
Index Cond: (("outer".token_id)::integer = (h2.token_id)::integer)
Performance is fine for this one and the plan is pretty much as i'd
expect.
This is where i hit a problem.
EXPLAIN SELECT *
FROM
tokens.ta_tokens t LEFT JOIN
tokens.ta_tokenhist h1 ON t.token_id = h1.token_id LEFT JOIN
tokens.ta_tokenhist h2 ON t.token_id = h2.token_id
WHERE
h2.histdate = 'now';
Hash Join (cost=1249148.59..9000709.22 rows=2215 width=1402)
Hash Cond: (("outer".token_id)::integer = ("inner".token_id)::integer)
-> Hash Left Join (cost=1225660.51..8181263.40 rows=4045106 width=714)
Hash Cond: (("outer".token_id)::integer = ("inner".token_id)::integer)
-> Seq Scan on ta_tokens t (cost=0.00..71828.06 rows=4045106 width=26)
-> Hash (cost=281243.21..281243.21 rows=10504921 width=688)
-> Seq Scan on ta_tokenhist h1 (cost=0.00..281243.21 rows=10504921 width=688)
-> Hash (cost=22970.70..22970.70 rows=5752 width=688)
-> Index Scan using idx_tokenhist__histdate on ta_tokenhist h2 (cost=0.00..22970.70 rows=5752 width=688)
Index Cond: (histdate = '2005-10-24 13:34:51.371905'::timestamp without time zone)
I would understand if h2 was joined on h1, but it isn't. It only joins
on t. can anyone give any tips on improving the performance of the second
query (aside from changing the join order manually)?
Thanks
--
- Rich Doughty
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Doughty | 2005-10-24 12:58:18 | Re: Outer join query plans and performance |
Previous Message | Florian Ledoux | 2005-10-24 12:29:24 | pg_dump, MVCC and consistency |