Re: 2 left joins causes seqscan

Willy-Bas Loos <willybas@gmail.com> wrote:

> As you can see, the second query is far more efficient, even
> though it scans both tables twice to combine the results.

But the two queries don't return the same results.  Of course the
second one will be faster.  The simple equivalent of your second
query is:

explain analyze select a.field1, b.title
  from a
  join b on b.id = a.id
  where lower(b.title) like 'abcd%'
    and lang in (1, 2);

The equivalent of your first query is to take the result sets from
these two queries:

select a1.field1, b1.title, b2.title
  from a a1
  join b b1 on b1.id = a1.id and b1.lang = 1
  left join b b2 on (b2.id = a1.id and b2.lang = 2)
  where lower(b1.title) like'abcd%'
union
select a2.field1, b4.title, b3.title
  from a a2
  join b b3 on b3.id = a2.id and b3.lang = 2
  left join b b4 on (b4.id = a2.id and b4.lang = 1)
  where lower(b3.title) like'abcd%';

The above form does optimize better than the original, but it's not
too surprising that the planner can't come up with the optimal
plan; you've posed quite a challenge for it.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company