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!