Re: UNION causes horrible plan on JOIN

At Mon, 28 Oct 2019 16:30:24 -0700, Craig James <cjames@emolecules.com> wrote in 
> On Mon, Oct 28, 2019 at 3:45 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> 
> > On Mon, Oct 28, 2019 at 03:40:58PM -0700, Craig James wrote:
> > > On Postgres 9.6 (config below), I have a case I don't understand: three
> > > tables that can be separately queried in milliseconds, but when put
> > > together into one view using UNION, take 150 seconds to query. Here's the
> > > rough idea (actual details below):
> >
> > Do you want UNION ALL ?
> >
> > UNION without ALL distintifies the output.
> > https://www.postgresql.org/docs/current/sql-select.html#SQL-UNION
> 
> 
> Interesting idea, thanks. But it makes no difference. Tried it and got the
> same bad performance.

The join clauses in the view also prevent the query from getting
faster plans. So if you somehow could move the join clauses out of the
UNION leafs in the view in addtion to using UNION ALL, you would get
better performance.

Or if hitlist_rows is known to highly narrow the result from the
element tables, using a function instead of the view might work.

create or replace function the_view(int)
returns table(thesaurus_id int, version_id int, normalized int,
              identifier int, typecode int) as $$
select
  rt.thesaurus_id,
  rt.version_id,
  rt.normalized,
  rt.identifier,
  rt.typecode
  from local_sample s
  join thesaurus_master rt using (sample_id)
  where rt.thesaurus_id = $1
union
...
$$ language sql;

explain analyze select c.version_id
from hitlist_rows_103710241 h,
lateral the_view(h.objectid) as c;

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center