capstone — planner ของคุณ เทียบกับ sqlite3

บทนี้ไม่มีโครงสร้างใหม่ให้สร้าง มีแต่การเอาของที่ประกอบมาเจ็ดบทไปวางข้างของจริงแล้วอ่านผล

เป้าหมายไม่ใช่ทำให้สองฝั่งตอบตรงกัน เราสร้างสิ่งที่อธิบายได้ว่าทำไมแผนของ SQLite ถึงหน้าตาแบบนั้น ไม่ใช่สิ่งที่ดีกว่า SQLite

สิ่งที่บทนี้ต้องทำให้ได้คืออธิบายทุกจุดที่สองฝั่งตอบไม่ตรงกัน ว่ามันย้อนกลับไปที่บทไหน ถ้าอธิบายไม่ได้ แปลว่าเรายังไม่เข้าใจแผนของตัวเอง

และวิธีแยกว่าความต่างข้อหนึ่งมาจากอะไร คือการทดลอง ไม่ใช่การอธิบาย

📦 code ของบทนี้

file ของบทนี้คือ qe/compare.py ซึ่ง import ของที่บท 2 ถึงบท 7 เขียนไว้แล้วหกตัว คือ heapfile, bptree, stats, cost, paths และ joins บวก fixture ของบท 1 และ sqlite3 ที่ติดมากับ Python อยู่แล้ว ยังไม่ต้องติดตั้งอะไรเพิ่มเหมือนเดิม

file นี้ยาวกว่าทุกบทที่ผ่านมามาก บทเรียนจึงยกมาเฉพาะช่วงที่อธิบาย และนี่เป็นบทเดียวในคอร์สที่ไม่ได้ยกมาทั้ง file ตัวเลขที่ตามมาจึงตรวจซ้ำได้ครบก็ต่อเมื่อรัน qe/compare.py ทั้ง file ไม่ใช่จากท่อนที่ยกมาในหน้านี้

ฐานข้อมูลที่ใช้เทียบคือ .build/orders.db ถ้ายังไม่มี qe/compare.py จะสร้างมันจาก fixture ชุดเดียวกับบท 1 แล้วสั่ง ANALYZE ถ้ายังไม่มีสถิติ ส่วนการทดลองทุกอันที่ต้องแก้อะไรสักอย่าง ทำบนสำเนาเสมอ ตัวจริงห้ามแตะ

สนามแข่ง — ของสองฝั่งวางอยู่บนข้อมูลชุดเดียวกันจริงไหม

สิ่งที่ต้องตรวจให้ผ่านก่อนจะเทียบแผนได้ คือจำนวนแถว ขนาดหน้า และจำนวนหน้าที่แต่ละฝั่งใช้เก็บของเดียวกัน

ก่อนจะเทียบแผน ต้องพิสูจน์ก่อนว่าสองฝั่งถือข้อมูลชุดเดียวกันอยู่จริง ไม่ใช่เชื่อเอาว่าเหมือนกัน

แถวใน SQLite เทียบกับใน fixture ได้ 200,000 ต่อ 200,000 แถว ตรงกัน
page_size ของ SQLite คือ 4096 B เท่ากับ PAGE_SIZE ที่บท 2 ตรึงไว้พอดี หน่วย “หน้า” ของสองฝั่งจึงวางทาบกันได้ตรงๆ
ทั้ง file ของ SQLite กินไป 11,009 หน้า

ตาราง dbstat ของ SQLite บอกได้ว่า 11,009 หน้านั้นไปอยู่ที่อะไรบ้าง และเทียบกับของที่เราสร้างเองในบท 2 กับบท 3 ได้ตรงๆ หกแถวข้างล่างรวมกันได้ 11,003 หน้า ส่วนอีก 6 หน้าที่เหลือเป็นของ sqlite_schema 1 หน้า sqlite_stat1 1 หน้า และ sqlite_stat4 4 หน้า

อะไร	SQLite (หน้า)	ของเรา (หน้า)	SQLite/เรา
`orders`	3,552	3,153	1.13
`idx_status_rest`	2,951	493	5.99
`idx_placed_at`	631	493	1.28
`idx_restaurant`	2,467	—	—
`idx_status`	872	—	—
`idx_customer`	530	—	—

ขีด — แปลว่าฝั่งเราไม่ได้สร้างโครงสร้างนั้นขึ้นมาจริง ไม่ใช่ว่าวัดไม่ได้ บทนี้สร้าง index ของตัวเองแค่สองตัว คือ idx_status_rest กับ idx_placed_at ไว้นับหน้าจริง ส่วนราคาของทุกแผน index คิดจากรูปร่างต้นไม้ที่บท 5 ตรึงไว้ ไม่ได้คิดจาก file index ที่สร้าง

ตาราง orders ของ SQLite ใหญ่กว่า heap ของเรา 1.13 เท่า เพราะของเราเก็บหก column แบบความยาวคงที่บวกชื่อร้าน ส่วน SQLite เก็บ record format ที่มี type กำกับราย column บวก rowid บวกที่ว่างเผื่อการแก้ไขในแต่ละหน้า

ส่วน index ห่างกันมากกว่านั้นมาก idx_status_rest ของ SQLite ใหญ่กว่าของเรา 5.99 เท่า เพราะคีย์ของมันเก็บค่าจริงซึ่งเป็นข้อความ ไม่ใช่เลข 4 byte แบบคีย์ของเรา

ตัวเลข 15.6 % ของบท 3 ที่บอกว่า index ทั้ง file กินแค่เศษเสี้ยวของ heap จึงเป็นตัวเลขของโครงสร้างชุดของเรา ไม่ใช่ของ index จริงที่เก็บข้อความ

orders ฝั่ง SQLite มี index ห้าตัว เป็นชุดเดียวกับที่บท 4 ใช้ทุกตัวอักษร เพื่อให้สองบทใช้ file เดียวกันได้

# index ที่คอร์สสอน — ชุดเดียวกับบท 4 เป๊ะ ๆ เพื่อให้ .build/orders.db ใช้ร่วมกันได้
INDEXES = ("CREATE INDEX idx_status ON orders(status)",
           "CREATE INDEX idx_restaurant ON orders(restaurant)",
           "CREATE INDEX idx_status_rest ON orders(status, restaurant)",
           "CREATE INDEX idx_placed_at ON orders(placed_at)",
           "CREATE INDEX idx_customer ON orders(customer_id)")

schema ฝั่ง SQLite ตรงกับหก column ของ fixture ทีละตัว แล้วเทแถวทั้งสองแสนเข้าไปรวดเดียว

        con = sqlite3.connect(DB_PATH)
        con.execute("CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER,"
                    " status TEXT, restaurant TEXT, placed_at INTEGER,"
                    " total_satang INTEGER)")
        con.executemany("INSERT INTO orders VALUES (?,?,?,?,?,?)", fixture.rows())
        for sql in INDEXES:
            con.execute(sql)
        con.commit()
        con.close()

ทุก connection ที่บทนี้ใช้อ่านแผนถูกตั้ง cached_statements=0 ไว้ล่วงหน้า เพื่อกันไม่ให้ได้แผนที่คอมไพล์ไว้ตั้งแต่ก่อนแก้สถิติกลับมาโดยไม่รู้ตัว

def connect(path):
    """cached_statements=0 สำคัญมาก: ถ้าไม่ปิด SQLite จะเอาคำสั่งที่คอมไพล์ไว้แล้วมาใช้ซ้ำ
    เราจึงอาจได้แผน 'ก่อนแก้สถิติ' กลับมาโดยไม่รู้ตัว ซึ่งทำให้หัวข้อ [6] อ่านผลผิดทั้งหัวข้อ"""
    return sqlite3.connect(path, cached_statements=0)

สถิติที่ ANALYZE เขียนไว้อยู่ในตาราง sqlite_stat1 รูปแบบ N k1 k2 ... โดยตัวเลขหลัง N คือจำนวนแถวเฉลี่ยที่มีค่าคีย์เท่ากัน

index	`sqlite_stat1`
`idx_customer`	`200000 100`
`idx_placed_at`	`200000 1`
`idx_restaurant`	`200000 20000`
`idx_status`	`200000 28572`
`idx_status_rest`	`200000 28572 2899`

