1

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!

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.