opengovsg / CheckWho

Enable Singapore residents to easily and authoritatively verify public officers
0 stars 0 forks source link

feat: create db index for quicker queries #508

Closed zxt-tzx closed 1 year ago

zxt-tzx commented 1 year ago

Run db migration to create index so that queries run faster

Example queries:

Queries to search for num_of_queries

SELECT
    count(*)
FROM
    unique_param
WHERE
    unique_param_string in(
        SELECT
            SUBSTRING(modality_params ->> 'message', POSITION('check-sms-' IN modality_params ->> 'message') + 10, 12) AS unique_param FROM notification
        WHERE
            modality_params ->> 'message' IS NOT NULL
            AND message_template_id = 15)
    AND num_of_queries = 0;

Verifying that the index works

EXPLAIN
select * from notification where  SUBSTRING(modality_params ->> 'message', POSITION('check-sms-' IN modality_params ->> 'message') + 10, 12) = 'sd7l2dq26x45';

EXPLAIN
SELECT
    *
FROM
    unique_param
    JOIN notification ON unique_param.unique_param_string = SUBSTRING(notification.modality_params ->> 'message', POSITION('check-sms-' IN notification.modality_params ->> 'message') + 10, 12) where unique_param.id=1;