UNION ALLの上でIN (サブクエリ)を書いたら、57万行を歩いた:PostgreSQLの見積もりと、= ANY(ARRAY(...))

SELECT p.id FROM catalog p
WHERE p.active AND p.id IN (
  SELECT id FROM catalog WHERE search_text LIKE '%vgs-12345%'
  UNION SELECT id FROM catalog WHERE search_text LIKE '%vgs12345%'
  UNION SELECT id FROM catalog WHERE code = 'vgs12345')
ORDER BY COALESCE(p.rank, 2147483647), p.released DESC, p.id
LIMIT 30;

Limit (actual rows=1 loops=1)
  ->  Nested Loop (actual rows=1 loops=1)
        Join Filter: (items.id = items_1.id)
        Rows Removed by Join Filter: 569999
        ->  Merge Append (actual rows=570000 loops=1)
              Sort Key: (COALESCE(items.rank, 2147483647)), items.released DESC, items.id
              ->  Index Scan using items_coalesce_released_id_idx on items (actual rows=500000 loops=1)
              ->  Index Scan using other_items_coalesce_released_id_idx on other_items (actual rows=70000 loops=1)
        ->  Materialize (actual rows=1 loops=570000)
              ->  Unique (actual rows=1 loops=1)
                    ...(品番の候補を集める部分。ここは速い)
Execution Time: 179.686 ms

Limit  (cost=743.11..835.88 rows=30 width=15)
  ->  Nested Loop  (cost=743.11..1023116.75 rows=330600 width=15)
        ->  Merge Append  (cost=0.85..35447.86 rows=570000 width=15)
        ->  Materialize  (cost=742.26..743.42 rows=116 width=32)

SELECT p.id FROM catalog p
WHERE p.active AND p.id = ANY (ARRAY(
  SELECT id FROM catalog WHERE search_text LIKE '%vgs-12345%'
  UNION SELECT id FROM catalog WHERE search_text LIKE '%vgs12345%'
  UNION SELECT id FROM catalog WHERE code = 'vgs12345'))
ORDER BY COALESCE(p.rank, 2147483647), p.released DESC, p.id
LIMIT 30;

Limit (actual rows=1 loops=1)
  InitPlan 1
    ->  Unique (actual rows=1 loops=1)
          ...(品番の候補を集める部分)
  ->  Sort (actual rows=1 loops=1)
        ->  Append (actual rows=1 loops=1)
              ->  Index Scan using items_pkey on items (actual rows=1 loops=1)
                    Index Cond: (id = ANY ((InitPlan 1).col1))
              ->  Index Scan using other_items_pkey on other_items (actual rows=0 loops=1)
                    Index Cond: (id = ANY ((InitPlan 1).col1))
Execution Time: 0.513 ms

CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- 50万件と7万件の、同じ形の2つの表。500件に1件だけ、人気順位がある
CREATE TABLE items (id text PRIMARY KEY, code text NOT NULL, released date NOT NULL,
  rank int, active boolean NOT NULL DEFAULT true, search_text text NOT NULL);
CREATE TABLE other_items (LIKE items INCLUDING ALL);
INSERT INTO items
SELECT 'p' || i, code, date '2010-01-01' + i % 6000, CASE WHEN i % 500 = 0 THEN i / 500 END, true,
       code || ' title ' || i
FROM generate_series(1, 500000) i,
     LATERAL (SELECT chr(97 + i % 26) || chr(97 + i / 26 % 26) || chr(97 + i / 676 % 26)
                     || lpad((i % 99999)::text, 5, '0') AS code) c;
INSERT INTO other_items
SELECT 'o' || i, 'zz' || i, date '2015-01-01' + i % 4000, CASE WHEN i % 100 = 0 THEN 1000 + i / 100 END, true,
       'zz' || i || ' other ' || i
FROM generate_series(1, 70000) i;
-- どちらの表にも、同じ索引を張る
CREATE INDEX ON items (code);
CREATE INDEX ON other_items (code);
CREATE INDEX ON items (COALESCE(rank, 2147483647), released DESC, id) WHERE active;
CREATE INDEX ON other_items (COALESCE(rank, 2147483647), released DESC, id) WHERE active;
CREATE INDEX ON items USING gin (search_text gin_trgm_ops);
CREATE INDEX ON other_items USING gin (search_text gin_trgm_ops);
-- 2つの表を、1つに見せる
CREATE VIEW catalog AS
  SELECT id, code, released, rank, active, search_text FROM items
  UNION ALL
  SELECT id, code, released, rank, active, search_text FROM other_items;
VACUUM ANALYZE;

SELECT id FROM (
  (SELECT id, rank, released FROM items p WHERE <条件> ORDER BY <並び> LIMIT 10)
  UNION ALL
  (SELECT id, rank, released FROM other_items p WHERE <条件> ORDER BY <並び> LIMIT 10)
) r
ORDER BY <並び> LIMIT 10;