นอกจากนั้น build ที่เราวัดยังมี sqlite_stat4 อยู่ 97 ตัวอย่าง ซึ่งเป็นชั้นที่สองของสถิติที่บท 4 แนะนำไว้แล้ว และเป็นชั้นที่หัวข้อถัดๆ ไปจะลบทิ้งบนสำเนา เพื่อดูว่ามันเปลี่ยนแผนข้อไหนบ้าง

บทนี้เป็นบทเดียวในคอร์สที่ผลผูกกับ build ของ SQLite

ตัวเลขแผนทั้งหมดในบทนี้วัดจาก runtime SQLite 3.53.4 ที่มากับ Python ของเครื่องที่ใช้วัด และ build นั้นเปิด SQLITE_ENABLE_STAT4 กับ SQLITE_ENABLE_DBSTAT_VTAB ไว้

build ที่ไม่ได้เปิด SQLITE_ENABLE_STAT4 จะให้แผนคนละแบบตั้งแต่หัวข้อแรกที่แตะสถิติ และ build ที่ไม่มี dbstat จะเทียบจำนวนหน้าของสองฝั่งไม่ได้เลย

ส่วนข้ออ้างเรื่อง ซอร์ส code ของ planner ที่บท 5 ยกมา ปักหมุดไว้ที่ SQLite 3.51.0 ซึ่งเป็นคนละตัวกับ runtime ที่วัดแผนในบทนี้ อย่าเขียนรวมเป็นเลขเดียว

22 คำถาม ชุดเดียวกัน และนิยามของคำว่า “ตรง”

ชุด query ที่ป้อนให้สองฝั่ง วิธีที่แต่ละฝั่งเลือกแผน และตารางผลว่าตรงกันกี่ข้อ

คำถามที่ป้อนให้สองฝั่งมี 22 ข้อ แบ่งเป็นสามกลุ่มตามบทที่มันมาจาก

กลุ่ม ก — 11 ข้อ ต้นทุนล้วนของบท 3 ถึงบท 5 คือ point lookup, ช่วงค่า และเงื่อนไขสอง column
กลุ่ม ข — 6 ข้อ ทางเข้าของบท 6 คือ covering, ลำดับ column และการเรียง
กลุ่ม ค — 5 ข้อ join ของบท 7 ซึ่งมีหัวข้อของตัวเองอยู่ท้ายบท

ฝั่งเราเลือกแผนจากค่าประมาณของบท 4 เท่านั้น ไม่เคยเห็นจำนวนแถวจริงสักข้อ ส่วนฝั่ง SQLite อ่านแผนด้วย EXPLAIN QUERY PLAN บน .build/orders.db ที่ ANALYZE แล้ว

def explain_plan(path, sql, params=()):
    """แผนของ SQLite แบบอ่านออก — บทนี้เท่านั้นที่ใช้ EXPLAIN QUERY PLAN ได้ (บท 1-7 ห้าม)"""
    con = connect(path)
    out = [r[3] for r in con.execute("EXPLAIN QUERY PLAN " + sql, params)]
    con.close()
    return out

มี self-check คุมอยู่หนึ่งชั้น คือจำนวนแถวที่ SQLite คืนต้องตรงกับที่นับจาก fixture ทุกข้อ และมันตรงทุกข้อจริง

ก่อนดูตาราง ต้องประกาศนิยามของคำว่า “ตรง” ให้ชัดก่อน เพราะสัดส่วนที่ได้เป็นผลของนิยาม ไม่ใช่ของ planner อย่างเดียว

“ตรง” ในบทนี้แปลว่าเลือกทางเข้าเดียวกัน คือทั้งคู่สแกนทั้งตาราง หรือทั้งคู่ใช้ index ตัวเดียวกัน ส่วนรายละเอียดว่าเดินกี่ช่วงหรือเรียงตอนไหน ไม่ถูกนับ

ในตาราง สแกน แปลว่าอ่านทั้งตาราง cover แปลว่าตอบจากคีย์ล้วนโดยไม่ต้องเปิดแถว และ skip แปลว่าใช้ index ทั้งที่ column หน้าของมันไม่ได้ถูกถาม

	คำถาม	แถวจริง	ฝั่งเรา	ฝั่ง SQLite	ผล
A1	`id = 12345`	1	rowid(PK)	rowid(PK)	ตรง
A2	`customer_id = 995`	97	`idx_customer`	`idx_customer`	ตรง
A3	`status = 'Placed'`	199	สแกน	`idx_status`	ต่าง
A4	`status = 'Delivered'`	163,733	สแกน	สแกน	ตรง
A5	`restaurant = 'ครัวคุณแม่'`	11,934	สแกน	`idx_restaurant`	ต่าง
A6	Rejected + ครัวสุขใจ (คู่ที่ไม่มีจริง)	0	สแกน	`idx_status_rest`	ต่าง
A7	Rejected + ครัวคุณแม่ (คู่ที่มีจริง)	8,126	สแกน	`idx_status_rest`	ต่าง
A8	`placed_at` วันที่ 90 (วันธรรมดา)	1,017	`idx_placed_at`	`idx_placed_at`	ตรง
A9	`placed_at` วันที่ 140 (วันโปรโมชัน)	17,603	`idx_placed_at`	`idx_placed_at`	ตรง
A10	`placed_at` 60 วันแรก	20,657	`idx_placed_at`	`idx_placed_at`	ตรง
A11	`placed_at` ทั้ง 180 วัน	200,000	สแกน	สแกน	ตรง
B1	`COUNT(*)` ของ `status='Delivered'`	163,733	cover `idx_status`	cover `idx_status`	ตรง
B2	`GROUP BY status` ทั้งตาราง	200,000	สแกน cover `idx_status`	สแกน cover `idx_status`	ตรง
B3	`GROUP BY status` ของร้านครัวคุณแม่	11,934	cover `idx_status_rest` x7	cover skip `idx_status_rest`	ตรง
B4	`ORDER BY placed_at LIMIT 20`	20	`idx_placed_at` (เรียงมาแล้ว)	สแกน `idx_placed_at`	ตรง
B5	`ORDER BY total_satang LIMIT 20`	200,000	สแกนแล้วเรียง	สแกน แล้วเรียงชั่วคราว	ตรง
B6	`restaurant='ครัวคุณแม่' ORDER BY status`	11,934	สแกน	skip `idx_status_rest`	ต่าง

ผลรวมคือตรงกัน 12 จาก 17 ข้อ ต่างกัน 5 ข้อ คือ A3, A5, A6, A7 และ B6 แยกเป็นกลุ่ม ก ตรง 7 จาก 11 ข้อ และกลุ่ม ข ตรง 5 จาก 6 ข้อ

สัดส่วนนี้ไม่ได้ตั้งเป้าไว้ล่วงหน้า มันคือผลที่วัดได้ ไม่ว่าจะออกมาเท่าไร และไม่มีการปรับ query ให้ตัวเลขสวยขึ้น

คู่ที่น่าดูที่สุดในตารางคือ A4 กับ B1 เงื่อนไขเดียวกันเป๊ะ ตรง 163,733 แถวเท่ากัน แต่ A4 ที่ขอทุก column ทำให้ทั้งสองฝั่งสแกน ส่วน B1 ที่ขอแค่ COUNT(*) ทำให้ทั้งสองฝั่งย้ายไป covering index

สิ่งที่พลิกแผนของ A4 กับ B1 จึงไม่ใช่เงื่อนไข แต่เป็น column ที่ query ขอ ซึ่งเป็นประเด็นทั้งหมดของบท 6

ทำไมข้อ `placed_at` ถึงตรงกันหมด

A8, A9 และ A10 ตรงกันทั้งสามข้อ และตัวเลขหน้าที่แตะของฝั่งเราก็สวยผิดปกติ เพราะ heap ของคอร์สนี้เรียงตาม placed_at อยู่แล้วตั้งแต่ fixture แถวที่ตรงช่วงเวลาเดียวกันจึงกองอยู่หน้าเดียวกัน

เทียบกับ A2 ที่สองฝั่งก็ตรงกันเหมือนกัน แต่ตรงแค่ 97 แถวและถามด้วย customer_id แถวเหล่านั้นกระจายอยู่คนละหน้ากันแทบทุกแถว ซึ่งบท 3 วัดไว้ให้ดูแล้ว

