fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_products WHERE category_id = 1 AND price > 100 ORDER BY price DESC LIMIT 20;

QUERY PLAN
——————————————————————————————————–
Limit (cost=1000.00..1000.20 rows=20 width=100) (actual time=100.234..100.345 rows=20 loops=1)
-> Sort (cost=1000.00..1002.50 rows=1000 width=100) (actual time=100.234..100.300 rows=20 loops=1)
Sort Key: price DESC
Sort Method: top-N heapsort Memory: 25kB
-> Seq Scan on fgedu_products (cost=0.00..995.00 rows=1000 width=100) (actual time=0.023..85.345 rows=1000 loops=1)
Filter: ((category_id = 1) AND (price > 100.00))
Rows Removed by Filter: 9000
Planning Time: 0.123 ms
Execution Time: 100.380 ms

# 2. 创建复合索引
fgedudb=> CREATE INDEX idx_products_category_price ON fgedu_products(category_id, price DESC);
CREATE INDEX

# 3. 优化后查询
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_products WHERE category_id = 1 AND price > 100 ORDER BY price DESC LIMIT 20;

QUERY PLAN
——————————————————————————————————–
Limit (cost=0.29..1.09 rows=20 width=100) (actual time=0.034..0.055 rows=20 loops=1)
-> Index Scan using idx_products_category_price on fgedu_products (cost=0.29..40.29 rows=1000 width=100) (actual time=0.034..0.045 rows=20 loops=1)
Index Cond: ((category_id = 1) AND (price > 100.00))
Planning Time: 0.100 ms
Execution Time: 0.060 ms

fgedudb=> BEGIN;
BEGIN

fgedudb=> — 锁定库存
fgedudb=> SELECT * FROM fgedu_inventory WHERE product_id = 1 FOR UPDATE;
id | product_id | quantity
—-+————+———-
1 | 1 | 100
(1 row)

fgedudb=> — 检查库存
fgedudb=> IF (SELECT quantity FROM fgedu_inventory WHERE product_id = 1) >= 1 THEN
fgedudb=> — 减少库存
fgedudb=> UPDATE fgedu_inventory SET quantity = quantity – 1 WHERE product_id = 1;
fgedudb=> — 创建订单
fgedudb=> INSERT INTO fgedu_orders (user_id, total_amount, status) VALUES (1, 100.00, ‘pending’);
fgedudb=> — 获取订单ID
fgedudb=> SELECT lastval() INTO oid;
fgedudb=> — 创建订单明细
fgedudb=> INSERT INTO fgedu_order_items (order_id, product_id, quantity, unit_price) VALUES (oid, 1, 1, 100.00);
fgedudb=> COMMIT;
fgedudb=> ELSE
fgedudb=> ROLLBACK;
fgedudb=> END IF;

# 2. 批量处理订单
fgedudb=> — 批量更新订单状态
fgedudb=> UPDATE fgedu_orders SET status = ‘paid’ WHERE id IN (1, 2, 3, 4, 5);
UPDATE 5

# 3. 使用索引优化订单查询
fgedudb=> CREATE INDEX idx_orders_user_status ON fgedu_orders(user_id, status);
CREATE INDEX

fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE user_id = 1 AND status = ‘pending’;

QUERY PLAN
——————————————————————————————————–
Index Scan using idx_orders_user_status on fgedu_orders (cost=0.29..1.09 rows=5 width=100) (actual time=0.024..0.035 rows=5 loops=1)
Index Cond: ((user_id = 1) AND (status = ‘pending’::text))
Planning Time: 0.100 ms
Execution Time: 0.040 ms

postgres=# CREATE TABLE fgedu_inventory (
id serial PRIMARY KEY,
product_id integer REFERENCES fgedu_products(id),
quantity integer NOT NULL,
last_updated timestamp DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE

# 2. 库存更新优化
postgres=# CREATE OR REPLACE FUNCTION update_inventory(p_product_id integer, p_quantity integer) RETURNS void AS $$
BEGIN
UPDATE fgedu_inventory
SET quantity = quantity + p_quantity,
last_updated = CURRENT_TIMESTAMP
WHERE product_id = p_product_id;

IF NOT FOUND THEN
INSERT INTO fgedu_inventory (product_id, quantity)
VALUES (p_product_id, p_quantity);
END IF;
END;
$$ LANGUAGE plpgsql;

# 3. 库存查询优化
postgres=# CREATE INDEX idx_inventory_product_quantity ON fgedu_inventory(product_id, quantity);
CREATE INDEX

postgres=# EXPLAIN ANALYZE SELECT * FROM fgedu_inventory WHERE product_id = 1 AND quantity > 0;

QUERY PLAN
——————————————————————————————————–
Index Scan using idx_inventory_product_quantity on fgedu_inventory (cost=0.29..1.09 rows=1 width=52) (actual time=0.024..0.035 rows=1 loops=1)
Index Cond: ((product_id = 1) AND (quantity > 0))
Planning Time: 0.100 ms
Execution Time: 0.040 ms