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

    upper(concat(substring(customer_fname, 1, 1), customer_lname, lpad(rnk::varchar, 3, '0'), '@SOME.COM')) AS customer_email
FROM (  
    SELECT customer_id,
        rank() OVER (
            PARTITION BY substring(customer_fname, 1, 1), customer_lname
            ORDER BY customer_id
        ) AS rnk
    FROM customers
) q
ORDER BY customer_email
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
customer_id customer_fname customer_lname rnk 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