ทั้งสองข้อนี้ตรงกันทั้งคู่ก็จริง แต่มาจากคนละเหตุผล และเหตุผลข้อแรกเป็นสมบัติของ fixture ไม่ใช่ของ index

แยกต้นเหตุด้วยการทดลอง ไม่ใช่ด้วยการอธิบาย

วิธีทดลองที่ตัดสินว่าความต่างข้อหนึ่งมาจากการเดาจำนวนแถว หรือมาจากการตีราคา และผลของมันกับทั้งห้าข้อ

เวลาสองฝั่งเลือกไม่เหมือนกัน มันเดาว่าเป็นเพราะอะไรก็ได้ทั้งนั้น การทดลองที่ใช้แยกจึงต้องตอบด้วยตัวเลข ไม่ใช่ด้วยเหตุผล

วิธีคือป้อน จำนวนแถวจริง เข้าไปแทนค่าประมาณ ให้ cost model ตัวเดิมที่ไม่ได้แก้อะไรเลย แล้วดูว่าคำตอบของเราเปลี่ยนไหม

เปลี่ยนไปตรงกับ SQLite แปลว่าราคาของเราถูกอยู่แล้ว สิ่งที่ผิดคือตัวประมาณจำนวนแถวของบท 4
ยังตอบเหมือนเดิม แปลว่าต่อให้รู้ความจริงเราก็ยังเลือกแบบเดิม สิ่งที่ผิดคือ cost model ของบท 5 กับบท 6

	ค่าประมาณของเรา	จริง	q-error	เราเลือก	ถ้ารู้ความจริงเราจะเลือก	ต้นเหตุ
A3	28,571.4	199	143.58	สแกน	`idx_status`	ตัวประมาณ (บท 4)
A5	20,000.0	11,934	1.68	สแกน	สแกน	cost model (บท 5-6)
A6	1,565.6	0	1565.58	สแกน	`idx_status_rest`	ตัวประมาณ (บท 4)
A7	785.6	8,126	10.34	สแกน	สแกน	cost model (บท 5-6)
B6	20,000.0	11,934	1.68	สแกน	สแกน	cost model (บท 5-6)

แถว A6 มีของจริง 0 แถว q-error ของมันจึงเป็นผลของการยกพื้นของจริงขึ้นเป็น 1 แถวก่อนหาร ไม่ใช่ค่าความคลาดที่วัดได้

ผลคือตัวประมาณ 2 ข้อ คือ A3 กับ A6 และ cost model 3 ข้อ คือ A5, A7 และ B6

สิ่งที่ตัดสินไม่ใช่ขนาดของ q-error แต่คือค่าประมาณกับความจริงตกคนละฝั่งของจุดตัด 394 แถวหรือเปล่า A3 กับ A6 ตกคนละฝั่ง คำตอบจึงพลิก ส่วน A5, A7 และ B6 อยู่เหนือจุดตัดทั้งค่าประมาณและความจริง จึงตอบเหมือนเดิมทั้งสามข้อ แม้ A7 จะพลาดไป 10.34 เท่าก็ตาม

flowchart TD
    QQ["คำถามหนึ่งข้อ<br/>สองฝั่งเลือกทางเข้าคนละทาง"] --> TT["ป้อนจำนวนแถวจริง<br/>เข้า cost model เดิมของเรา"]
    TT --> DEC{"คำตอบของเราเปลี่ยนไหม"}
    DEC -->|"เปลี่ยนไปตรงกับ SQLite"| EST["ต้นเหตุคือตัวประมาณจำนวนแถว<br/>บท 4 — A3 กับ A6"]
    DEC -->|"ยังตอบเหมือนเดิม"| CM["ต้นเหตุคือ cost model<br/>บท 5-6 — A5 A7 และ B6"]
    QQ --> THIRD["ต้นเหตุแบบที่สาม<br/>ที่โผล่มาตอนทำหัวข้อ join"]
    THIRD --> REW["SQLite เขียนเงื่อนไขใหม่ก่อนคิดราคา<br/>ไม่ใช่ตัวประมาณ ไม่ใช่ราคา"]
    EST --> S4["ชั้นสถิติที่ SQLite มี แต่เราไม่มี"]
    CM --> XO["จุดตัดของสองฝั่งอยู่คนละที่"]

คำบรรยายภาพ: การทดลองที่แยกต้นเหตุมีขั้นเดียว คือป้อนจำนวนแถวจริงเข้า cost model เดิม · กิ่งซ้ายคือความต่างที่หายไปเมื่อรู้ความจริง ต้นเหตุจึงอยู่ที่การเดาจำนวนแถวของบท 4 · กิ่งขวาคือความต่างที่ยังอยู่ แปลว่าสองฝั่งตีราคาไม่เท่ากัน · กิ่งล่างคือต้นเหตุแบบที่สามที่เจอตอนลงไปดูแผน join

ต้นเหตุที่หนึ่ง — ตัวประมาณจำนวนแถว

ค่าประมาณของสองฝั่งที่ A3 สถิติชั้นที่สองที่ SQLite มีแต่เราไม่มี และผลของการทดลองเอาสถิติชั้นนั้นออก ซึ่งไม่ได้ออกมาอย่างที่ตั้งเกณฑ์ไว้

ตัวประมาณของเราสำหรับ status คือ n / distinct ตัวเดียว ได้ 200,000 หารด้วย 7 เท่ากับ 28,571.4 แถว เท่ากันหมดทุกค่า

ที่น่าสนใจคือ sqlite_stat1 ของ idx_status เขียนไว้ว่า 200000 28572 ซึ่งเป็นตัวเลขเดียวกันเป๊ะ สองฝั่งเก็บสถิติชั้นแรกเหมือนกันทุกประการ

ความต่างอยู่ที่ SQLite ยังมี sqlite_stat4 ซึ่งเก็บจำนวนแถวจริงของค่าที่ถูกเก็บตัวอย่างไว้

`status`	ของจริง	`stat4` บอก	ตัวประมาณของเรา
Placed	199	199	28,571.4
Delivered	163,733	163,733	28,571.4
Rejected	13,165	13,165	28,571.4

ที่ Placed SQLite เห็นตัวเลข 199 แถวซึ่งน้อยมาก จึงใช้ index ส่วนเราเห็น 28,571.4 แถว ซึ่งอยู่เหนือจุดตัด 394 แถวของบท 5 ไปไกล จึงสแกน

ข้อพิสูจน์ว่าลำพังตัวประมาณก็พอจะพลิกคำตอบของฝั่งเราแล้ว คือป้อน 199 แถวเข้า cost model เดิมโดยไม่แก้ราคาสักตัว มันเลือก idx_status ทันที

ทดลองย้อนกลับ — ลบ `sqlite_stat4` ทิ้ง

ถ้าความต่างมาจากสถิติชั้นที่สองจริง การเอามันออกต้องทำให้ SQLite เดาเหมือนเรา แล้วต้องทำให้มันเลือกเหมือนเราด้วย การทดลองนี้ทำบนสำเนา แล้วลบตาราง sqlite_stat4 ทิ้งทั้งตาราง

คำถาม	SQLite ปกติ	SQLite ไม่มี `stat4`	ฝั่งเรา
A3 `status = 'Placed'`	`idx_status`	`idx_status`	สแกน
A4 `status = 'Delivered'`	สแกน	`idx_status`	สแกน
A5 `restaurant = 'ครัวคุณแม่'`	`idx_restaurant`	`idx_restaurant`	สแกน
A6 Rejected + ครัวสุขใจ	`idx_status_rest`	`idx_status_rest`	สแกน

พอไม่มี stat4 SQLite ก็เชื่อค่าเฉลี่ย 28,571 แถวเหมือนเรา แล้วเลือก index ให้ทุกค่าของ status รวมถึง Delivered ที่ตรง 163,733 แถว ซึ่งเป็นแผนที่แย่ชัดๆ

นี่คือผลที่ควรจดไว้ให้ดี ค่าเฉลี่ยตัวเดียวกันเป๊ะทำให้เราสแกนทุกอย่าง แต่ทำให้ SQLite ใช้ index ทุกอย่าง

แถว A3 จึงผ่านเกณฑ์แค่ครึ่งเดียว พอไม่มี stat4 SQLite เดาเท่าค่าเฉลี่ยของเราก็จริง แต่มันยังเลือก idx_status อยู่เหมือนเดิม ความต่างของ A3 ยังอยู่ครบ การทดลองย้อนกลับนี้จึงไม่ได้ยืนยันว่า stat4 คือต้นเหตุของ A3 มันบอกอย่างอื่นแทน

สองฝั่งพลาดจากข้อมูลชุดเดียวกันด้วยตัวเลขเดียวกัน แล้วออกคนละทิศ เพราะราคาที่เอาไปคูณเป็นคนละตัว ซึ่งพาไปที่ต้นเหตุอีกแบบพอดี

ต้นเหตุที่สอง — cost model และจุดตัดคนละเส้น

การกวาดหาจุดที่ SQLite เปลี่ยนใจ ระยะห่างระหว่างจุดตัดของสองฝั่ง และสิ่งที่ต้องแก้ในค่าคงที่ของเราเพื่อให้เข้าใกล้

A5 กับ A7 เป็นข้อที่สองฝั่งเห็นจำนวนแถวใกล้กัน แต่ตัดสินคนละอย่าง แปลว่าราคาที่เอาไปคูณคนละตัว

จุดตัดของเราสำหรับ index ที่ไม่ cluster คือ 394 แถว เท่ากับ 0.197 % ของตาราง เลยจุดนั้นไปการสแกนถูกกว่า

จุดตัดของ SQLite ไม่ได้ประกาศไว้ที่ไหน แต่วัดได้ วิธีวัดคือกวาดด้วย customer_id <= k แล้วดูว่ามันเปลี่ยนใจที่ k ไหน เลือก customer_id เพราะมันกระจายทั่วทั้ง file จึงเป็นเคสไม่ cluster แบบเดียวกับที่ราคาของเราคิด

`k` (ลูกค้า 1..k)	แถวที่ตรง	% ของตาราง	SQLite	SQLite ไม่มี `stat4`	ฝั่งเรา
10	1,021	0.51	index	index	สแกน
100	9,991	5.00	index	index	สแกน
400	40,216	20.11	index	index	สแกน
900	90,286	45.14	index	index	สแกน
1,600	160,064	80.03	สแกน	index	สแกน
2,000	200,000	100.00	สแกน	index	สแกน

ค้นทวิภาคต่อลงไปได้จุดที่แน่นอน SQLite เปลี่ยนใจที่ k = 1,111 ซึ่งเท่ากับ 111,261 แถว หรือ 55.63 % ของตาราง ส่วนเราเปลี่ยนใจที่ 394 แถว ห่างกัน 282 เท่า

column SQLite ไม่มี stat4 ยังบอกอีกอย่าง พอไม่มี stat4 SQLite ไม่เปลี่ยนใจเลยแม้ช่วงจะกินทั้งตาราง เพราะค่าเดาสำเร็จรูปของช่วงค่าไม่ได้ขึ้นกับ k มันเดาเป็นสัดส่วนคงที่

สถิติหายไปทีละชั้น แผนก็เพี้ยนคนละแบบ ไม่ใช่เพี้ยนหนักขึ้นแบบเดียวกัน

fit ค่า `RAND_PAGE` ใหม่ให้จุดตัดตรงกับ SQLite

ถ้าอยากรู้ว่าความต่าง 282 เท่าอยู่ที่ตรงไหน วิธีที่ตรงที่สุดคือลองปรับค่าของเราจนจุดตัดสองฝั่งเข้าใกล้กัน แล้วดูว่าต้องปรับอะไรบ้าง

`RAND_PAGE`	สมมุติฐาน buffer pool	จุดตัดของเรา (แถว)	% ของตาราง
16.00	ไม่มีเลย	394	0.20
16.00	มี (ทั้ง file)	420	0.21
1.00	มี (ทั้ง file)	173,038	86.52
1.35	มี (ทั้ง file)	112,602	56.30

ต้องตั้ง RAND_PAGE ราว 1.35 พร้อมกับ เปิดสมมุติฐานว่าทั้ง file อยู่ใน buffer pool จึงจะได้จุดตัดใกล้ที่ SQLite เปลี่ยนใจจริงที่ 111,261 แถว

แถวที่สองบอกว่าลำพัง buffer pool ขยับจุดตัดจาก 394 เป็น 420 แถวเท่านั้น ความต่างเกือบทั้งหมดจึงอยู่ที่ราคาของการอ่านแบบสุ่ม ไม่ใช่ที่ cache

บท 5 ตั้ง RAND_PAGE = 16.0 จาก readahead 16 หน้า โดยไม่มี buffer pool เลย

`RAND_PAGE` กับ `FETCH` ไม่ใช่ตัวเลขที่อยู่ใน SQLite

มันเป็น parameter ที่เรา fit กับ cost model ของเราเองในบท 5 และหัวข้อนี้คือหลักฐานตรงกันข้ามกับความเข้าใจว่ามันคือค่าของ SQLite

แถวที่เข้าใกล้จุดตัดของ SQLite ที่สุดเปลี่ยนทั้งค่าคงที่และสมมุติฐานเรื่อง buffer pool พร้อมกัน แต่น้ำหนักของสองอย่างนี้ไม่เท่ากันเลย

ลำพัง buffer pool ขยับจุดตัดจาก 394 เป็น 420 แถว ส่วนการลด RAND_PAGE เหลือ 1.35 บนสมมุติฐานเดียวกันนั้นพาไปถึง 112,602 แถว ความต่างเกือบทั้งหมดจึงอยู่ที่ราคาที่แต่ละฝั่งตั้งให้การอ่านแบบสุ่ม ซึ่งเป็นตัวเลขที่ทั้งสองฝั่งสมมุติขึ้นทั้งคู่

A6 — คู่ที่ไม่มีอยู่จริง สองฝั่งพลาดพอๆ กัน แต่ตัดสินคนละทาง

ค่าประมาณของสองฝั่งบนคู่ที่ของจริงไม่มีสักแถว และเหตุผลที่ตัวเลขใกล้กันกลับพาไปคนละแผน

A6 ถามหาออเดอร์สถานะ Rejected ของร้านครัวสุขใจ ซึ่งเป็นคู่ที่ fixture ตั้งใจทำให้ไม่มีอยู่จริงตั้งแต่บท 1 ของจริงคือ 0 แถว

ค่าประมาณของเราจาก independence assumption ได้ 1,565.6 แถว ส่วนค่าที่ SQLite มีให้ใช้เมื่อคู่นี้ไม่ติดตัวอย่างใน stat4 คือ k2 ของ idx_status_rest ซึ่งเท่ากับ 2,899 แถว

สองฝั่งพลาดพอๆ กัน คือประมาณว่ามีพันกว่าแถวทั้งที่ของจริงไม่มีสักแถว แต่ตัดสินคนละทาง

เหตุผลที่ตัวเลขใกล้กันแล้วยังตัดสินคนละทาง อยู่ที่จุดตัดคนละเส้น พันกว่าแถวอยู่ เหนือ จุดตัดของเราที่ 394 แถว แต่อยู่ ต่ำกว่า จุดตัดของ SQLite ที่ 111,261 แถวไปมาก

ผลคือ SQLite แตะ index ไม่กี่หน้าแล้วตอบว่าไม่มีแถวเลย ส่วนเราอ่านครบทั้ง 3,153 หน้าเพื่อได้คำตอบเดียวกัน โดยที่ Pager ของเรายังไม่มี buffer pool ตัวเลขนั้นจึงเป็นจำนวนครั้งที่เรียกอ่านหน้าเต็มๆ

ส่วนเหตุผลที่ตารางต้นเหตุจัด A6 ไว้ที่ตัวประมาณ เป็นคำถามคนละข้อ พอป้อน 0 แถวจริงเข้าไป ราคาเดิมของเราย้ายไป idx_status_rest ทันที ค่าประมาณจึงเป็นสิ่งที่แก้แล้วความต่างข้อนี้หายไป ส่วนจุดตัดคือสิ่งที่อธิบายว่าทำไมสองฝั่งพลาดพอกันแล้วยังไปคนละทาง

q-error 1565.58 เป็นสิ่งประดิษฐ์ ไม่ใช่การวัด

q-error วัดกรณีที่ของจริงเป็นศูนย์ไม่ได้ เพราะสูตรของมันต้องหารด้วยของจริง เลข 1565.58 ในตารางจึงมาจากการยกพื้นของจริงขึ้นเป็น 1 แถวก่อนหาร

ตัวเลขที่มีความหมายจริงในข้อนี้คือ 1,565.6 เทียบกับ 0 ไม่ใช่ตัวคูณ

และนี่เป็นความพลาดชนิดที่การเก็บตัวอย่างช่วยไม่ได้ คู่ที่ไม่มีแถวเลยย่อมไม่มี entry ใน index ให้สุ่มติดตัวอย่างมาตั้งแต่ต้น

B6 — ช่องที่ cost model ของเราไม่มีให้กรอก

ราคาสามแผนของฝั่งเราในข้อที่ SQLite เลือกต่างออกไป และหน่วยที่อธิบายความต่างนั้นซึ่งไม่ได้อยู่ในราคา

B6 ถามออเดอร์ของร้านครัวคุณแม่แล้วเรียงตาม status ของจริง 11,934 แถว เราเลือกสแกน ส่วน SQLite เลือก skip idx_status_rest

หน่วยของต้นทุนคือค่าอ่านเพจแบบเรียงตัวหนึ่งเพจ = 1.0 ราคาสามแผนของฝั่งเราเมื่อป้อนจำนวนแถวจริงจึงออกมาแบบนี้

แผนของฝั่งเรา	ราคารวม	หน้าเรียง	หน้าสุ่ม
สแกน	6,313.0	3,153	0
`idx_status_rest` x7	191,274.6	30	11,941
`idx_restaurant` แล้วเรียง	191,178.6	30	11,935

ต่อให้ตัดแผนสแกนทิ้งแล้วให้เลือกระหว่างสอง index เท่านั้น เราก็ยังเลือกผิดอยู่ดี เพราะ idx_restaurant แล้วเรียง ถูกกว่านิดเดียว

สองแผน index ราคาต่างกันแค่นั้นเพราะทั้งคู่ต้องเปิดแถวจริงเท่ากัน สิ่งที่ต่างกันจริงไม่ได้อยู่ในหน่วยราคาเลย

ผ่าน idx_status_rest ทั้ง 7 ช่วง ผลออกมาเรียงตาม status อยู่แล้ว ถือไว้ 1 แถว
ผ่าน idx_restaurant ต้องกองไว้ให้ครบ 11,934 แถวแล้วค่อยเรียง

cost model สามตัวเลขของบท 5 ไม่มีที่ให้ใส่ราคาของการเรียง เราจึงมองไม่เห็นข้อนี้ ส่วน SQLite คิดราคาการเรียงด้วย จึงยอมลงจากรากหลายครั้งเพื่อไม่ต้องเรียงทีหลัง

นี่คือข้อจำกัดของ cost model ของเรา ไม่ใช่ข้อผิดพลาดของการวัด และเป็นราคาที่เราจ่ายไปตั้งแต่ตอนเลือกให้ราคามีแค่สามตัวเลขในบท 5

เลข 30 ใน column หน้าเรียงคือใบของ index ที่ต้องเดินผ่าน ส่วน column หน้าสุ่มคือหน้า heap ที่ต้องเปิดแถวจริง บวกการลงจากรากช่วงละหนึ่งหน้า ทั้งสองแผน index คิดจากสูตรเดียวกันนี้

def plan_cost(t, matched, *, k_ranges=1, covering=False, clustered=False, cache=False,
              height=cost.HEIGHT, leaf_cap=cost.LEAF_CAP):
    """สูตรเดียวกับบท 6: ต้นทุน = k x ความสูง (ลงจากราก k ครั้ง) + ใบที่เดินผ่าน + heap

    covering=True คือคำตอบอยู่ในคีย์ครบแล้ว ไม่ต้องตามตัวชี้กลับไปเปิดแถว (heap = 0 หน้า)"""
    matched = max(0, matched)
    leaves = float(max(k_ranges, math.ceil(matched / leaf_cap))) if matched else float(k_ranges)
    descent = float((height - 1) * k_ranges)
    hp = 0.0 if covering else cost.heap_pages(t, matched, clustered, cache)
    if clustered:
        return cost.Cost(seq=leaves + hp, rand=descent, rows=float(matched))
    return cost.Cost(seq=leaves, rand=descent + hp, rows=float(matched))

ส่วนการเลือกทั้งหมดของ planner เราอยู่ในสี่บรรทัดถัดไปนี้

def choose(cands, matched):
    """ตรรกะทั้งหมดของ planner เรา: คิดราคาทุกแผนที่รู้จัก แล้วเอาแผนที่ถูกที่สุด"""
    priced = [(cost.total(c.cost(matched)), c) for c in cands]
    best = min(priced, key=lambda p: p[0])
    return best[1], best[0]

choose เลือกแผนที่ถูกที่สุดจากรายการที่รู้จักเสมอ แผนที่ไม่ได้อยู่ในรายการจึงไม่มีวันถูกเลือก และถ้าราคามองไม่เห็นความต่าง min จะคืนตัวแรกในรายการ ผลจึงถูกตัดสินด้วยลำดับที่เราเขียนแผนไว้ ไม่ใช่ด้วยคุณสมบัติของแผน

VDBE opcode — หน่วยของฝั่ง SQLite ที่วัดซ้ำได้

การนับคำสั่งที่เครื่องเสมือนของ SQLite เดินจริง สิ่งที่ตัวเลขนั้นบอก และข้อควรระวังที่สำคัญที่สุดของหัวข้อนี้

ฝั่งเรานับหน้าที่แตะได้เพราะเราเขียน Pager เอง ฝั่ง SQLite นับไม่ได้แบบนั้น แต่มีหน่วยของตัวเองที่วัดซ้ำได้เท่ากันทุกครั้ง ต่างจากเวลา

VDBE opcode วัดได้สองแบบ และสองแบบนี้ตอบคนละคำถาม

def program(path, sql, params=()):
    """โปรแกรม VDBE ที่คอมไพล์ออกมา (EXPLAIN เฉย ๆ) — คืนรายชื่อ opcode ตามลำดับ"""
    con = connect(path)
    out = [r[1] for r in con.execute("EXPLAIN " + sql, params)]
    con.close()
    return out


def vdbe_steps(path, sql, params=()):
    """จำนวน opcode ที่ 'ถูกเดินจริง' ตอนรัน — นับด้วย progress handler ทุก 1 opcode

    หน่วยนี้วัดซ้ำได้เท่ากันเป๊ะทุกครั้ง (ต่างจากเวลา) แต่ต้องระวังว่ามันไม่ใช่เวลา
    และไม่ใช่สิ่งที่ planner ของ SQLite พยายามทำให้น้อยที่สุด — หัวข้อ [5.3] วัดให้เห็น"""
    con = connect(path)
    hits = [0]

    def tick():
        hits[0] += 1
        return 0

    con.set_progress_handler(tick, 1)
    nrows = len(con.execute(sql, params).fetchall())
    con.set_progress_handler(None, 1)
    con.close()
    return hits[0], nrows

program คืนความยาวของโปรแกรมที่คอมไพล์ออกมา ส่วน vdbe_steps นับจำนวนคำสั่งที่ถูกเดินจริงตอนรัน ผ่าน progress handler ที่ตั้งให้เรียกทุก 1 opcode

ความยาวโปรแกรมของทั้ง 17 ข้ออยู่ในช่วง 14 ถึง 45 opcode เท่านั้น ตั้งแต่ 14 opcode ที่ A1 กับ B1 เท่ากัน ไปจนถึง 45 opcode ที่ B3 ซึ่งยาวที่สุด

ตัวเลขนั้นสั้นเสมอเพราะ opcode เกือบทั้งหมดอยู่ในลูป ความยาวโปรแกรมจึงไม่ได้บอกว่างานหนักแค่ไหน มันบอกแค่ว่าแผนซับซ้อนแค่ไหน

จำนวนที่เดินจริงเป็นคนละเรื่อง วิธีวัดคือบังคับสองแผนบนคำถามเดียวกันด้วย INDEXED BY กับ NOT INDEXED แล้วเทียบ

คำถาม	แถว	index (opcode)	สแกน (opcode)	index/สแกน	SQLite เลือก
A3 `status='Placed'`	199	4,124	603,526	0.007	index
A4 `status='Delivered'`	163,733	1,639,465	1,748,264	0.938	สแกน
A5 `restaurant='ครัวคุณแม่'`	11,934	121,474	685,671	0.177	index
A6 Rejected x ครัวสุขใจ	0	2,138	628,465	0.003	index

ทุกบรรทัดวัดซ้ำได้ตัวเลขเดิมเป๊ะ เพราะจำนวน opcode ขึ้นกับแผนกับข้อมูล ไม่ขึ้นกับเครื่อง และทุกบรรทัดมี self-check ว่าสองแผนคืนแถวเท่ากันจริง

แถว A4 คือแถวที่ต้องอ่านให้ละเอียด แผน index เดิน 1,639,465 opcode ส่วนแผนสแกนเดิน 1,748,264 แปลว่าแผน index เดิน opcode น้อยกว่า 6.2 % แต่ SQLite เลือกสแกน

ประโยคที่ซื่อสัตย์เมื่อสองหน่วยชี้คนละทาง

จำนวน opcode วัดซ้ำได้และชี้ไปทาง index ส่วน planner ของ SQLite ชี้ไปทางสแกน ทั้งสองอย่างจริงพร้อมกันได้ เพราะมันวัดคนละอย่าง

opcode ไม่ได้นับของที่แพงที่สุดในแผน index คือการกระโดดไปเปิดหน้าคนละที่ต่อหนึ่งแถว opcode ตัวเดียวชื่อ Column หรือ Seek อาจแปลว่าอ่านหนึ่งหน้า หรือแปลว่าไม่อ่านอะไรเลยก็ได้

และห้ามเอา opcode ไปหารกับหน้าที่แตะของเรา มันเป็นคนละหน่วย และเราไม่มีทางนับหน้าที่ SQLite อ่านจริงจาก Python stdlib ล้วนๆ ได้

ช่องว่าง 6.2 % ยังแคบเกินกว่าจะสรุปอะไร ถ้าอยากได้เคสที่คม ต้องกวาดหาเคสที่ห่างกันหลายเท่า ไม่ใช่ขยายผลจากเคสแคบ

ในสี่เคสที่วัด เคสที่คมที่สุดคือ A6 แผน index เดิน opcode น้อยกว่าแผนสแกน 294 เท่า และนั่นคือตัวเลขที่ยกไปอ้างได้ ไม่ใช่ตัวเลขของ A4

การทดลอง fudge สถิติ — โกหก `sqlite_stat1` แล้วแผนขยับไหม

สี่ขั้นของการแก้ตารางสถิติบนสำเนา ผลที่ได้จริงในแต่ละขั้น และเส้นแบ่งระหว่างสิ่งที่การทดลองนี้ยืนยันได้กับสิ่งที่มันแยกไม่ออก

เรื่องที่เล่ากันบ่อยคือแก้สถิติแล้วแผนพลิก การทดลองนี้ทำจริงสี่ขั้นบนสำเนา .build/compare_fudge.db โดยตัวจริงไม่ถูกแตะ คำถามที่ใช้ตรวจคือ status = 'Delivered' ซึ่งตรง 163,733 แถว

ขั้น 0 สำเนาที่ยังไม่แก้อะไร ตอบว่าสแกน เหมือนตัวจริง

ขั้น 1 โกหกว่า idx_status มี 1 แถวต่อหนึ่งค่า ด้วยการตั้ง stat เป็น 200000 1 แล้วถามซ้ำบน connection เดิม ทั้งก่อนแก้ หลังแก้ และหลังสั่ง ANALYZE sqlite_schema ได้คำตอบว่าสแกนทั้งสามครั้ง

คำอธิบายที่มักได้ยินตรงนี้คือ SQLite อ่านสถิติตอนโหลด schema แล้วเก็บผลไว้ใน cache ของ connection นั้น การแก้ตารางสถิติเฉยๆ จึงไม่ทำให้แผนขยับ

แต่ผลของขั้น 1 ลำพังตัวมันเองยังแยกไม่ออกว่ามาจาก cache นั้นหรือมาจาก stat4 ที่ยังอยู่ครบ เพราะขั้น 2 ที่เปิด connection ใหม่ทั้งอันก็ยังได้แผนเดิม การตั้ง cached_statements=0 ของ connect จึงเป็นการปิดความเสี่ยงไว้ล่วงหน้า ไม่ใช่สิ่งที่บทนี้วัดได้ว่าเปลี่ยนแผนข้อไหน

ขั้น 2 เปิด connection ใหม่ทั้งอัน แผนยังไม่ขยับ ทั้งที่โกหกไปแล้วว่า status ทุกค่ามีแค่ 1 แถว

เหตุผลคือ sqlite_stat4 ของ idx_status ยังอยู่ครบ 14 ตัวอย่าง และค่าที่ถามเป็นค่าคงที่ในคำสั่ง SQLite จึงหยิบจำนวนแถวจริงของค่านั้นจาก stat4 มาใช้ ไม่ต้องพึ่งค่าเฉลี่ยใน stat1 เลย

ขั้น 3 ลบ sqlite_stat4 แล้วถามซ้ำ คราวนี้แผนขยับ SQLite ย้ายไปใช้ idx_status กับคำถามที่ตรงถึง 163,733 แถว ซึ่งเป็นแผนที่แย่กว่าสแกนชัดๆ

ขั้นนี้แยกไม่ออกว่าผลมาจากคำโกหกหรือมาจากการลบ stat4 เพราะหัวข้อต้นเหตุที่หนึ่งวัดไว้แล้วว่าลำพังการลบ stat4 โดยที่ stat1 ยังซื่อสัตย์อยู่ ก็พา Delivered ไป idx_status เหมือนกัน

ลำดับที่การทดลองชุดนี้ยืนยันได้ตรงๆ จึงมีข้อเดียว คือเมื่อ stat4 มีตัวอย่างของค่าที่ถาม มันมาก่อน stat1 เสมอ ซึ่งเป็นสิ่งที่ขั้น 2 พิสูจน์ ส่วนอันดับระหว่าง stat1 กับค่าเดาสำเร็จรูป ไม่มีขั้นไหนแยกให้เห็น

ขั้น 4 โกหกว่า idx_restaurant แทบไม่กรองอะไรเลย คือ 100,000 แถวต่อค่า แล้วถาม restaurant = 'ครัวคุณแม่' มันไม่ยอมกลับไปสแกนตาราง แต่ย้ายไปใช้ idx_status_rest แทน

รายการแผนของเราสำหรับคำถามนี้มีแค่สแกนกับ idx_restaurant เราจึงไม่มีทางตอบแบบเดียวกัน แผน k ช่วงของบท 6 เราใส่ไว้เฉพาะข้อที่ถามถึง status ด้วย คือ B3 กับ B6 และที่ B6 เราก็ตีราคามันแพงกว่าสแกนอยู่ดี

ผลของทั้งสี่ขั้นผูกกับ build ที่รันอยู่ build ที่ไม่ได้เปิด SQLITE_ENABLE_STAT4 จะเห็นแผนพลิกตั้งแต่ขั้น 2 เลย ผลออกทางไหนก็รายงานทางนั้น ตัวเลขที่แต่งขึ้นเผยแพร่ไม่ได้

join — สิ่งเดียวที่เทียบกันได้คือใครเป็นตารางนอก

ขอบเขตของการเทียบ join ผลว่าตารางนอกตรงกันกี่ข้อ และต้นเหตุความต่างแบบที่สามที่เจอในหัวข้อนี้

planner ของบท 5 ไม่รู้จัก join เลย หัวข้อนี้จึงเป็นส่วนขยายที่ต่อจากราคาเดิมตรงๆ ด้วยสูตรเดียว คือราคาเข้าตารางนอก บวกกับจำนวนแถวของตารางนอกคูณราคาค้นตารางในหนึ่งครั้ง

ข้อมูลฝั่ง customers คือตารางของบท 7 ทั้งชุด มี 2,000 แถว กินไป 16 หน้าใน heap ของเรา ในนั้น tier gold มี 209 คน และออเดอร์ของคนกลุ่มนี้รวม 20,912 แถว คิดเป็น 10.46 % ของ orders

ราคาฝั่งเราตั้งสมมุติฐานเพิ่มหนึ่งข้อ คือให้ customers ทั้ง 16 หน้าอยู่ใน buffer pool ได้ทั้งตาราง ไม่งั้น cost model ของบท 5 จะคิดค่าอ่านหนึ่งหน้าต่อหนึ่งแถว ซึ่งเกินจริงมากกับตารางเล็กขนาดนี้

ด้วยข้อจำกัดนั้น สิ่งที่เทียบกันได้จริงมีอย่างเดียวคือใครเป็นตารางนอก ซึ่งเป็นการตัดสินใจหลักของ join อยู่แล้ว

	คำถาม	แถวจริง	ตารางนอกที่เราเลือก	ตารางนอกของ SQLite	ผล	opcode ที่เดินจริง
C1	ออเดอร์ของลูกค้า 995 + เมืองของลูกค้า	97	`orders`	`customers`	ต่าง	2,816
C2	นับออเดอร์ของลูกค้า tier gold	20,912	`customers`	`customers`	ตรง	65,904
C3	จำนวนออเดอร์ต่อเมือง (ไม่กรองอะไรเลย)	200,000	`customers`	`customers`	ตรง	2,608,441
C4	tier gold + `status='Placed'`	17	`orders`	`orders`	ตรง	3,746
C5	tier gold + `status='Delivered'`	17,163	`orders`	`customers`	ต่าง	124,900

ตารางนอกตรงกัน 3 จาก 5 ข้อ และสองข้อที่ต่างกันมาจากคนละเหตุผลกันสนิท

C1 คือต้นเหตุแบบที่สาม เงื่อนไขที่เขียนไว้คือ o.customer_id = 995 ซึ่งอยู่ฝั่ง orders แต่ SQLite เริ่มจาก customers เพราะมันส่งต่อเงื่อนไขข้ามเครื่องหมายเท่ากับของ join ได้

c.id = o.customer_id คู่กับ o.customer_id = 995 จึงแปลว่า c.id = 995 ซึ่งเหลือแถวเดียว planner ของเราไม่ได้ส่งต่อเงื่อนไขแบบนั้น จึงเห็นฝั่ง customers เป็นทั้งตาราง

ต้นเหตุข้อนี้ไม่ใช่ตัวประมาณ และไม่ใช่ราคา แต่เป็นการเขียนเงื่อนไขใหม่ก่อนคิดราคา ซึ่งเป็นขั้นตอนที่ planner ของเราไม่มีเลย

C5 เป็นต้นเหตุเดิม และ C4 คือตัวคุมของมัน คำถามหน้าตาเหมือนกันทุกอย่าง ต่างแค่ค่าของ status แต่ SQLite สลับตารางนอกระหว่างสองข้อ เพราะ stat4 บอกว่า Placed หายากกว่ามาก ส่วนเราใช้ค่าเฉลี่ยตัวเดียวกันกับทั้งสองข้อ จึงตอบเหมือนกัน

แผน join ทั้งห้าข้อไม่มีข้อไหนเป็น hash join

แผนของ SQLite ทั้งห้าข้อเป็นการวนลูปซ้อนกับ index ทั้งหมด ซึ่งตรงกับที่ SQLite ประกาศไว้ว่ามันมี nested loop อย่างเดียว ข้อสังเกตนี้อ้างจากแผนห้าข้อที่วัดได้ในหัวข้อนี้เท่านั้น

บท 7 วัดไว้แล้วว่า hash join ชนะ index nested loop เรื่องหน้าที่อ่านอย่างขาดลอยในโจทย์ของบทนั้น แต่เป็นคนละโจทย์กับห้าข้อนี้ จึงเอาตัวเลขสองชุดมาเทียบตรงๆ ไม่ได้

สิ่งที่สรุปได้คือแผนที่ดีที่สุดที่ SQLite เลือกได้ กับแผนที่ดีที่สุดที่มีอยู่จริง ไม่จำเป็นต้องเป็นอันเดียวกัน

`ANALYZE` เทียบ `PRAGMA optimize`

เหตุผลที่คอร์สนี้เลือกคำสั่งหนึ่ง สิ่งที่ SQLite แนะนำสำหรับแอปจริง และผลต่างของสถิติที่สองวิธีเขียนไว้

คอร์สนี้ใช้ ANALYZE เพราะต้องการ determinism คือรันซ้ำต้องได้แผนเดิมเป๊ะและ stdout เท่าเดิมทุกครั้ง

ส่วน SQLite แนะนำ PRAGMA optimize สำหรับแอปจริง หัวข้อนี้จึงรันทั้งสองตัวบนสำเนาที่ลบสถิติทิ้งหมด แล้ววัดผลต่าง

index	`ANALYZE` เต็มๆ	`PRAGMA optimize`
`idx_customer`	`200000 100`	`200000 101`
`idx_placed_at`	`200000 1`	`200000 1`
`idx_restaurant`	`200000 20000`	`200000 2001`
`idx_status`	`200000 28572`	`200000 1334`
`idx_status_rest`	`200000 28572 2899`	`200000 1334 308`

PRAGMA optimize วิเคราะห์แบบจำกัดจำนวนแถวที่อ่าน มันจึงได้ค่าเฉลี่ยต่อคีย์ที่ต่ำกว่าความจริงมาก และไม่ได้เขียน sqlite_stat4 ไว้เลย จาก 97 ตัวอย่างเหลือ 0

ผลพลอยได้คือแผนของคำถามที่ตรง 163,733 แถวเปลี่ยนไปจากที่ ANALYZE เต็มๆ ให้ พูดตรงๆ คือในชุดข้อมูลนี้ คำสั่งที่ SQLite แนะนำสำหรับแอปจริงให้แผนที่แย่กว่า

แต่ข้อสรุปที่ใช้ได้จริงไม่ได้กลับข้าง สองคำสั่งนี้ตอบคนละโจทย์

ANALYZE อ่านตารางแล้วเขียนสถิติใหม่ทั้งหมดทุกครั้ง ผลเหมือนเดิมทุกครั้งที่รัน เหมาะกับบทเรียนหรือ benchmark ที่ต้องรันซ้ำได้
PRAGMA optimize ดูว่าตารางไหนน่าจะต้องวิเคราะห์ใหม่แล้วค่อยสั่งให้เอง แบบจำกัดปริมาณงาน เหมาะกับของจริงที่ข้อมูลเปลี่ยนตลอดและไม่มีใครมานั่งสั่งเอง
ราคาของตัวหลัง คือผลขึ้นกับว่า connection นั้นเคยรัน query อะไรมาบ้าง และตัวเลขที่ได้เป็นค่าประมาณ ซึ่งเป็นสองข้อที่ทำให้มันไม่เหมาะกับคอร์สนี้

ในแอปจริงจึงใช้ PRAGMA optimize และถ้าต้องการสถิติละเอียดค่อยสั่ง ANALYZE เป็นครั้งคราวตอนข้อมูลนิ่ง ส่วนพฤติกรรมที่วัดได้ในหัวข้อนี้ผูกกับ build ที่รันอยู่

ของที่อีกฝั่งมี แต่เราไม่มี — และกลับกัน

รายการความสามารถที่แต่ละฝั่งมีอยู่แล้วอีกฝั่งไม่มี และหน่วยคู่หนึ่งที่เทียบข้ามฝั่งไม่ได้เลย

หลังเทียบมาทั้งบท ช่องว่างของสองฝั่งเขียนออกมาเป็นรายการได้ตรงๆ

SQLite มี แต่ planner ของเราไม่มี

sqlite_stat4 คือจำนวนแถวจริงของค่าที่ถูกเก็บตัวอย่างไว้ แทนค่าเฉลี่ยทั้ง column
การหยิบ skip-scan ขึ้นมาพิจารณาเองทุกคำถาม ส่วนแผน k ช่วงของบท 6 ที่เทียบเท่ากัน เราใส่ไว้ในรายการแค่ B3 กับ B6 ด้วยมือ
การรู้จากตัว query เองว่าแผนไหน covering ได้ และรู้ว่า COUNT(*) ไม่ต้องเปิดแถวจริง ส่วนฝั่งเราต้องประกาศไว้เป็นรายข้อ
การคิดราคาของการเรียง จึงยอมจ่ายค่าเดินต้นไม้เพิ่มเพื่อไม่ต้องเรียงทีหลัง
การส่งต่อเงื่อนไขข้ามเครื่องหมายเท่ากับของ join ก่อนคิดราคา ซึ่งเป็นสิ่งที่ C1 วัดให้เห็น

