בכתיבת שאילתת SQL יש קושי אותו איננו חווים בעבודה עם שפות התכנות המקובלות, כמו C, Python, Java או JavaScript. שפות התכנות הללו הן אימפרטיביות, מה שאומר שהקוד הוא רשימת פקודות אותן אנו מעבירים לקומפיילר או לאינטרפרטר. כך גם כאשר איננו מבינים בדיוק לאילו פקודות מכונה מתורגמות הפקודות שלנו, עדיין יש לנו מושג כללי איך נראה המבנה של הקוד. למשל, אנחנו מבינים שכשכתבנו לולאה הקוד שבתוכה ירוץ כמספר הפעמים שהגדרנו, או שכאשר הגדרנו תנאי הקוד שבתוכו ירוץ בהתאם לקיום התנאי.
שאילתות SQL כתובות בצורה דקלרטיבית. דהיינו, השאילתות לא אומרות לשרת אילו פקודות להריץ אלא מכריזות על התוצאה הסופית שאנו רוצים לקבל. השרת הוא זה שמתרגם את השאילתא לסדרת פקודות שמאחזרות את הנתונים הרצויים. באופן כזה קשה לנו להבין מה השרת עושה מאחורי הקלעים, ולא תמיד אנו יודעים האם השאילתא שכתבנו יעילה ומה אפשר לעשות כדי לשפר אותה.
אחד הכלים שעוזר לנו להתגבר על הפער הזה הוא ה-execution plan, המתאר את התרגום לפעולות בסיסיות יותר שמבצע השרת לשאילתא שלנו. בפוסט הזה נשתמש בכלי הזה כדי לראות על קצה המזלג איזה סוג מידע אפשר לשאוב ממנו ומה אפשר לעשות עם המידע הזה כדי לשפר את השאילתות שלנו. את הדוגמאות נדגים על שאילתות ב-PostgreSQL, אבל המימוש דומה מאוד בכל בסיס נתונים מוכר. נניח שלקוראים יש ידע בסיסי בלבד ב-SQL, ונריץ רק שאילתא אחת על טבלה אחת ונראה את איך השאילתא רצה בשלוש צורות שונות.
שימוש ב-Execution Plan
נניח שאנו מנהלים מערכת לניהול תשלומים ויש לנו טבלה של היסטוריית התשלומים של הלקוחות שלנו, לפי המבנה הבא:
| CREATE TABLE payment ( | |
| id int primary key, | |
| client_id int, | |
| amount float, | |
| payment_date date | |
| ); | |
| INSERT INTO "payment" (id, client_id, amount, payment_date) | |
| values(1, 250, 49.26, '2019-03-22'), (2, 143, 79.66, '2019-02-19'), (3, 87, 59.66, '2019-10-22'), (4, 159, 29.37, '2018-03-16'), (5, 213, 97.10, '2019-05-06'), (6, 225, 86.02, '2018-05-01'), (7, 85, 90.51, '2019-06-17'), (8, 58, 96.20, '2019-10-27'), (9, 200, 87.65, '2018-08-24'), (10, 238, 62.82, '2018-04-24'), (11, 21, 75.02, '2019-03-17'), (12, 239, 80.02, '2018-07-13'), (13, 43, 37.26, '2019-02-16'), (14, 105, 48.98, '2019-06-06'), (15, 232, 70.09, '2019-08-22'), (16, 237, 95.45, '2019-01-14'), (17, 171, 24.83, '2019-03-22'), (18, 195, 6.20, '2018-03-28'), (19, 20, 80.56, '2020-02-22'), (20, 142, 59.38, '2019-11-30'), (21, 211, 14.78, '2018-07-16'), (22, 58, 58.35, '2019-05-21'), (23, 120, 49.71, '2018-08-05'), (24, 202, 40.03, '2019-12-19'), (25, 225, 0.03, '2019-09-13'), (26, 296, 84.11, '2019-07-30'), (27, 5, 20.06, '2019-12-30'), (28, 94, 80.15, '2018-11-26'), (29, 59, 31.29, '2019-01-16'), (30, 140, 63.41, '2019-05-20'), (31, 38, 12.89, '2020-02-28'), (32, 5, 33.11, '2019-06-15'), (33, 270, 54.67, '2019-10-24'), (34, 57, 63.62, '2020-02-17'), (35, 272, 47.29, '2019-01-22'), (36, 197, 14.06, '2018-07-15'), (37, 300, 61.68, '2019-11-15'), (38, 187, 27.59, '2019-08-02'), (39, 265, 10.84, '2018-04-20'), (40, 115, 50.77, '2019-01-06'), (41, 158, 87.04, '2018-05-18'), (42, 142, 35.46, '2018-05-07'), (43, 184, 43.31, '2019-12-01'), (44, 132, 83.17, '2018-08-23'), (45, 19, 55.77, '2019-05-10'), (46, 69, 32.62, '2018-05-29'), (47, 183, 9.35, '2018-10-01'), (48, 32, 83.77, '2019-03-11'), (49, 173, 35.34, '2019-10-06'), (50, 157, 76.70, '2020-03-03'), (51, 217, 10.12, '2018-09-02'), (52, 13, 96.98, '2019-03-22'), (53, 233, 92.51, '2020-02-28'), (54, 295, 70.59, '2018-11-01'), (55, 139, 95.08, '2019-08-28'), (56, 252, 54.91, '2020-02-11'), (57, 142, 95.29, '2019-06-07'), (58, 97, 4.17, '2018-03-27'), (59, 13, 13.46, '2019-01-15'), (60, 240, 9.42, '2019-09-30'), (61, 156, 20.74, '2018-09-13'), (62, 223, 96.67, '2018-06-09'), (63, 102, 94.70, '2018-11-09'), (64, 63, 93.42, '2019-01-15'), (65, 83, 50.04, '2019-05-08'), (66, 238, 56.34, '2019-10-16'), (67, 119, 17.30, '2019-07-07'), (68, 49, 19.21, '2019-05-28'), (69, 247, 53.30, '2019-09-19'), (70, 33, 89.84, '2018-11-14'), (71, 74, 61.08, '2019-11-19'), (72, 74, 68.71, '2018-12-03'), (73, 201, 23.49, '2018-05-20'), (74, 147, 28.70, '2019-08-07'), (75, 78, 67.23, '2019-06-12'), (76, 130, 26.95, '2019-05-14'), (77, 184, 57.05, '2019-04-06'), (78, 78, 6.78, '2019-04-16'), (79, 265, 90.80, '2020-01-25'), (80, 151, 7.78, '2019-02-23'), (81, 27, 93.83, '2019-10-13'), (82, 207, 98.92, '2018-04-30'), (83, 124, 35.77, '2019-07-15'), (84, 79, 73.72, '2020-01-03'), (85, 215, 99.76, '2019-10-31'), (86, 184, 59.34, '2019-02-10'), (87, 60, 81.91, '2019-01-26'), (88, 206, 57.06, '2020-02-15'), (89, 120, 56.41, '2018-09-20'), (90, 187, 85.42, '2018-04-05'), (91, 132, 7.05, '2020-03-01'), (92, 196, 91.79, '2020-01-07'), (93, 244, 17.66, '2019-01-11'), (94, 36, 6.52, '2019-04-25'), (95, 115, 60.81, '2019-05-30'), (96, 5, 58.00, '2018-03-22'), (97, 66, 87.56, '2018-08-02'), (98, 282, 87.27, '2020-02-19'), (99, 16, 8.36, '2020-01-07'), (100, 173, 23.01, '2019-08-14'), (101, 179, 96.50, '2020-01-01'), (102, 10, 23.75, '2019-09-19'), (103, 298, 41.86, '2018-05-24'), (104, 196, 56.91, '2018-10-10'), (105, 4, 2.59, '2019-08-16'), (106, 148, 57.20, '2019-12-03'), (107, 299, 91.28, '2019-11-10'), (108, 299, 3.15, '2019-12-09'), (109, 264, 16.28, '2019-09-14'), (110, 117, 48.26, '2020-02-23'), (111, 27, 96.27, '2018-12-20'), (112, 112, 95.77, '2019-07-23'), (113, 104, 54.39, '2018-06-09'), (114, 141, 19.90, '2019-01-25'), (115, 107, 74.09, '2019-10-24'), (116, 137, 41.42, '2019-01-02'), (117, 56, 36.92, '2019-04-06'), (118, 145, 72.86, '2019-10-30'), (119, 108, 61.92, '2018-12-10'), (120, 155, 4.65, '2018-10-16'), (121, 220, 99.96, '2019-12-17'), (122, 39, 41.66, '2019-08-16'), (123, 186, 0.76, '2018-07-09'), (124, 281, 20.31, '2018-03-26'), (125, 227, 84.09, '2018-09-17'), (126, 8, 47.49, '2018-11-03'), (127, 179, 39.42, '2020-02-16'), (128, 65, 65.36, '2019-03-11'), (129, 25, 92.02, '2019-10-16'), (130, 58, 7.46, '2019-04-04'), (131, 290, 19.07, '2018-03-21'), (132, 37, 78.28, '2019-09-02'), (133, 153, 44.76, '2018-10-16'), (134, 284, 92.16, '2019-04-15'), (135, 61, 77.33, '2020-01-14'), (136, 2, 22.48, '2018-09-20'), (137, 52, 78.43, '2020-01-13'), (138, 205, 26.53, '2018-11-21'), (139, 107, 57.65, '2019-03-26'), (140, 192, 92.21, '2019-06-17'), (141, 255, 50.24, '2020-01-10'), (142, 97, 4.12, '2018-03-14'), (143, 134, 65.45, '2019-07-30'), (144, 126, 53.70, '2018-07-17'), (145, 292, 9.30, '2018-07-30'), (146, 23, 65.14, '2018-10-17'), (147, 55, 17.32, '2018-10-05'), (148, 288, 47.61, '2020-03-13'), (149, 89, 7.94, '2019-04-03'), (150, 140, 59.47, '2019-10-16'), (151, 279, 87.93, '2018-06-10'), (152, 98, 76.91, '2019-09-30'), (153, 84, 39.07, '2019-02-10'), (154, 142, 83.93, '2020-02-11'), (155, 118, 23.60, '2019-09-30'), (156, 109, 65.65, '2018-06-22'), (157, 284, 21.02, '2019-11-01'), (158, 15, 23.01, '2019-08-27'), (159, 74, 24.94, '2019-09-26'), (160, 187, 84.89, '2018-11-30'), (161, 286, 36.45, '2018-09-23'), (162, 111, 57.93, '2019-12-17'), (163, 17, 10.83, '2018-03-24'), (164, 73, 21.46, '2019-04-06'), (165, 292, 88.55, '2018-08-09'), (166, 51, 86.69, '2018-03-27'), (167, 94, 92.53, '2018-04-13'), (168, 114, 62.87, '2019-03-21'), (169, 27, 90.43, '2018-04-16'), (170, 57, 32.52, '2019-06-20'), (171, 225, 3.36, '2019-10-08'), (172, 123, 5.25, '2020-01-31'), (173, 8, 31.84, '2019-07-11'), (174, 276, 17.25, '2019-11-24'), (175, 288, 53.93, '2019-09-15'), (176, 206, 78.03, '2019-12-01'), (177, 146, 69.19, '2019-02-01'), (178, 52, 63.26, '2019-02-23'), (179, 109, 47.61, '2020-03-07'), (180, 207, 67.66, '2019-06-29'), (181, 193, 28.44, '2018-10-17'), (182, 184, 84.89, '2019-01-26'), (183, 197, 78.57, '2019-01-31'), (184, 239, 32.36, '2019-12-08'), (185, 48, 14.98, '2019-05-14'), (186, 32, 3.05, '2019-07-17'), (187, 253, 34.65, '2020-03-06'), (188, 64, 30.51, '2019-06-25'), (189, 111, 33.04, '2020-01-01'), (190, 224, 82.32, '2019-07-03'), (191, 202, 86.76, '2019-02-12'), (192, 26, 40.61, '2018-10-14'), (193, 95, 21.95, '2018-07-08'), (194, 69, 43.06, '2018-04-07'), (195, 52, 59.47, '2019-05-29'), (196, 157, 47.86, '2019-07-16'), (197, 93, 1.80, '2019-11-17'), (198, 269, 2.92, '2018-06-09'), (199, 229, 76.02, '2020-03-08'), (200, 62, 37.55, '2019-11-25'), (201, 20, 34.52, '2019-08-19'), (202, 104, 96.50, '2018-11-28'), (203, 84, 75.66, '2019-10-17'), (204, 188, 33.64, '2018-03-21'), (205, 258, 54.27, '2018-07-28'), (206, 284, 82.56, '2019-09-24'), (207, 249, 13.91, '2019-12-31'), (208, 122, 22.88, '2020-03-05'), (209, 31, 76.33, '2018-12-04'), (210, 91, 38.69, '2019-06-16'), (211, 106, 63.93, '2019-01-28'), (212, 156, 75.28, '2019-04-01'), (213, 274, 91.04, '2018-10-01'), (214, 283, 35.08, '2018-06-03'), (215, 168, 18.43, '2019-09-25'), (216, 296, 0.77, '2018-06-08'), (217, 291, 47.28, '2019-03-22'), (218, 191, 80.08, '2019-06-23'), (219, 87, 24.75, '2018-09-17'), (220, 210, 74.05, '2019-09-02'), (221, 50, 34.13, '2019-07-02'), (222, 203, 69.18, '2018-07-10'), (223, 248, 79.85, '2018-07-20'), (224, 65, 28.22, '2018-09-25'), (225, 276, 53.78, '2018-12-01'), (226, 79, 45.87, '2019-10-11'), (227, 175, 69.82, '2019-06-19'), (228, 238, 27.51, '2020-01-09'), (229, 88, 36.24, '2019-05-26'), (230, 13, 31.48, '2018-08-20'), (231, 134, 70.14, '2019-04-04'), (232, 119, 9.66, '2018-05-07'), (233, 27, 83.49, '2018-03-18'), (234, 114, 25.36, '2019-09-12'), (235, 208, 4.24, '2018-07-12'), (236, 283, 84.45, '2018-07-22'), (237, 242, 4.31, '2019-03-11'), (238, 271, 94.39, '2019-05-04'), (239, 82, 72.99, '2019-01-06'), (240, 297, 2.02, '2018-08-20'), (241, 35, 71.00, '2018-05-28'), (242, 90, 21.61, '2019-02-05'), (243, 162, 88.94, '2019-08-21'), (244, 54, 33.80, '2018-11-22'), (245, 299, 33.20, '2018-07-06'), (246, 171, 78.29, '2018-07-09'), (247, 177, 59.33, '2019-05-29'), (248, 142, 50.98, '2019-06-21'), (249, 185, 99.89, '2019-02-26'), (250, 236, 41.24, '2019-07-04'), (251, 167, 41.55, '2020-02-17'), (252, 276, 91.10, '2020-01-03'), (253, 297, 32.93, '2019-06-26'), (254, 86, 27.96, '2018-11-28'), (255, 21, 59.54, '2018-09-09'), (256, 254, 7.79, '2019-12-30'), (257, 88, 86.24, '2019-07-04'), (258, 130, 13.00, '2019-05-17'), (259, 11, 87.34, '2019-06-17'), (260, 178, 86.44, '2018-12-28'), (261, 165, 54.73, '2019-12-16'), (262, 242, 97.90, '2020-02-19'), (263, 196, 64.90, '2019-04-14'), (264, 296, 29.07, '2019-02-10'), (265, 56, 29.53, '2019-08-21'), (266, 134, 76.91, '2019-12-07'), (267, 186, 69.61, '2018-11-19'), (268, 94, 60.73, '2019-11-02'), (269, 126, 91.68, '2020-03-03'), (270, 95, 47.72, '2018-06-09'), (271, 162, 4.86, '2019-09-03'), (272, 116, 64.39, '2018-05-27'), (273, 300, 46.96, '2018-12-29'), (274, 283, 86.80, '2019-09-28'), (275, 137, 37.40, '2019-10-27'), (276, 259, 84.89, '2018-05-02'), (277, 244, 35.10, '2018-05-15'), (278, 39, 81.61, '2019-09-07'), (279, 83, 47.96, '2019-07-23'), (280, 271, 59.78, '2020-02-28'), (281, 102, 79.76, '2020-02-28'), (282, 148, 39.36, '2018-06-29'), (283, 115, 10.40, '2019-07-04'), (284, 209, 5.77, '2018-11-09'), (285, 177, 27.91, '2019-10-10'), (286, 242, 36.13, '2019-09-18'), (287, 209, 35.21, '2018-08-27'), (288, 62, 3.51, '2018-11-15'), (289, 180, 66.71, '2019-12-31'), (290, 151, 92.95, '2019-04-24'), (291, 266, 95.52, '2019-07-07'), (292, 95, 74.13, '2019-07-04'), (293, 37, 53.75, '2018-07-22'), (294, 150, 14.77, '2018-07-25'), (295, 12, 0.61, '2019-10-19'), (296, 59, 3.16, '2019-06-10'), (297, 107, 62.43, '2019-08-27'), (298, 140, 10.37, '2018-12-13'), (299, 22, 74.09, '2019-11-12'), (300, 186, 13.76, '2020-01-12'), (301, 295, 69.47, '2019-10-14'), (302, 246, 96.50, '2018-11-11'), (303, 63, 32.21, '2019-02-26'), (304, 106, 37.54, '2019-09-12'), (305, 28, 27.95, '2018-11-06'), (306, 250, 35.33, '2019-08-29'), (307, 281, 67.64, '2019-10-17'), (308, 13, 61.16, '2018-06-26'), (309, 122, 58.02, '2019-10-05'), (310, 232, 21.13, '2019-12-23'), (311, 129, 37.28, '2018-05-31'), (312, 185, 61.57, '2019-08-02'), (313, 230, 53.03, '2019-05-02'), (314, 155, 19.64, '2019-11-19'), (315, 241, 81.02, '2019-09-15'), (316, 218, 49.51, '2019-10-17'), (317, 235, 44.42, '2019-09-09'), (318, 7, 59.12, '2018-05-24'), (319, 218, 99.57, '2020-02-28'), (320, 152, 88.09, '2019-06-13'), (321, 81, 91.03, '2018-11-09'), (322, 79, 67.69, '2019-11-12'), (323, 37, 83.92, '2019-04-05'), (324, 191, 78.88, '2019-08-20'), (325, 102, 93.34, '2018-10-29'), (326, 96, 12.02, '2019-11-01'), (327, 254, 48.19, '2019-07-18'), (328, 269, 85.83, '2020-01-31'), (329, 95, 98.43, '2018-07-19'), (330, 225, 46.59, '2019-08-01'), (331, 258, 78.52, '2019-01-20'), (332, 6, 98.30, '2019-05-19'), (333, 211, 72.55, '2019-03-06'), (334, 276, 71.48, '2019-11-27'), (335, 69, 86.96, '2018-10-06'), (336, 165, 30.35, '2018-06-12'), (337, 220, 82.76, '2018-06-09'), (338, 101, 35.84, '2019-09-21'), (339, 91, 81.37, '2018-07-08'), (340, 187, 5.86, '2019-11-14'), (341, 127, 93.11, '2019-08-17'), (342, 26, 25.06, '2019-08-01'), (343, 238, 46.46, '2019-06-07'), (344, 78, 34.90, '2018-05-23'), (345, 257, 12.04, '2019-12-01'), (346, 299, 4.65, '2018-08-30'), (347, 210, 81.43, '2018-03-29'), (348, 142, 20.85, '2018-09-13'), (349, 119, 70.37, '2019-03-22'), (350, 245, 36.03, '2019-10-02'), (351, 264, 79.27, '2018-12-08'), (352, 291, 54.25, '2018-12-08'), (353, 152, 17.53, '2018-05-17'), (354, 134, 77.96, '2018-09-29'), (355, 43, 61.74, '2018-07-08'), (356, 151, 29.55, '2019-09-20'), (357, 221, 4.01, '2019-01-24'), (358, 128, 81.76, '2018-07-26'), (359, 299, 69.56, '2018-09-15'), (360, 290, 82.49, '2019-02-28'), (361, 60, 84.31, '2019-02-27'), (362, 142, 19.33, '2020-02-23'), (363, 82, 43.89, '2019-05-15'), (364, 202, 55.45, '2019-12-01'), (365, 125, 29.40, '2019-03-21'), (366, 237, 59.61, '2020-02-06'), (367, 294, 70.25, '2018-06-15'), (368, 296, 73.75, '2019-05-22'), (369, 32, 93.15, '2019-06-22'), (370, 202, 70.73, '2018-09-08'), (371, 212, 93.28, '2019-05-21'), (372, 250, 63.84, '2019-02-04'), (373, 250, 56.15, '2018-09-14'), (374, 216, 79.26, '2018-09-23'), (375, 182, 4.04, '2019-03-06'), (376, 297, 26.52, '2019-09-28'), (377, 191, 98.58, '2018-09-03'), (378, 113, 11.12, '2019-11-05'), (379, 279, 45.70, '2018-09-11'), (380, 232, 91.94, '2019-11-26'), (381, 282, 31.10, '2019-04-02'), (382, 214, 93.13, '2018-07-29'), (383, 22, 23.47, '2018-05-07'), (384, 269, 21.27, '2018-05-18'), (385, 138, 20.76, '2018-07-31'), (386, 118, 9.87, '2018-06-25'), (387, 263, 6.92, '2019-07-05'), (388, 282, 79.19, '2018-08-10'), (389, 299, 72.96, '2018-07-14'), (390, 149, 41.09, '2020-02-23'), (391, 269, 78.14, '2018-08-25'), (392, 146, 76.84, '2018-12-22'), (393, 101, 98.61, '2018-12-11'), (394, 123, 49.41, '2019-09-03'), (395, 38, 1.52, '2019-04-07'), (396, 293, 63.68, '2019-07-07'), (397, 46, 54.69, '2019-05-19'), (398, 73, 35.29, '2018-04-17'), (399, 141, 23.06, '2018-12-16'), (400, 151, 62.83, '2018-04-03'), (401, 230, 92.05, '2019-07-18'), (402, 1, 98.40, '2019-05-09'), (403, 154, 46.19, '2019-02-11'), (404, 210, 18.35, '2020-01-15'), (405, 262, 57.32, '2019-04-25'), (406, 177, 14.59, '2019-10-13'), (407, 9, 39.37, '2019-04-22'), (408, 29, 28.92, '2018-11-26'), (409, 267, 30.80, '2019-04-01'), (410, 165, 69.43, '2018-07-06'), (411, 126, 30.12, '2019-08-01'), (412, 130, 67.41, '2019-06-27'), (413, 156, 8.35, '2019-06-23'), (414, 203, 9.91, '2019-07-14'), (415, 221, 7.56, '2018-06-18'), (416, 93, 76.75, '2019-08-01'), (417, 267, 48.21, '2019-09-27'), (418, 197, 26.84, '2019-09-14'), (419, 153, 16.00, '2019-07-17'), (420, 160, 91.67, '2019-02-15'), (421, 23, 35.84, '2020-02-12'), (422, 113, 21.91, '2018-06-14'), (423, 217, 60.97, '2018-05-27'), (424, 88, 96.43, '2019-01-02'), (425, 267, 45.45, '2020-03-13'), (426, 131, 51.29, '2019-02-24'), (427, 236, 17.77, '2019-07-24'), (428, 295, 66.58, '2019-12-28'), (429, 235, 91.48, '2018-04-21'), (430, 173, 39.86, '2019-03-30'), (431, 147, 33.06, '2018-04-03'), (432, 291, 66.45, '2019-04-11'), (433, 68, 58.32, '2018-04-18'), (434, 153, 4.47, '2019-09-13'), (435, 171, 8.31, '2018-09-24'), (436, 159, 91.91, '2019-05-27'), (437, 221, 97.11, '2018-04-14'), (438, 220, 73.27, '2020-02-03'), (439, 26, 98.73, '2018-12-27'), (440, 245, 77.56, '2019-08-18'), (441, 55, 15.45, '2019-06-03'), (442, 169, 1.42, '2019-06-17'), (443, 214, 54.10, '2018-09-06'), (444, 162, 50.39, '2018-12-24'), (445, 113, 71.85, '2019-05-03'), (446, 19, 55.27, '2018-04-13'), (447, 103, 98.57, '2019-11-08'), (448, 154, 0.75, '2019-09-30'), (449, 239, 56.68, '2019-04-26'), (450, 128, 39.34, '2020-02-10'), (451, 264, 99.92, '2019-08-20'), (452, 142, 80.24, '2019-05-23'), (453, 183, 66.77, '2018-10-02'), (454, 219, 53.15, '2019-11-30'), (455, 231, 36.69, '2019-04-09'), (456, 127, 19.85, '2019-06-29'), (457, 63, 88.91, '2019-01-16'), (458, 283, 87.80, '2019-04-11'), (459, 57, 12.11, '2019-03-28'), (460, 236, 8.05, '2018-08-26'), (461, 244, 99.80, '2019-11-22'), (462, 229, 52.28, '2020-01-23'), (463, 182, 37.54, '2018-03-20'), (464, 232, 67.47, '2018-12-02'), (465, 114, 24.18, '2018-10-12'), (466, 293, 84.47, '2020-02-16'), (467, 13, 47.08, '2018-05-13'), (468, 147, 71.32, '2019-01-13'), (469, 163, 30.79, '2018-10-17'), (470, 120, 51.00, '2019-09-12'), (471, 147, 63.10, '2019-06-21'), (472, 52, 49.82, '2019-10-24'), (473, 170, 11.41, '2020-02-18'), (474, 292, 77.74, '2018-04-23'), (475, 81, 78.16, '2019-04-04'), (476, 184, 5.66, '2018-07-28'), (477, 283, 96.99, '2019-07-05'), (478, 19, 37.78, '2019-07-06'), (479, 133, 31.78, '2020-03-05'), (480, 193, 53.67, '2018-09-27'), (481, 171, 27.91, '2018-05-16'), (482, 66, 55.26, '2018-11-14'), (483, 216, 0.25, '2019-04-18'), (484, 246, 97.57, '2018-12-20'), (485, 36, 20.17, '2018-10-04'), (486, 75, 54.96, '2018-11-22'), (487, 19, 0.94, '2018-11-25'), (488, 239, 24.60, '2018-07-08'), (489, 292, 46.57, '2018-09-27'), (490, 9, 69.32, '2019-11-05'), (491, 88, 57.25, '2018-12-10'), (492, 260, 87.53, '2018-11-15'), (493, 164, 38.20, '2018-06-27'), (494, 190, 31.18, '2020-01-06'), (495, 22, 57.80, '2018-10-17'), (496, 68, 91.63, '2019-10-08'), (497, 57, 98.60, '2018-10-11'), (498, 137, 12.50, '2019-08-31'), (499, 275, 82.38, '2018-08-10'), (500, 213, 2.19, '2019-02-05'); |
עבור כל תשלום אנחנו שומרים רשומה הכוללת את מספר הלקוח ומספר נתונים על התשלום עצמו כמו סכום התשלום ואת תאריך התשלום.
כדי לראות מה שרת ה-SQL עושה מאחורי הקלעים נוסיף את המילים הבאות EXPLAIN ANALYZE לתחילת השאילתא. הפקודה EXPLAIN מורה לשרת להציג את הצעדים הלוגיים שקורים מאחורי הקלעים של השאילתא. המילה ANALYZE מורה לשרת להריץ בפועל את השאילתא, ובכך לאפשר לשרת להציג סטטיסטיקות ואנליזות שונות על ההרצה בפועל של השאילתא. נשים לב שבגלל ששימוש בפקודה EXPLAIN בלבד לא יריץ את השאילתא בפועל, נוכל להשתמש בפקודה הזאת באופן נרחב כדי לדבג את השאילתות שלנו. הוספת הפקודה ANALYZE מריצה את השאילתא בפועל, ולכן כדאי לנו להיות זהירים בשימוש בה כדי שלא נגיע למצב בו פגענו בנתונים בגלל הניסויים שלנו.
כעת נתבונן בשאילתא הפשוטה הבאה:
| EXPLAIN ANALYZE | |
| SELECT * | |
| FROM payment | |
| WHERE payment_date = '2020-01-13' | |
| ORDER BY amount; |
הפלט של הפקודה הוא תיאור ה-execution plan הזה:
Sort Key: amount
Sort Method: quicksort Memory: 25kB nbsp; -> Seq Scan on payment (cost=0.00..10.25 rows=1 width=20) (actual time=0.063..0.072 rows=1 loops=1)
Filter: (payment_date = '2020-01-13'::date)
Rows Removed by Filter: 499
Planning time: 0.089 ms
Execution time: 0.095 ms
מתוך שפע המידע שאנו מקבלים מה-execution plan נתרכז בחלקים היותר רלוונטיים. סימנו בצהוב את הפעולות הלוגיות שהשרת מבצע בהן נתמקד ובתכלת את טווח הזמנים בו רצה כל פעולה יחסית לתחילת הריצה. כאמור , בגלל שהשתמשנו בפקודה ANALYZE, אנו מקבלים את הנתונים האמיתיים על הריצה בפועל של השאילתא.
הנתונים מאוחסנים בשרת לפי שורות, כאשר כל שורה נמצאת באזור אחד על הדיסק. כדי לסנן לפי עסקאות שנערכו בתאריך מסוים, השרת מבצע סריקה שיטתית על כל שורות הטבלה ובוחר רק את השורות שמתאימות לקריטריון שהגדרנו. את השורות המוחזרות הוא ממיין לפי הערך בשדה amount. ניתן לראות, למשל, שסריקת הטבלה אירעה בין נקודת הזמן של 0.063 מילישניות ו-0.072 מילישניות, כך בסך הכל היא ארכה 0.009 מילישניות.
מטבע הדברים שאילתות פשוטות יתורגמו ל-execution plan טריוויאלי ולא צפוי שניתקל בהפתעות מיוחדות. שאילתות מורכבות יותר צפויות לעבור אופטימיזציות מתוחכמות יותר, ונוכל ללמוד מה-execution plan יותר על התהליכים הפנימיים שמתבצעים.
אינדקס
אינדקס הוא כלי מפתח במסדי נתונים המאפשר לנו גישה לנתונים בצורה מהירה. נחשוב על ספר היסטוריה על מלכי צרפת בו על כל מלך יש פרק, כאשר הפרקים מסודרים לפי סדר כרונולוגי. אם נרצה למצוא את הפרק על המלך לותר כאשר איננו יודעים באיזו שנה הוא עלה לכס המלוכה, לא תהיה לנו ברירה ונצטרך לעבור על כל הפרקים עד שנמצא את הפרק המבוקש. למזלנו, בספרים מהסוג הזה יהיה בדרך כלל אינדקס: רשימה של כל המלכים המסודרת לפי סדר הא״ב, כאשר לכל מלך יש הפנייה לעמוד הרלוונטי בספר. כאשר האינדקס ממויין על פי הקריטריון הרצוי לנו (במקרה הזה, על פי שם המלך), אנחנו לא צריכים לעבור על כל הערכים באינדקס כדי למצוא את הערך המבוקש. מה שנעשה הוא חיפוש בינארי: נבחר ערך מאמצע הרשימה ונראה האם השם המבוקש נמצא במחצית הרשימה הראשונה או השנייה. בחצי הרשימה שנותרה נטפל שוב באותה הדרך, עד שבמהירות נצמצם את האפשרויות ונגיע לערך המבוקש. אם הרשימה של הערכים היא באורך n, אזי מספר החיפושים יהיה (log(n.
במקרה של השאילתא שלנו, מכיוון שפעמים רבות נרצה לסנן את היסטוריית התשלומים לפי התאריך נרצה להוסיף אינדקס מתאים. נריץ את הפקודה הבאה כדי ליצור אינדקס על העמודה payment_date:
| create index ix1 on payment(payment_date); |
נריץ את השאילתא שהרצנו קודם שוב, ונסתכל על ה-execution plan כדי לראות מה השתנה:
Sort Key: amount
Sort Method: quicksort Memory: 25kB
-> Index Scan using ix1 on payment (cost=0.27..8.29 rows=1 width=20) (actual time=0.018..0.019 rows=1 loops=1)
Index Cond: (payment_date = '2020-01-13'::date)
Planning time: 0.178 ms
Execution time: 0.058 ms
אנו רואים שכעת במקום לסרוק שיטתית את כל שורות הטבלה כדי לקרוא את הערך payment_date, השרת מבצע Index Scan – הוא מסוגל לבצע חיפוש בינארי של כל השורות המתאימות לתנאי של payment_date משום שיצרנו אינדקס לפי הערכים הללו. ואכן, אנו רואים שבמקום 0.043 מילישניות שארכה הסריקה בשאילתא הקודמת, הפעם הסריקה ארכה 0.001 מילישיניות בלבד. שיפור הביצועים ניכר גם בזמן הכללי שארך ביצוע השאילתא – 0.058 מילישיניות במקום 0.133 מילישיניות בשאילתא הקודמת.
אופטימיזציות מתקדמות
מסד הנתונים מנסה גם לעשות אופטימיזציות שלא תלויות רק בשאילתא אלא גם בנתונים. השאילתא האחרונה שהרצנו מחזירה שורה אחת ולכן תהליך המיון שאחריו לא קורה בפועל. נריץ שאילתא דומה, אך עם תאריך אחר בו היו חמש עסקאות.
| EXPLAIN ANALYZE | |
| SELECT * | |
| FROM payment | |
| WHERE payment_date = '2018-06-09' | |
| ORDER BY amount; |
מבחינה לוגית השאילתא היא אותה שאילתא אבל שרת הפוסטגרס יבצע אותה בדרך אחרת. נסתכל ב-execution plan של השאילתא השניה:
Sort Key: amount
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on payment (cost=4.31..8.37 rows=5 width=20) (actual time=0.022..0.025 rows=5 loops=1)
Recheck Cond: (payment_date = '2018-06-09'::date)
Heap Blocks: exact=3
-> Bitmap Index Scan on ix1 (cost=0.00..4.31 rows=5 width=0) (actual time=0.015..0.015 rows=5 loops=1)
Index Cond: (payment_date = '2018-06-09'::date)
Planning time: 0.156 ms
Execution time: 0.061 ms
אנו רואים שהשרת מתחיל עם סריקה על האינדקס אבל הוא מבצע גם Bitmap Heap Scan on payment. מה זה אומר?
סריקת אינדקס רגילה עוברת סדרתית על כל ערכי העמודה העונים לקריטריון. כל אחד מהמצביעים של האינדקס מצביע על המקום בטבלה בו שמורה השורה עצמה, והשרת שולף משם שורה אחר שורה. שליפה מהזיכרון היא פעולה יקרה, אך השרת עושה זאת בנפרד עבור כל שורה למרות שייתכן שיהיו שורות שונות באותו עמוד בזיכרון שהיו יכולות להיקרא ביחד על ידי שליפה אחת מהזיכרון.
סריקת ביטמפ לוקחת את כל המצביעים וממיינת אותם על פי המיקום בזיכרון (ששמור בביטמפ). כאשר המצביעים ממויינים לפי המיקום בזיכרון אפשר לשלוף את השורות על פי המיקום וכך לאחזר ביחד בקריאת דיסק אחת שורות שנמצאות באותו עמוד בזיכרון. בכלל, גישה למידע על פי סדר השמירה בדיסק היא מהירה משמעותית יחסית לגישה אקראית לדיסק.
מכיוון שהשרת לא יכול לשמור ביטמפ עבור כל שורה, נשמרות בביטמפ קבוצות של שורות שנמצאות באותו עמוד בזיכרון. כך שהשרת חייב לאחזר את כל השורות שנמצאות בעמוד ומתוך כל השורות הללו לסנן שוב את השורות הרצויות. מכיוון שגישה לדיסק היא פעולה יקרה, במיוחד כאשר היא נעשית אקראית, נעדיף לאחזר מידע על פי המיקום בדיסק גם במחיר של סינון חוזר. כך שהשרת לא רק ממיר את השאילתא לפקודות לוגיות אלא גם מנסה להבין מהי הדרך היעילה ביותר לעשות זאת.
לסיכום
ראינו שבעזרת ה-execution plan אפשר לקבל מושג על מה קורה מאחורי הקלעים של השאילתא ואילו פעולות עושה השרת כדי להביא לנו את הנתונים שביקשנו. כמובן שכאן נגענו רק בחלק קטן מתוך טווח האפשרויות שעומדות לרשותנו לניתוח השאילתות ולשימוש בתובנות כדי לשפר אותן.
מרתק ממש!
אני עובדת לא מעט עם שאילתות, פשוטות אמנם, ולא ניתקלתי בזה עד עכשיו.
האם עובד גם ב-mySql?
אני כ ר יכולה לחשוב על שאילתה שהייתי מאד רוצה לדעת מה קורה מאחורי הקלעים שלה כדי לאפטם אותה.
תודה רבה.
ודאי שזה עובד גם בmysql. ממה שאני רואה בחיפוש קצר בגוגל, ב-MySql הפורמט קצת שונה אבל הרעיון דומה בכל מסדי הנתונים.
מאמר על EXPLAIN ב-MySql
http://www.softwarearchiblog.com/2017/12/explain.html
אלוף! תודה.
מדהים חדשת לי המון!