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.
| 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 |