UNION causes horrible plan on JOIN

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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):

create view thesaurus as
            (select id, name from A)
  union (select id, name from B)
  union (select id, name from C);

create table h(i integer);
insert into h values(12345);
select * from thesaurus join h on (thesaurus.id = h.id);

On the other hand, if you do this, it's a millisecond plan:

select * from thesaurus where id in (12345);

Notice that it's effectively the same query since h above contains just this one value.

Here are the actual details. The view being queried:

create view thesaurus2 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)
union
select c.id as thesaurus_id,
  c.id as version_id,
  c.cas_number as normalized,
  c.cas_number as identifier,
  3 as typecode
  from cas_number c
  join sample s on c.id = s.version_id
union
select m.id as thesaurus_id,
  m.id as version_id,
  lower(m.mfcd) as normalized,
  m.mfcd as identifier,
  4 as typecode
  from mfcd m
  join sample s on m.id = s.version_id;

The bad sort (147 seconds to execute). Note that the "hitlist" table contains exactly one row.
 explain analyze select c.version_id
   from thesaurus2 c
   join hitlist_rows_103710241 h on (c.thesaurus_id = h.objectid);
https://explain.depesz.com/s/5oRC

If I instead just query directly for that value, the answer is almost instant (1.2 msec):

explain analyze select c.version_id
from thesaurus2 c
where c.version_id in (1324511991);
https://explain.depesz.com/s/EktF

Now if I take any one of the three tables in the UNION view, the query is really fast on each one. For example:

select distinct c.version_id
from (
select distinct c.id as thesaurus_id,
  c.id as version_id,
  c.cas_number as normalized,
  c.cas_number as identifier,
  3 as typecode
  from cas_number c
  join sample s on c.id = s.version_id
) c
join hitlist_rows_103710241 h on (c.thesaurus_id = h.objectid);

https://explain.depesz.com/s/KJUZ

The other two subqueries are similarly fast.

This is Postgres9.6 running on Ubuntu 16.04, 64GB memory 16 CPUs. Non-default config values:

max_connections = 2000
shared_buffers = 12073MB
work_mem = 256MB
maintenance_work_mem = 512MB
synchronous_commit = off
effective_cache_size = 32GB
wal_level = logical
wal_keep_segments = 1000
max_wal_senders = 10
hot_standby = on
archive_mode = on
archive_command = '/bin/true'

Thanks!
Craig


--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
3430 Carmel Mountain Road, Suite 250
San Diego, CA 92121
---------------------------------

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux