Hi!
I found something strange with merge join. Let there are two table
(http://www.sigaev.ru/misc/ex.sql.gz, 360Kb) t1 and t2, both without indexes.
Query is:
UPDATE
t1
SET
f1 = t1.f1 || t2.f1
FROM
t2
WHERE
t2.f1 = t1.f1 AND
t2.f2 = t1.f2 AND
t2.f3 = t1.f3 AND
t2.f4 =
I found something strange with merge join. Let there are two table
Sorry, postgresql's version is 8.4 from today CVS
--
Teodor Sigaev E-mail: teo...@sigaev.ru
WWW: http://www.sigaev.ru/
--
Sent via
Teodor Sigaev wrote:
I found something strange with merge join. Let there are two table
Sorry, postgresql's version is 8.4 from today CVS
For what it's worth - 8.4.0 gives as expected.
aap=# explain UPDATE
t1
SET
f1 = t1.f1 || t2.f1
FROM
t2
WHERE
t2.f1 = t1.f1 AND
t2.f2 =
Teodor Sigaev teo...@sigaev.ru writes:
I found something strange with merge join. Let there are two table
Sorry, postgresql's version is 8.4 from today CVS
Can't reproduce it here, either in HEAD or 8.4. Sure you have a clean
build with no local modifications? The outright-incorrect last
Can't reproduce it here, either in HEAD or 8.4. Sure you have a clean
build with no local modifications? The outright-incorrect last plan
Sure, just checkout'ed sources. And it's reproduced on current HEAD right now.
More info:
postgresql.conf changes:
shared_buffers=128MB
On Fri, 19 Feb 2010, Tom Lane wrote:
Teodor Sigaev teo...@sigaev.ru writes:
I found something strange with merge join. Let there are two table
Sorry, postgresql's version is 8.4 from today CVS
Can't reproduce it here, either in HEAD or 8.4. Sure you have a clean
build with no local
Poking a bit deeper, it *does* think the plan with sorts is cheaper than
without. The mergejoin plan it really prefers is:
regression=# set enable_hashjoin TO 0;
SET
regression=# set enable_nestloop TO 0;
SET
regression=# explain ...
QUERY PLAN
I wrote:
However, even given that, it's odd that it prefers a plan with two sorts
to a plan with one materialize. Poking around in costsize.c, I think
that the reason for this is that the rescan cost of a sort is estimated
at cpu_operator_cost per tuple, whereas rescanning a materialize node
