Odd Choice of seq scan

Hi there,

I'm wondering if anyone has any insight into what might make the database
choose a sequential scan for a query (table defs and plan below) like :

SELECT orders.orderid FROM orders
WHERE (
orders.orderid IN ('546111')
OR
orders.orderid IN (select orderid FROM orderstotrans WHERE (transid IN
('546111')))
);

I have a couple of environments, all on Postgresql 13.7 and:
- on one the query executes with an sequential scan on the orders table
- on the other sequential scan on an index (ie walks index and filters,
rather than looking up ids on the index as an index condition.)

Plan and tables are below, but it seems to me that the planner knows the
subplan is going to return 1 row (max) and should "know" that there is a
max of 2 IDs to look up an indexes would be faster than a sequential scan
(of either table or index) and filter. I've tried re analyzing to make sure
stats are good and it hasn't helped

I can get a good plan that does use the index efficiently by using a union,
eg:

select orders.orderid FROM orders
WHERE (
orders.orderid IN (
SELECT '546111'
UNION
SELECT orderid FROM orderstotrans WHERE (transid IN ('546111'))
)
);

but I want to understand what warning signs I should be aware of with the
original query that put it on the path of a bad plan, so I don't do it
again.

Plan - seq scan of table:
=====
> explain
select orders.orderid FROM orders WHERE (orders.orderid IN ('546111') OR
orders.orderid IN (select orderid FROM orderstotrans WHERE (transid IN
('546111'))));

QUERY PLAN

-------------------------------------------------------------------------------------------------------
Seq Scan on orders (cost=8.45..486270.87 rows=4302781 width=8)
Filter: ((orderid = '546111'::bigint) OR (hashed SubPlan 1))
SubPlan 1
-> Index Scan using orderstotrans_transid_key on orderstotrans
(cost=0.43..8.45 rows=1 width=8)
Index Cond: (transid = '546111'::bigint)
(5 rows)
=====

Plan - Seq scan and filter of index:
=====
> explain select orders.orderid FROM orders WHERE (orders.orderid IN
('546111') OR orders.orderid IN (select orderid FROM orderstotrans WHERE
(transid IN ('546111'))));
QUERY PLAN

-------------------------------------------------------------------------------------------------------
Index Only Scan using orders_pkey on orders (cost=9.16..4067888.60
rows=64760840 width=8)
Filter: ((orderid = '546111'::bigint) OR (hashed SubPlan 1))
SubPlan 1
-> Index Scan using orderstotrans_transid_key on orderstotrans
(cost=0.57..8.59 rows=1 width=8)
Index Cond: (transid = '546111'::bigint)
(5 rows)
=====

Tables:
=====
Table "test.orders"
Column | Type | Collation | Nullable
| Default
----------------------+-----------------------------+-----------+----------+--------------
orderid | bigint | | not null |
istest | smallint | | not null
| 0
orderstatusid | integer | | |
customername | text | | |
customeraddress | text | | |
customercountry | text | |
|
customercity | text | |
|
customerstate | text | |
|
customerzip | text | | |
"orders_pkey" PRIMARY KEY, btree (orderid)

Table "test.orderstotrans"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
orderid | bigint | | |
transid | bigint | | |
orderitemid | integer | | |
Indexes:
"orderstotrans_orderid_idx" btree (orderid)
"orderstotrans_orderitemid_idx" btree (orderitemid)
"orderstotrans_transid_key" UNIQUE, btree (transid)

Happier plan for the union version:
====
explain select orders.orderid FROM orders
WHERE (
orders.orderid IN (
SELECT '3131275553'
UNION
select orderid FROM orderstotrans WHERE (transid IN ('3131275553'))
)
);

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=9.21..21.84 rows=2 width=8) (actual time=0.034..0.043
rows=1 loops=1)
-> Unique (cost=8.64..8.65 rows=2 width=8) (actual time=0.024..0.026
rows=2 loops=1)
-> Sort (cost=8.64..8.64 rows=2 width=8) (actual
time=0.023..0.024 rows=2 loops=1)
Sort Key: ('3131275553'::bigint)
Sort Method: quicksort Memory: 25kB
-> Append (cost=0.00..8.63 rows=2 width=8) (actual
time=0.001..0.019 rows=2 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=8) (actual
time=0.001..0.001 rows=1 loops=1)
-> Index Scan using orderstotrans_transid_key on
orderstotrans (cost=0.57..8.59 rows=1 width=8) (actual time=0.015..0.016
rows=1 loops=1)
Index Cond: (transid = '3131275553'::bigint)
-> Index Only Scan using orders_pkey on orders (cost=0.57..6.58 rows=1
width=8) (actual time=0.007..0.007 rows=0 loops=2)
Index Cond: (orderid = ('3131275553'::bigint))
Heap Fetches: 0
Planning Time: 0.165 ms
Execution Time: 0.065 ms
(14 rows)
====
(though that plan is a bit misleading, as that index condition isn't
exactly what is used, ie with:

select orders.orderid FROM orders
WHERE (
orders.orderid IN (
SELECT '3131275553'
UNION
select orderid FROM orderstotrans WHERE (transid IN ('3131275553'))
)
);
orderid
-----------
439155713
(1 row)

the orderid it matches, isn't the one the planner showed, but it works)