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