เรามี แต่ SQLite ไม่ได้ให้

ราคาของทุกแผนที่แพ้ ไม่ใช่แค่แผนที่ชนะ EXPLAIN QUERY PLAN บอกว่าเลือกอะไร แต่ไม่บอกว่าทำไมถึงไม่เลือกอีกแผน
หน่วยเดียวตลอดคอร์ส คือหน้าที่แตะ โดยมี 3,153 หน้าของ heap เป็นตัวหารเดียวกันหมด
parameter ที่เปลี่ยนแล้ววัดผลได้ทันทีว่าจุดตัดขยับไปไหน

สิ่งที่เทียบกันไม่ได้ตรงๆ เลยคือหน้าที่แตะของเรากับ opcode ของ SQLite เราไม่มีทางนับหน้าที่ SQLite อ่านจริงจาก Python ล้วนๆ ได้ จึงไม่เทียบข้ามหน่วย

ทุกจุดที่ไม่ตรง ชี้กลับไปที่บทใดบทหนึ่งเสมอ

สิ่งที่ของเล่นเจ็ดบทนี้ทำได้และทำไม่ได้ และที่ที่ผู้อ่านควรไปต่อ

ของที่เราสร้างมาเจ็ดบทเป็นของเล่นสำหรับอธิบายว่าทำไมแผนของ SQLite ถึงหน้าตาแบบนั้นเท่านั้น มันไม่ได้เร็วกว่า ไม่ได้ฉลาดกว่า และไม่ได้ทำซ้ำสิ่งที่ SQLite ทำ

สิ่งที่มันทำได้คือทำให้ทุกตัวเลขในแผนอธิบายได้ พอเอามาเทียบกันแล้ว ทุกจุดที่สองฝั่งไม่ตรงกันจึงชี้กลับไปที่บทใดบทหนึ่งได้เสมอ

flowchart LR
    L2["บท 2<br/>หน้าและ heap file"] --> L3["บท 3<br/>B+tree"]
    L3 --> L4["บท 4<br/>สถิติ"]
    L4 --> L5["บท 5<br/>ราคา"]
    L5 --> L6["บท 6<br/>ทางเข้า"]
    L6 --> L7["บท 7<br/>join"]
    L7 --> L8["บท 8<br/>เทียบกับ SQLite"]
    L8 -.->|"A3 ค่าเฉลี่ยต่อ column · A6 independence"| L4
    L8 -.->|"C5 — ต้นเหตุเดียวกับ A3 และ A6"| L4
    L8 -.->|"A5 กับ A7 — จุดตัดคนละเส้น"| L5
    L8 -.->|"B6 — ราคาสามตัวเลขไม่มีช่องให้กรอกราคาการเรียง"| L5
    L8 -.->|"C1 — ส่งต่อเงื่อนไขข้ามเท่ากับก่อนคิดราคา"| L7
    L8 -.->|"3,153 หน้า เป็นตัวหารเดียวกันหมด"| L2

คำบรรยายภาพ: เส้นทึบคือลำดับที่คอร์สนี้ประกอบของขึ้นมาทีละชั้น ตั้งแต่หน้าและ heap file จนถึงการเทียบกับ SQLite · เส้นประคือความต่างแต่ละข้อที่บทนี้วัดได้ ชี้ย้อนกลับไปที่บทที่อธิบายมันได้ · ทุกเส้นประมีปลายทางเสมอ นั่นคือเงื่อนไขที่บทนี้ตั้งไว้ตั้งแต่ต้น

🔁 อ่านต่อ — จากผู้สร้างแผน กลับไปหาผู้วินิจฉัยแผน

คอร์สนี้สร้างสิ่งที่ผลิตแผนขึ้นมา ส่วนการวินิจฉัยอาการจากแผนที่ได้มาแล้ว เป็นคนละงาน

ถ้าคุณมาที่นี่ด้วยอาการเพิ่ม index แล้วยังช้าเหมือนเดิม ตอนนี้คุณอ่านแผนออกในระดับที่รู้แล้วว่าตัวเลขไหนมาจากการเดา และตัวเลขไหนมาจากราคาที่ใครสักคนสมมติขึ้น

กลับไปอ่าน ทำไม index ที่คุณเพิ่งเพิ่มถึงไม่ถูกใช้ อีกรอบ อาการทุกข้อในบทความนั้นมีชื่อบทของคอร์สนี้กำกับอยู่แล้ว

🔗 อ้างอิงต้นทางของบทนี้

EXPLAIN QUERY PLAN — เอกสารทางการของ SQLite (ตรวจ 2026-08-11) — รูปแบบของแผนที่คำสั่งนี้คืนออกมา และข้อเตือนว่ารูปแบบนั้นเปลี่ยนได้ระหว่าง version จึงไม่ควรให้โปรแกรมอ่านผลของมันโดยตรง
The Virtual Database Engine of SQLite — รายการ opcode ทั้งหมด (ตรวจ 2026-08-11) — ความหมายของแต่ละ opcode ที่ EXPLAIN คืนมา และคำอธิบายว่าเครื่องเสมือนตัวนี้เดินโปรแกรมอย่างไร
PRAGMA optimize (ตรวจ 2026-08-11) — สิ่งที่คำสั่งนี้ทำจริง และคำแนะนำของ SQLite ว่าแอปจริงควรเรียกมันตอนไหน
ANALYZE (ตรวจ 2026-08-11) — สถิติที่คำสั่งนี้เขียนลง sqlite_stat1 และเงื่อนไขที่ sqlite_stat4 จะถูกเขียนด้วย

เช็กความเข้าใจ — บทที่ 8

ข้อ 1 / 3

ที่ A4 (status='Delivered' ตรง 163,733 แถว) แผน index เดิน 1,639,465 opcode ส่วนแผนสแกนเดิน 1,748,264 opcode แต่ SQLite เลือกสแกน ข้อสรุปที่ซื่อสัตย์ที่สุดคืออะไร

capstone — planner ของ​คุณ เทียบ​กับ sqlite3

สนาม​แข่ง — ของ​สอง​ฝั่ง​วาง​อยู่​บน​ข้อมูล​ชุด​เดียวกัน​จริง​ไหม

22 คำถาม ชุด​เดียวกัน และ​นิยาม​ของ​คำ​ว่า “ตรง”

แยก​ต้นเหตุ​ด้วย​การ​ทดลอง ไม่ใช่​ด้วย​การ​อธิบาย

ต้นเหตุ​ที่​หนึ่ง — ตัว​ประมาณ​จำนวน​แถว

ทดลอง​ย้อน​กลับ — ลบ sqlite_stat4 ทิ้ง

ต้นเหตุ​ที่​สอง — cost model และ​จุด​ตัด​คนละ​เส้น

fit ค่า RAND_PAGE ใหม่​ให้​จุด​ตัด​ตรง​กับ SQLite

A6 — คู่​ที่​ไม่มี​อยู่​จริง สองฝั่งพลาดพอๆ กัน แต่​ตัดสิน​คนละ​ทาง

B6 — ช่อง​ที่ cost model ของ​เรา​ไม่มี​ให้​กรอก

VDBE opcode — หน่วย​ของ​ฝั่ง SQLite ที่​วัด​ซ้ำ​ได้

การ​ทดลอง fudge สถิติ — โกหก sqlite_stat1 แล้ว​แผน​ขยับ​ไหม

join — สิ่ง​เดียว​ที่​เทียบ​กัน​ได้​คือ​ใคร​เป็น​ตาราง​นอก

ANALYZE เทียบ PRAGMA optimize

ของ​ที่​อีก​ฝั่ง​มี แต่​เรา​ไม่มี — และ​กลับ​กัน

ทุก​จุด​ที่​ไม่​ตรง ชี้​กลับ​ไป​ที่​บท​ใด​บท​หนึ่ง​เสมอ