一个性能优化的问题

Question

SELECT member_id FROM mm_view_scene WHERE scene_id = 30196
这个子查询大概会返回1000条数据，用IN判断可能效率比较低。不知道postgresql中有没有二分查找的函数？如果有的话我考虑把子查询排序，然后用二分来查找

Answer 1

修改了一下，速度提高了3倍，下面的查询1比查询2快3倍。CREATE TABLE test (
id1 INTEGER NOT NULL,
id2 INTEGER NOT NULL,
PRIMARY KEY (id1, id2)
);-- 插入测试数据
CREATE OR REPLACE FUNCTION
insert_data ()
RETURNS INTEGER AS
$$
DECLARE
v_temp1 INTEGER;
v_temp2 INTEGER;
v_temp3 INTEGER;
BEGIN
FOR v_temp1 IN 0 .. 999 LOOP
FOR v_temp2 IN 0 .. 999 LOOP
v_temp3 := (random() * 1000)::INTEGER % 1000;
IF v_temp3 < 200 THEN
INSERT INTO test (id1, id2) VALUES (v_temp1, v_temp2);
END IF;
END LOOP;
END LOOP;
RETURN 0;
END;
$$ LANGUAGE 'plpgsql';
SELECT insert_data();-- 查询1
SELECT t.id1, COUNT(*) FROM test t, (SELECT id2 FROM test WHERE id1 = 500) AS r
WHERE t.id2 = r.id2
AND t.id1 <> 500
GROUP BY t.id1
ORDER BY COUNT(t.id1) DESC
LIMIT 10;
-- 查询2
SELECT id1, COUNT(*) FROM test WHERE id2 IN (
SELECT id2 FROM test WHERE id1 = 500
)
AND id1 <> 500
GROUP BY id1
ORDER BY COUNT(id1) DESC
LIMIT 10;

Answer 2

错了在id2上建了个索引，查询2的速度提高了近20倍，查询1则变化不大。CREATE INDEX idx_test_id2 ON test(id2);

Answer 3

终于又优化了一点：创建另一个表：
CREATE TABLE test1
(
  id2 int4 NOT NULL,
  id1 int4 NOT NULL,
  PRIMARY KEY (id2, id1)
);
数据和test一模一样。
buy_count信息则放到相关产品的表格中，每次有购买时就更新，这样可以省去统计的开销。
CREATE TABLE book
(
  id int4 NOT NULL,
  name varchar(10) NOT NULL,
  buy_count INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (id1)
);SELECT b.id, b.buy_count FROM book b, (
SELECT id1 FROM test1 WHERE id2 IN
( SELECT id2 FROM test WHERE id1 = 5000 ) GROUP BY id1) r
WHERE b.id = r.id1 AND b.id <> 5000
ORDERY BY buy_count DESC
LIMIT 10;现在1000万条数据查询不足一秒。主要是因为id2排在test1的主键的前面，在作IN查询时速度极快，由于这个查询的结果记录已经不多，剩下的查询并ORDER都是很快的。