PHPTek 2024 - EXPLAIN - The Best And Worst Tool For Speeding Up SQL Queries

Structured Query Language can be tricky.

The primary tool that is used to check query performance is EXPLAIN.

EXPLAIN is beyond tricky, having its little quirks and foibles.

This is an introductory session on Explain and its use with MySQL and PostgreSQL, where you will learn to decipher what EXPLAIN is trying to tell you.

EXPLAIN is a handy tool once you learn how to read the entrails.

PHPTek 2024 - PHP[Architect] BALLROOM Tue 11:00 am - 11:50 am

Transcript

1. Databases run better with Percona
2. EXPLAIN - The Best And Worst Tool For Speeding Up SQL Queries David.Stokes@Percona.com @Stoker slideshare.com/stoker
3. ©2023 Percona | Confidential EXPLAIN - The Best And Worst Tool For Speeding Up SQL Queries Structured Query Language can be tricky. The main tool that is used to check query performance is EXPLAIN. EXPLAIN is beyond tricky, having its own little quirks and foibles. This is an introductory session into its use with MySQL and PostgreSQL where you will learn to decipher what EXPLAIN is trying to tell you. EXPLAIN is a handy tool once you learn how to read the entrails. PHPTek 2024 - PHP[Architect] BALLROOM Tue 11:00 am - 11:50 am 3
4. ©2023 Percona | Confidential Who Am I I am Dave Stokes Technology Evangelist at Percona Author of MySQL & JSON - A Practical Programming Guide Over a decade on the Oracle MySQL Community Team Started with MySQL 3.29 David.Stokes@Percona.com @Stoker https://speakerdeck.com/stoker
5. Explain
6. ©2023 Percona | Confidential Warning: EXPLAIN can be very frustrating as does not lead you from Point A to Point B and requires you to pay attention. 6
7. ©2023 Percona | Confidential Much like IQ tests and credit scoring, it gives you a partial view of the situation which may be incomplete, ot just wrong when viewed in the totality. 7 EXPLAIN is a tool … It does not hand you the correct answer. It’s output requires interpretation, which means you have to make decisions. There are many ways to skin a cat, and how many skinless cats do you need anyway? Explain relies on historical information which is often wrong. Your Mileage May Very. Objects in mirror are closer than they appear. Close cover before striking. Etcetera!
8. ©2024 Percona By default EXPLAIN lies to you! (More about that later) 8
9. Query Plan You quest is to find the ultimate query plan
10. ©2023 Percona | Confidential The Optimizer … takes your Structured Query Language (SQL) command and determines what needs to be fetched to return the requested data. Is that data in memory? Or on disk? Is in different tables? Or Databases? What functions will be needed? Sorting? Grouping? 10
11. ©2023 Percona | Confidential Five Options is 5! 11
12. ©2023 Percona | Confidential Cost Based Optimizer MySQL uses a cost-based optimizer to determine the best way to resolve a query. In many cases, MySQL can calculate the best possible query plan, but sometimes MySQL does not have enough information about the data at hand and has to make “educated” guesses about the data. Remember ‘educated’ for later. PostgreSQL : The estimated cost is computed as (disk pages read * seq_page_cost) + (rows scanned * cpu_tuple_cost). By default, seq_page_cost is 1.0 and cpu_tuple_cost is 0.01, so the estimated cost is (358 * 1.0) + (10000 * 0.01) = 458. 12
13. ©2023 Percona | Confidential Sequential Scan - Read EVERYTHING!!! Yup, you could just read everything from all the tables. But that is very slow Takes up lots of memory Not a good use of resources So TRY to avoid sequential scans on BIG tables (not all sequential scans are evil) 13
14. ©2023 Percona | Confidential Is there an index to help us? It would be much faster if we could skip over the parts we do not need. 14
15. ©2023 Percona | Confidential Can we get data from just the index Usually you have to read the index and then read the corresponding data. But what if we could answer the query from just the information in the index? It saves having to read the index and then the data itself. Win!! 15
16. ©2023 Percona | Confidential The execution of a query involves many possible operations: scan, fetch, join, filter, and so on. When the query processor is planning the query execution, there may be several possible choices for each operation: for example, there may be different possible indexes, or a choice of join types. With each of these operations, some of these choices are quicker and more efficient than others. The query processor attempts to choose the most efficient options when creating the query execution plan. https://docs.couchbase.com/cloud/n1ql/n1ql-language-reference/cost-based-optimizer.html 16
17. Explaining Explain
18. ©2023 Percona | Confidential EXPLAIN … is prepended to a query The output of this command displays the execution plan that the planner generates for the supplied statement. The execution plan shows how the table(s) referenced by the statement will be scanned — by plain sequential scan, index scan, etc. — and if multiple tables are referenced, what join algorithms will be used to bring together the required rows from each input table. 18
19. ©2023 Percona | Confidential MySQL Version {EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] {EXPLAIN | DESCRIBE | DESC} [explain_type] {explainable_stmt | FOR CONNECTION connection_id} {EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement explain_type: { FORMAT = format_name } format_name: { TRADITIONAL | JSON | TREE } explainable_stmt: { SELECT statement | TABLE statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement } 19 EXPLAIN [ ( option [, ...] ) ] statement EXPLAIN [ ANALYZE ] [ VERBOSE ] statement where option can be one of: ANALYZE [ boolean ] VERBOSE [ boolean ] COSTS [ boolean ] SETTINGS [ boolean ] GENERIC_PLAN [ boolean ] BUFFERS [ boolean ] WAL [ boolean ] TIMING [ boolean ] SUMMARY [ boolean ] FORMAT { TEXT | XML | JSON | YAML } PostgreSQL Version
20. ©2023 Percona | Confidential That’s the essentials! Thank you, Good night, and Don’t forget to tip your waitress!! 20
21. ©2023 Percona | Confidential No, there is more … 21
22. ©2023 Percona | Confidential Warning! It does not matter how good your ear protection is if you are not wearing your safety glasses! 22
23. ©2023 Percona | Confidential MySQL explain select * from ticket where id > 0 order by id \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ticket partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100 Extra: Using where; Using filesort 1 row in set, 1 warning (0.0010 sec) Note (code 1003): /* select#1 */ select `test`.`ticket`.`id` AS `id`,`test`.`ticket`.`customer` AS `customer`,`test`.`ticket`.`rep_current` AS `rep_current` from `test`.`ticket` where (`test`.`ticket`.`id` > 0) order by `test`.`ticket`.`id` 23 type: all is a sequential scan the actual proposed query plan
24. ©2023 Percona | Confidential PostgreSQL EXPLAIN select * from x1 where x > 0 order by id; QUERY PLAN ------------------------------------------------------------ Sort (cost=61.72..63.26 rows=617 width=16) Sort Key: id -> Seq Scan on x1 (cost=0.00..33.12 rows=617 width=16) Filter: (x > 0) (4 rows) 24 The two costs figures are the start-up and the final Sequential Scan Filter
25. ©2023 Percona | Confidential MySQL explain select * from ticket where id > 0 order by id \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ticket partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100 Extra: Using where; Using filesort 1 row in set, 1 warning (0.0010 sec) Note (code 1003): /* select#1 */ select `test`.`ticket`.`id` AS `id`,`test`.`ticket`.`customer` AS `customer`,`test`.`ticket`.`rep_current` AS `rep_current` from `test`.`ticket` where (`test`.`ticket`.`id` > 0) order by `test`.`ticket`.`id` 25 WHERE clauses can be tricky (columns in where clauses are automatically candidates for b Post data return actions are usually expensive
26. ©2023 Percona | Confidential PostgreSQL EXPLAIN select * from x1 where x > 0 order by id; QUERY PLAN ------------------------------------------------------------ Sort (cost=61.72..63.26 rows=617 width=16) Sort Key: id -> Seq Scan on x1 (cost=0.00..33.12 rows=617 width=16) Filter: (x > 0) (4 rows) 26 Yup, sorting has a cost (another great candidate for sort maybe the column you are sorting on [you have to test]) (BTW the database is a much more efficient sorting machine than your PHP/Node.js application)
27. ©2023 Percona | Confidential MySQL Types - Best to worst performance 27 Const Used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values System The table has only one row (= system table). Eq_ref One row is read from this table for each combination of rows from the previous tables Ref Used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index Fulltext Uses a fulltext index Ref_or_null This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values Index Merge The Index Merge access method retrieves rows with multiple range scans and merges their results into one Unique Subquery An index lookup function that replaces the subquery completely for better efficiency Index Subquery Similar to Unique Subquery but for non unique indexes Range Only rows that are in a given range are retrieved, using an index to select the rows Index If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned All Full Table Scan
28. Examples
29. ©2023 Percona | Confidential MySQL - Create Some Sample Data create table tek1 (id int unsigned auto_increment, a int, b int, c int, primary key (id)); insert into tek1 (a,b,c) values (rand()*(100-1),rand()*(100-1),rand()*(100-1)); 10 times 29
30. ©2023 Percona | Confidential WHERE id > 0 > EXPLAIN SELECT * FROM tek1 WHERE id > 0\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tek1 partitions: NULL type: range possible_keys: PRIMARY - An available key key: PRIMARY – The key used key_len: 4 - 4 Byte Integer ref: NULL rows: 10 – Number of rows in table filtered: 100 Extra: Using where 1 row in set, 1 warning (0.0010 sec) 30
31. ©2023 Percona | Confidential WHERE a > 9 > EXPLAIN SELECT * FROM tek1 WHERE a > 9\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tek1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 – We have ten rows in the table filtered: 33.32999801635742 - And optimizer is guessing we will have to read 33% of ‘em Extra: Using where 1 row in set, 1 warning (0.0010 sec) 31 > select count(id) from tek1 where a > 9; +-----------+ | count(id) | +-----------+ | 10 | But the actual number is 10!! +-----------+ Optimizer has poor information on column ‘a’
32. ©2023 Percona | Confidential • Poor data statistics • System assumes data in columns has an equal distribution • Lack of index maintenance on tables • Growth of data in size • Churn of data 32 ANALYZE TABLE <table> • Will perform a key distribution analysis and stores the distribution for the named table What causes bad query plans?
33. ©2023 Percona | Confidential PostgreSQL Version create table tek1 (id serial primary key, a int, b int, c int); insert into tek1 (a,b,c) values (random()*(100-1)+1, random()*(100-1)+1, random()*(100-1)+1); 33
34. ©2023 Percona | Confidential WHERE id > 0 test=# EXPLAIN select * FROM tek1 where id > 0; QUERY PLAN -------------------------------------------------------------------------- Bitmap Heap Scan on tek1 (cost=8.93..26.65 rows=617 width=16) Recheck Cond: (id > 0) -> Bitmap Index Scan on tek1_pkey (cost=0.00..8.78 rows=617 width=0) Index Cond: (id > 0) (4 rows) 34 test=# EXPLAIN select * FROM tek1 where a > 9; QUERY PLAN -------------------------------------------------------- Seq Scan on tek1 (cost=0.00..33.12 rows=617 width=16) Filter: (a > 9) (2 rows) WHERE a > 9
35. ©2023 Percona | Confidential Do Indexes Really Work? test=# create table z as (SELECT GENERATE_SERIES(1,1000000) as id); SELECT 1000000 # Two identical tables test=# create table w as (SELECT GENERATE_SERIES(1,1000000) as id); SELECT 1000000 test=# create index w_id_idx on w (id); CREATE INDEX test=# explain select id from z where id = 1001; QUERY PLAN ------------------------------------------------------------------- Gather (cost=1000.00..10688.43 rows=1 width=4) Workers Planned: 2 -> Parallel Seq Scan on z (cost=0.00..9688.33 rows=1 width=4) Filter: (id = 1001) (4 rows) test=# explain select id from w where id = 1001; QUERY PLAN ----------------------------------------------------------------------- Index Only Scan using w_id_idx on w (cost=0.42..8.44 rows=1 width=4) Index Cond: (id = 1001) 35
36. So what do I look for?
37. ©2024 Percona What are your most frequently run queries? Percona Monitoring and Management MySQL Workbench Performance schema queries Another monitoring tool 37
38. ©2024 Percona What are your longest running queries? Bad use of a transaction? Excessive locking N+1 Issues Mixing analytics with transactions 38
39. ©2024 Percona • Are you joining two indexed columns? Are they both the same data type? • Be careful of trying to match an index to a function - to_lower(last_name) • Follow good indexing practices ◦ Do not over index ◦ Columns to the RIGHT of a WHERE clause are candidates for indexing • Really small tables fit in memory & are had to optimize around • For speed pull values of JSON data and put in their own column (generated column) Data Stuff 39
40. ©2023 Percona | Confidential First step - Look At Table Definitions SHOW CREATE TABLE x\G *************************** 1. row *************************** Table: x Create Table: CREATE TABLE `x` ( `a` int NOT NULL, `b` int DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 40 SHOW CREATE TABLE y\G *************************** 1. row *************************** Table: y Create Table: CREATE TABLE `y` ( `a` int NOT NULL, `b` int DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
41. ©2023 Percona | Confidential Are you joining TWO indexed fields? explain format=tree select x.a, y.b from x join y where x.a = y.a\G *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=1.6 rows=3) -> Index scan on x using PRIMARY (cost=0.55 rows=3) -> Single-row index lookup on y using PRIMARY (a=x.a) (cost=0.283 rows=1) 1 row in set (0.0035 sec) 41
42. ©2023 Percona | Confidential x Has an index, y Does not explain format=tree select x.a, y.b from x join y where x.a = y.b\G *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=1.6 rows=3) -> Filter: (y.b is not null) (cost=0.55 rows=3) -> Table scan on y (cost=0.55 rows=3) -> Single-row covering index lookup on x using PRIMARY (a=y.b) (cost=0.283 rows=1) 1 row in set (0.0011 sec) 42
43. ©2023 Percona | Confidential Hash joins are fast explain format=tree select x.a, y.b from x join y where x.b = y.b\G *************************** 1. row *************************** EXPLAIN: -> Inner hash join (y.b = x.b) (cost=1.7 rows=3) -> Table scan on y (cost=0.117 rows=3) -> Hash -> Table scan on x (cost=0.55 rows=3) MySQL employs a hash join for any query for which each join has an equi-join condition, and in which there are no indexes that can be applied to any join conditions 43
44. ©2023 Percona | Confidential Multi column indexes create table multi (a int, b int, c int); insert into multi values (10,11,12),(20,21,22),(30,31,32),(40,41,42); create index multi_abc_idx on multi(a,b,c); 44 For multi column indexes, traditionally you would put the column with the highest cardinality (fewest in number of values) in the left most column, the second highest in the next column, and so forth.
45. ©2023 Percona | Confidential Quiz on create index multi_abc_idx on multi(a,b,c); This index will be used when searching on columns: 1. a 2. a and b 3. a and b and c 4. a and c 5. All of the above 45
46. ©2023 Percona | Confidential a only? explain format=tree select * from multi where a=10\G *************************** 1. row *************************** EXPLAIN: -> Covering index lookup on multi using multi_abc_idx (a=10) (cost=0.35 rows=1) 😁 46
47. ©2023 Percona | Confidential a and b explain format=tree select * from multi where a=10 and b = 11\G *************************** 1. row *************************** EXPLAIN: -> Covering index lookup on multi using multi_abc_idx (a=10, b=11) (cost=0.35 rows=1) 😁 47
48. ©2023 Percona | Confidential a, b, and c? explain format=tree select * from multi where a=10 and b = 11 and c =12\G *************************** 1. row *************************** EXPLAIN: -> Covering index lookup on multi using multi_abc_idx (a=10, b=11, c=12) (cost=0.35 rows=1) 😁 48
49. ©2023 Percona | Confidential a and c?? explain format=tree select * from multi where a=10 and c =12\G *************************** 1. row *************************** EXPLAIN: -> Filter: (multi.c = 12) (cost=0.275 rows=0.25) -> Covering index lookup on multi using multi_abc_idx (a=10) (cost=0.275 rows=1) 😁 49
50. ©2023 Percona | Confidential b and c ?? explain format=tree select * from multi where b=11 and c =12\G *************************** 1. row *************************** EXPLAIN: -> Filter: ((multi.c = 12) and (multi.b = 11)) (cost=0.65 rows=1) -> Index scan on multi using multi_abc_idx (cost=0.65 rows=4) 50 And index scan is not a Covering index lookup and comes with a higher cost 😒
51. ©2023 Percona | Confidential PostgreSQL version test=# create table multi (a int, b int, c int); CREATE TABLE test=# insert into multi values (10,11,12), (20,21,22), (30,31,32); INSERT 0 3 test=# create index multi_abc_index on multi(a,b,c); CREATE INDEX 51
52. ©2023 Percona | Confidential Lets Try It Without An Index test=# explain select * from multi where a=10 and b=11 and c=12; QUERY PLAN ------------------------------------------------------ Seq Scan on multi (cost=0.00..1.05 rows=1 width=12) Filter: ((a = 10) AND (b = 11) AND (c = 12)) 52
53. ©2023 Percona | Confidential JSON Format test=# explain (format json) select * from multi where a=10 and b=11; QUERY PLAN ------------------------------------------- [ + { + "Plan": { + "Node Type": "Seq Scan", + "Parallel Aware": false, + "Async Capable": false, + "Relation Name": "multi", + "Alias": "multi", + "Startup Cost": 0.00, + "Total Cost": 1.04, + "Plan Rows": 1, + "Plan Width": 12, + "Filter": "((a = 10) AND (b = 11))"+ } + } + ] 53
54. ©2023 Percona | Confidential Lets Try A Table With an Index test=# \d multi2 Table "public.multi2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | | b | integer | | | c | integer | | | Indexes: "multi2_idx" btree (a, b, c) test=# insert into multi2 (a,b,c) select a , random()*(1000-1)+1, random()*(1000-1)+1 FROM generate_series(1,100000) a; INSERT 0 100000 test=# update multi2 set b = 1, c = 1 where a = 999; # We need an unique column UPDATE 1 54
55. ©2023 Percona | Confidential a only? test=# explain select a,b,c from multi2 where a=999; QUERY PLAN ---------------------------------------------------------------------------- --- Index Only Scan using multi2_idx on multi2 (cost=0.42..4.44 rows=1 width=12) Index Cond: (a = 999) (2 rows) 😁 55
56. ©2023 Percona | Confidential a and b? test=# explain select a,b,c from multi2 where a=999 and b=1; QUERY PLAN ---------------------------------------------------------------------------- --- Index Only Scan using multi2_idx on multi2 (cost=0.42..4.44 rows=1 width=12) Index Cond: ((a = 999) AND (b = 1)) (2 rows) 😁 56
57. ©2023 Percona | Confidential a, b, and c? test=# explain select a,b,c from multi2 where a=999 and b=1 and c=1; QUERY PLAN ---------------------------------------------------------------------------- --- Index Only Scan using multi2_idx on multi2 (cost=0.42..4.44 rows=1 width=12) Index Cond: ((a = 999) AND (b = 1) AND (c = 1)) (2 rows) 😁 57
58. ©2023 Percona | Confidential b & c? test=# explain select a,b,c from multi2 where b=1 and c=1; QUERY PLAN ---------------------------------------------------------- (cost=0.00..2041.00 rows=1 width=12) Filter: ((b = 1) AND (c = 1)) (2 rows) 😒 58
59. ©2023 Percona | Confidential a and c? test=# explain select a,b,c from multi2 where a=999 and c=1; QUERY PLAN ---------------------------------------------------------------------------- --- Index Only Scan using multi2_idx on multi2 (cost=0.42..4.44 rows=1 width=12) Index Cond: ((a = 999) AND (c = 1)) (2 rows) 😁 59
60. Lies, Damned lies … And Statistics
61. ©2023 Percona | Confidential Remember slide 8? 61
62. ©2024 Percona EXPLAIN vs EXPLAIN ANALYZE The ANALYZE option causes the statement to be actually executed, not only planned. Then actual run time statistics are added to the display, including the total elapsed time expended within each plan node (in milliseconds) and the total number of rows it actually returned. This is useful for seeing whether the planner's estimates are close to reality. (if not run ANALYZE <table>) 62
63. ©2024 Percona Gives you real timings Actually runs the query - impacting row, performance Good vs BAD 63
64. ©2024 Percona EXPLAIN format=tree SELECT a.name,b.name FROM Country a JOIN City b ON (a.Code = b.CountryCode) where b.District = 'Texas'\G *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=823 rows=419) -> Filter: (b.District = 'Texas') (cost=425 rows=419) -> Table scan on b (cost=425 rows=4188) -> Single-row index lookup on a using PRIMARY (Code=b.CountryCode) (cost=0.85 rows=1) 1 row in set (0.0011 sec) EXPLAIN ANALYZE - Runs the query 64 EXPLAIN ANALYZE format=tree SELECT a.name,b.name FROM Country a JOIN City b ON (a.Code = b.CountryCode) where b.District = 'Texas'\G *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=823 rows=419) (actual time=1.12..1.19 rows=26 loops=1) -> Filter: (b.District = 'Texas') (cost=425 rows=419) (actual time=1.1..1.16 rows=26 loops=1) -> Table scan on b (cost=425 rows=4188) (actual time=0.0859..0.938 rows=4079 loops=1) -> Single-row index lookup on a using PRIMARY (Code=b.CountryCode) (cost=0.85 rows=1) (actual time=735e-6..750e-6 rows=1 loops=26) 1 row in set (0.0022 sec)
65. ©2024 Percona create table x1 (a int, b int); insert into x1 values (1,10),(2,20),(3,30),(4,40),(5,50),(6,60),(7,70); explain format=tree select a from x1 where a > 0\G *************************** 1. row *************************** EXPLAIN: -> Filter: (x1.a > 0) (cost=0.95 rows=2.33) -> Table scan on x1 (cost=0.95 rows=7) explain ANALYZE format=tree select a from x1 where a > 0\G *************************** 1. row *************************** EXPLAIN: -> Filter: (x1.a > 0) (cost=0.95 rows=2.33) (actual time=0.0271..0.0333 rows=7 loops=1) -> Table scan on x1 (cost=0.95 rows=7) (actual time=0.026..0.0318 rows=7 loops=1) 65
66. ©2024 Percona 66 EXPLAIN format=tree SELECT city.name as 'City', country.name as 'country' from city join country on (city.countrycode = country.code) where district = 'Texas'\G *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=904 rows=419) -> Filter: (city.District = 'Texas') (cost=444 rows=419) -> Table scan on city (cost=444 rows=4188) -> Single-row index lookup on country using PRIMARY (Code=city.CountryCode) (cost=1 rows=1) ANALYZE TABLE City UPDATE histogram on District WITH 1024 buckets; EXPLAIN ANALYZE format=tree SELECT city.name as 'City', country.name as 'country' from city join country on (city.countrycode = country.code) where district = 'Texas'\G *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=454 rows=26.7) (actual time=1.47..1.55 rows=26 loops=1) -> Filter: (city.District = 'Texas') (cost=425 rows=26.7) (actual time=0.899..0.974 rows=26 loops=1) -> Table scan on city (cost=425 rows=4188) (actual time=0.0407..0.788 rows=4079 loops=1) -> Single-row index lookup on country using PRIMARY (Code=city.CountryCode) (cost=1 rows=1) (actual time=0.022..0.022 rows=1 loops=26) Seeing Performance Improvements
67. ©2024 Percona test=# explain select q.a, w.c from multi q join multi2 w on (q.a = w.a); QUERY PLAN -------------------------------------------------------------------------------------- Nested Loop (cost=0.42..14.37 rows=3 width=8) -> Seq Scan on multi q (cost=0.00..1.03 rows=3 width=4) -> Index Only Scan using multi2_idx on multi2 w (cost=0.42..4.44 rows=1 width=8) Index Cond: (a = q.a) (4 rows) test=# explain ANALYZE select q.a, w.c from multi q join multi2 w on (q.a = w.a); QUERY PLAN --------------------------------------------------------------------------------------------------------------------- ----------- Nested Loop (cost=0.42..14.37 rows=3 width=8) (actual time=1.210..1.216 rows=3 loops=1) -> Seq Scan on multi q (cost=0.00..1.03 rows=3 width=4) (actual time=0.018..0.018 rows=3 loops=1) -> Index Only Scan using multi2_idx on multi2 w (cost=0.42..4.44 rows=1 width=8) (actual time=0.397..0.397 rows=1 loops=3) Index Cond: (a = q.a) Heap Fetches: 0 Planning Time: 0.122 ms Execution Time: 1.231 ms (7 rows) PostgreSQL EXAMPLE 67
68. You have to go work on your queries Now comes the hardpart
69. ©2024 Percona Optimize the most frequently used queries first Understand indexing Understand that FULL TABLE SCANS are NOT always EVIL RTFM Practice Double check you assumptions, the computer does not think the same way you! Things to do 69
70. ©2024 Percona Try Percona software: ➔ Percona Distribution for Postgres ➔ Percona Operator for PostgreSQL ➔ Percona Monitoring and Management (PMM) We have a TDE solution looking for testers! ➔ github.com/Percona-Lab/postgresql-tde Ask questions and leave your feedback: ➔ percona.community ➔ forums.percona.com ➔ github.com/percona 70 Innovate freely with highly available and reliable production PostgreSQL
71. ©2024 Percona Percona is hiring! • Senior Software Engineer (PostgreSQL) • Support Engineer (PostgreSQL) • PostgreSQL Evangelist … and more!
72. Thank You! David.Stokes@Percona.com @Stoker Speakerdeck.com/Stoker