Re: Performance of full outer join in 8.3

2009/4/15 Christian Schröder <cs(at)deriva(dot)de>:
> Grzegorz Jaśkiewicz wrote:
>>
>> set work_mem=24000; before running the query.
>>
>> postgres is doing merge and sort on disc, that's always slow.
>>
>
> Ok, but why is the plan different in 8.2? As you can see the same query is
> really fast in 8.2, but slow in 8.3.

Did that set help ?

I think Tom will know more about it, but probably (and I am guessing
here, to be honest) - Materialize plan wasn't either available, or
didn't appear too be a planners favourite.
on 8.2 the two loops instead were were much faster.

Can you try increasing stat target to 100, vacuum analyze and see if
different plan is choosen ?

Again, I don't know at that point why is it so - just trying to
suggests things that I would try .

>> is there an index on column isin ?
>>
>
> There is a separate index on the isin column of the attachment_isins table
> (attachment_isins_isin_idx). The other table (rec_isins) has the combination
> of attachment and isin as primary key which creates an implicit index. Can
> this index be used for the single column isin? And again: Why doesn't this
> matter in 8.2??

well, it is a different major release, and differences between
8.2->8.3 are vast.

--
GJ