Exercises – Tuning Queries

As part of this exercise, you need to prepare data set, go through the explain plan and come up with right indexes to tune the performance.

• As of now customer email id in customers table contain same value (XXXXXXXXX).

• Let us update customer_email_id.

  • Use initial (first character) of customer_fname

  • Use full string of customer_lname

  • Use row_number by grouping or partitioning the data by first character of customer_fname and full customer_lname then sort it by customer_id.

  • Make sure row_number is at least 3 digits, if not pad with 0 and concatenate to email id. Here are the examples

  • Also make sure email ids are in upper case. |customer_id|customer_fname|customer_lname|rank|customer_email| |———–|————–|————–|—-|————–| |11591|Ann|Alexander|1|AALEXANDER001@SOME.COM| |12031|Ashley|Benitez|1|ABENITEZ001@SOME.COM| |11298|Anthony|Best|1|ABEST001@SOME.COM| |11304|Alexander|Campbell|1|ACAMPBELL001@SOME.COM| |11956|Alan|Campos|1|ACAMPOS001@SOME.COM| |12075|Aaron|Carr|1|ACARR001@SOME.COM| |12416|Aaron|Cline|1|ACLINE001@SOME.COM| |10967|Alexander|Cunningham|1|ACUNNINGHAM001@SOME.COM| |12216|Ann|Deleon|1|ADELEON001@SOME.COM| |11192|Andrew|Dickson|1|ADICKSON001@SOME.COM|

• Let us assume that customer care will try to search for customer details using at least first 4 characters.

• Generate explain plan for the query.

• Create unique index on customer_email.

• Generate explain plan again and review the differences.

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%load_ext sql

%%sql

SELECT q.*,
    upper(concat(substring(customer_fname, 1, 1), customer_lname, lpad(rnk::varchar, 3, '0'), '@SOME.COM')) AS customer_email
FROM (  
    SELECT customer_id,
        customer_fname,
        customer_lname,
        rank() OVER (
            PARTITION BY substring(customer_fname, 1, 1), customer_lname
            ORDER BY customer_id
        ) AS rnk
    FROM customers
) q
ORDER BY customer_email
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.