Postgresql 14: Searching partial index with type conversion

Question

Based on solution from this post I've created the following partial index:

CREATE INDEX CONCURRENTLY IF NOT EXISTS cdbus_message_message_vessel_barcode_delete_procedure_idx ON tare_new.cdbus_message USING btree 
 (
 (vessel_barcode::int8), 
 (
 length(regexp_replace(vessel_barcode, '\D', '', 'g')) = length(vessel_barcode)
 AND length(vessel_barcode) = 16
 )
 )
WHERE (
 length(regexp_replace(vessel_barcode, '\D', '', 'g')) = length(vessel_barcode) 
 AND length(vessel_barcode) = 16 
 )
;

I need such index for deletion of old data from source table where "vessel_barcode" column is stored as text. Rows of this table not matching index criteria (see above) are not passed to the next stage of processing, where "vessel_barcode" column is then stored as bigint.

According to the article mentioned above, I tried to access this partial index with the following query (before that I checked index creation was really completed):

WITH vb AS (
 SELECT 1017535400101448 AS vessel_barcode_int8
)
SELECT 
 *
FROM tare_new.cdbus_message cm 
 INNER JOIN vb ON 
 vb.vessel_barcode_int8 = cm.vessel_barcode::bigint
WHERE
 length(regexp_replace(vessel_barcode, '\D'::text, ''::text, 'g'::text)) = length(vessel_barcode) 
 AND length(vessel_barcode) = 16
;

The problem is that EXPLAIN still shows sequential scan:

Gather (cost=1000.00..148473103.29 rows=96 width=1340)
 Workers Planned: 2
 -> Parallel Seq Scan on cdbus_message cm (cost=0.00..148472093.69 rows=40 width=1340)
 Filter: ((length(vessel_barcode) = 16) AND ('1017535400101448'::bigint = (vessel_barcode)::bigint) AND (length(regexp_replace(vessel_barcode, '\D'::text, ''::text, 'g'::text)) = 16))

Any ideas why this doesn't work? Size of cdbus_message is 1.2T, so using index should be preffered here.

Thanks for your help!

Answer 1

It seems Postgresql 14 doesn't optimize index conditions the same way it optimizes query predicates (see EXPLAIN results above). So, changed index predicates to:

 length(regexp_replace(vessel_barcode, '\D', '', 'g')) = 16
AND length(vessel_barcode) = 16