问在PostgreSQL中，哈希子计划是什么意思，最后重写的查询是什么？

EN

DROP TABLE ords;
CREATE TABLE ords (
ORD_ID INT NOT NULL,
ORD_PROD_ID VARCHAR(2) NOT NULL,
ETC_CONTENT VARCHAR(100));
ALTER TABLE ords ADD CONSTRAINT ords_PK PRIMARY KEY(ORD_ID);
CREATE INDEX ords_X01 ON ords(ORD_PROD_ID);
INSERT INTO ords
SELECT i
      ,chr(64+case when i <= 10 then i else 26 end)
      ,rpad('x',100,'x')
  FROM generate_series(1,10000) a(i);

SELECT COUNT(*) FROM ords WHERE ORD_PROD_ID IN ('A','B','C');

DROP TABLE delivery;
CREATE TABLE delivery (
ORD_ID INT NOT NULL,
VEHICLE_ID VARCHAR(2) NOT NULL,
ETC_REMARKS VARCHAR(100));
ALTER TABLE delivery ADD CONSTRAINT delivery_PK primary key (ORD_ID, VEHICLE_ID);
CREATE INDEX delivery_X01 ON delivery(VEHICLE_ID);
INSERT INTO delivery
SELECT i
     , chr(88 + case when i <= 10 then mod(i,2) else 2 end)
     , rpad('x',100,'x')
  FROM generate_series(1,10000) a(i);

analyze ords;
analyze delivery;
This is the SQL I am interested in.

SELECT *
  FROM ords a
 WHERE ( EXISTS (SELECT 1
                   FROM delivery b
                  WHERE a.ORD_ID = b.ORD_ID
                    AND b.VEHICLE_ID IN ('X','Y')
                 )
         OR a.ORD_PROD_ID IN ('A','B','C')
         );
Here is the execution plan
| Seq Scan on portal.ords a (actual time=0.038..2.027 rows=10 loops=1)                                           |
|   Output: a.ord_id, a.ord_prod_id, a.etc_content                                                               |
|   Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR ((a.ord_prod_id)::text = ANY ('{A,B,C}'::text[]))) |
|   Rows Removed by Filter: 9990                                                                                 |
|   Buffers: shared hit=181                                                                                      |
|   SubPlan 1                                                                                                    |
|     ->  Index Only Scan using delivery_pk on portal.delivery b (never executed)                                |
|           Index Cond: (b.ord_id = a.ord_id)                                                                    |
|           Filter: ((b.vehicle_id)::text = ANY ('{X,Y}'::text[]))                                               |
|           Heap Fetches: 0                                                                                      |
|   SubPlan 2                                                                                                    |
|     ->  Index Scan using delivery_x01 on portal.delivery b_1 (actual time=0.023..0.025 rows=10 loops=1)        |
|           Output: b_1.ord_id                                                                                   |
|           Index Cond: ((b_1.vehicle_id)::text = ANY ('{X,Y}'::text[]))                                         |
|           Buffers: shared hit=8                                                                                |
| Planning:                                                                                                      |
|   Buffers: shared hit=78                                                                                       |
| Planning Time: 0.302 ms                                                                                        |
| Execution Time: 2.121 ms 

 (
     SELECT *
       FROM ords a
      WHERE EXISTS (SELECT 1
                      FROM delivery b
                     WHERE a.ORD_ID = B.ORD_ID
                       AND b.VEHICLE_ID IN ('X','Y')
                     OFFSET 0 --> the optimizer prevented subquery collapse.
                   )
     *alternative OR*
     SELECT a.*
       FROM ords a *(Semi Hash Join)* delivery b --> the optimizer used b as an build input
      WHERE a.ORD_ID = b.ORD_ID
        AND b.VEHICLE_ID IN ('X','Y') --> the optimzer used the delivery_x01 index.
    )
    *filtered OR*    
    SELECT *
      FROM ords a
     WHERE a.ORD_PROD_ID IN ('A','B','C') --> the optimizer cannot use the ords_x01 index due to the query transformation