我有两张简单的table node 以及 node_ip 使用外键链接，如下所示：

CREATE TABLE node_ip (
    id serial NOT NULL,
    node_id int4 NOT NULL,
    ip inet NULL
);

CREATE TABLE node (
  id serial NOT NULL,
  mac macaddr NULL,
  is_local bool,
  CONSTRAINT node_pkey PRIMARY KEY ( id)

);

ALTER TABLE node_ip ADD CONSTRAINT node_const
   FOREIGN KEY (node_id) REFERENCES node(id);

以及以下指标：

CREATE INDEX idx_node_ip_1 ON node_ip USING btree (ip)
CREATE INDEX idx_node_1    ON node    USING btree (id) WHERE ((NOT is_local) AND ((mac)::text !~~ '02:00:00%'::text))

我正在尝试优化以下查询：

select * from node_ip
where ip = '192.168.1.6'
  and node_id in (select id from node
                  where is_local = false
                  and mac::text not like '02:00:00%');

但是这是我能得到的最好的：

Gather  (cost=1352.74..29923.00 rows=13921 width=46) (actual time=1.905..32.612 rows=14656 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Nested Loop Semi Join  (cost=352.74..27530.90 rows=5800 width=46) (actual time=0.694..20.534 rows=4885 loops=3)
        ->  Parallel Bitmap Heap Scan on node_ip  (cost=352.32..22986.04 rows=5800 width=46) (actual time=0.638..3.547 rows=4892 loops=3)
              Recheck Cond: (ip = '192.168.1.6'::inet)
              Heap Blocks: exact=491
              ->  Bitmap Index Scan on idx_node_ip_1  (cost=0.00..348.84 rows=13921 width=0) (actual time=1.381..1.381 rows=14676 loops=1)
                    Index Cond: (ip = '192.168.1.6'::inet)
        ->  Index Only Scan using idx_node_1 on node  (cost=0.42..0.77 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=14676)
              Index Cond: (id = node_ip.node_id)
              Heap Fetches: 4328
Planning Time: 0.616 ms
Execution Time: 33.310 ms

有关表格的信息：

select count(*) from node ;   --  500000
select count(*) from node_ip; -- 2500000
select count(*) from node where is_local = false and mac::text not like '02:00:00%'; -- 300000

从计划上看，大部分时间都花在了工作上 Nested Loop Semi Join ，有没有办法加快速度？
相关问题：什么是最好的索引 macaddr 类型？我的大部分问题都在哪里 LIKE '02:00:00%' ?
注意：我正在使用 postgres 11