The following labs will take you through various query tuning scenarios and allow you to discover various ways to observe, diagnose, and optimize query performance with CockroachDB.
- DBeaver
- ./cockroach sql --insecure
- psql
Student, Database, pgurl, adminurl
The lab cluster is configured in Google Clould in a singe zone:
- us-east4-b
https://github.com/glennfawcett/roachcrib
Run the following query and observe the performance.
SELECT ol_number, SUM(ol_quantity)
FROM order_line
WHERE ol_w_id > 30
AND ol_amount > 9990
GROUP BY ol_number
ORDER BY ol_number;
This query must run in less than 1 second! Note the the location of the client program will effect performance. For instance, the following times should be achieved
- DBeaver:: < 200ms
- cockroach (laptop-to-cloud):: < 200ms
- roachprod sql glenn-querylabs:3 --insecure:: < 12ms
How do you show the query plan for this query?
How to you gather an explain plan /w statistics used by the optimizer?
How do you analyze details of the query runtime performance?
What can be done to improve the performance of this Query so that it runs in less than 170ms?
Run the following query:
SELECT w_name, w_city, sum(ol_amount)
FROM order_line
INNER JOIN warehouse ON (w_id = ol_supply_w_id)
WHERE ol_supply_w_id > 40
GROUP BY 1,2;
How does this query join?
Show how to force this query to use all join methods (LOOKUP, HASH, MERGE) ?
How do you make the optimizer choose MERGE join without using a HINT?
How do you force the query to use the primary key and not the indexes?