Re: v7.2.3 versus v7.3 -> huge performance penalty for JOIN with UNION

"ir. F.T.M. van Vugt bc." <ftm(dot)van(dot)vugt(at)foxi(dot)nl> writes:
> The query below runs 10-20 times slower under v7.3 than it did under v7.2.3:

I don't suppose you have explain output for it from 7.2.3?

It seems strange to me that the thing is picking a nestloop join here.
Either merge or hash would make more sense ... oh, but wait:

> inner join creditor c
> on foo.dflt_creditor_id = c.old_creditor_id

> * foo.dflt_creditor_id is of type varchar(20)
> * c.old_creditor_id is of type text

IIRC, merge and hash only work on plain Vars --- the implicit type
coercion from varchar to text is what's putting the kibosh on a more
intelligent join plan. Can you fix your table declarations to agree
on the datatype? If you don't want to change the tables, another
possibility is something like

select foo.*, c.id from
(select *, dflt_creditor_id::text as key, 't' from lijst01_table union all
select *, dflt_creditor_id::text as key, 't' from lijst02_table union all
select *, dflt_creditor_id::text as key, 'f' from lijst03_table union all
select *, dflt_creditor_id::text as key, 'f' from lijst04_table union all
select *, dflt_creditor_id::text as key, 't' from lijst04b_table ) as foo
inner join creditor c
on foo.key = c.old_creditor_id
order by old_id;

ie, force the type coercion to occur down inside the union, not at the
join.

This doesn't explain the slowdown from 7.2.3, though --- it had the same
deficiency. (I am hoping to get around to fixing it for 7.4.)

It could easy be that --enable-locale explains the slowdown. Are you
running 7.4 in C locale, or something else? Comparisons in locales
like en_US can be *way* slower than in C locale. You can use
pg_controldata to check this for sure.

regards, tom lane