不久前看了一篇名为：修改一行代码提升 Postgres 性能 100 倍 的文章。
http://www.iteye.com/news/28235
今天自己碰到类似的查询，就又回去看了一遍，发现我和他的场景还是比较类似的。所以索性就模拟一下。
先介绍一下我的环境，首先呢，是一张交易表transaction，有transaction_id varchar(32)，user_id int4，等字段。其中有1100万条数据。transaction_id做了唯一btree索引。
另一张是用户表user，有1000条左右的数据，id int4是主键。
现在要查出指定1000个transaction_id的交易记录和用户信息，下面是我的查询语句和执行计划。
--(1)
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM transaction t
JOIN user u ON t.user_id = u.id
WHERE transaction_id IN ('p1605170001BC33752830','p160519000129B89658337','p1603280001EE231575962',...和其它996个...,'p1604190001F9188378917')
AND t.user_id = 5392;
Nested Loop (cost=4580.87..4594.16 rows=1 width=2072) (actual time=17.694..17.706 rows=4 loops=1)
Buffers: shared hit=4038
-> Bitmap Heap Scan on transaction t (cost=4580.58..4585.84 rows=1 width=470) (actual time=17.686..17.690 rows=4 loops=1)
Recheck Cond: ((user_id = 5392) AND ((transaction_id)::text = ANY ('{p1605170001BC33752830,p160519000129B89658337,p1603280001EE231575962,...和其它996个...,p1604190001F9188378917}'::text[])))
Heap Blocks: exact=4
Buffers: shared hit=4026
-> BitmapAnd (cost=4580.58..4580.58 rows=1 width=0) (actual time=17.680..17.680 rows=0 loops=1)
Buffers: shared hit=4022
-> Bitmap Index Scan on fk_transaction_user_id (cost=0.00..34.33 rows=1852 width=0) (actual time=0.129..0.129 rows=1001 loops=1)
Index Cond: (user_id = 5392)
Buffers: shared hit=7
-> Bitmap Index Scan on uk_transaction (cost=0.00..4546.00 rows=1000 width=0) (actual time=17.482..17.482 rows=1000 loops=1)
Index Cond: ((transaction_id)::text = ANY ('{p1605170001BC33752830,p160519000129B89658337,p1603280001EE231575962,...和其它996个...,p1604190001F9188378917}'::text[]))
Buffers: shared hit=4015
-> Index Scan using user_pkey on user u (cost=0.29..8.30 rows=1 width=1602) (actual time=0.002..0.002 rows=1 loops=4)
Index Cond: (id = 5392)
Buffers: shared hit=12
Planning time: 0.734 ms
Execution time: 22.647 ms
--(2)
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM transaction t
JOIN user u ON t.user_id = u.id
WHERE transaction_id = ANY ('{p1605170001BC33752830,p160519000129B89658337,p1603280001EE231575962,...和其它996个...,p1604190001F9188378917}'::TEXT[])
AND t.user_id = 5392;
Nested Loop (cost=4565.40..4578.69 rows=1 width=2064) (actual time=17.698..17.710 rows=4 loops=1)
Buffers: shared hit=4037
-> Bitmap Heap Scan on transaction t (cost=4565.11..4570.37 rows=1 width=462) (actual time=17.688..17.691 rows=4 loops=1)
Recheck Cond: ((user_id = 5392) AND ((transaction_id)::text = ANY ('{p1605170001BC33752830,p160519000129B89658337,p1603280001EE231575962,...和其它996个...,p1604190001F9188378917}'::text[])))
Heap Blocks: exact=4
Buffers: shared hit=4025
-> BitmapAnd (cost=4565.11..4565.11 rows=1 width=0) (actual time=17.683..17.683 rows=0 loops=1)
Buffers: shared hit=4021
-> Bitmap Index Scan on fk_transaction_user_id (cost=0.00..25.36 rows=1190 width=0) (actual time=0.169..0.169 rows=1001 loops=1)
Index Cond: (user_id = 5392)
Buffers: shared hit=7
-> Bitmap Index Scan on uk_transaction (cost=0.00..4539.50 rows=1000 width=0) (actual time=17.445..17.445 rows=1000 loops=1)
Index Cond: ((transaction_id)::text = ANY ('{p1605170001BC33752830,p160519000129B89658337,p1603280001EE231575962,...和其它996个...,p1604190001F9188378917}'::text[]))
Buffers: shared hit=4014
-> Index Scan using user_pkey on user u (cost=0.29..8.30 rows=1 width=1602) (actual time=0.003..0.003 rows=1 loops=4)
Index Cond: (user_id = 5392)
Buffers: shared hit=12
Planning time: 0.754 ms
Execution time: 22.680 ms--(3)
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM transaction t
JOIN user u ON t.user_id = u.id
WHERE transaction_id = ANY (VALUES('p1605170001BC33752830'),('p160519000129B89658337'),('p1603280001EE231575962'),...和其它996个...,('p1604190001F9188378917'))
AND t.user_id = 5392;
Nested Loop (cost=15.85..1749.26 rows=595 width=2064) (actual time=2.168..19.464 rows=4 loops=1)
Buffers: shared hit=5018
-> Index Scan using user_pkey on user u (cost=0.29..8.30 rows=1 width=1602) (actual time=0.006..0.006 rows=1 loops=1)
Index Cond: (user_id = 5392)
Buffers: shared hit=3
-> Nested Loop (cost=15.56..1735.00 rows=595 width=462) (actual time=2.160..19.454 rows=4 loops=1)
Buffers: shared hit=5015
-> HashAggregate (cost=15.00..17.00 rows=200 width=32) (actual time=0.424..0.572 rows=1000 loops=1)
Group Key: "*VALUES*".column1
-> Values Scan on "*VALUES*" (cost=0.00..12.50 rows=1000 width=32) (actual time=0.003..0.124 rows=1000 loops=1)
-> Index Scan using uk_transaction on transaction t (cost=0.56..8.58 rows=1 width=462) (actual time=0.019..0.019 rows=0 loops=1000)
Index Cond: ((transaction_id)::text = "*VALUES*".column1)
Filter: (user_id = 5392)
Rows Removed by Filter: 1
Buffers: shared hit=5015
Planning time: 0.943 ms
Execution time: 19.581 ms
其中（1）和（2）的执行计划几乎是一样的。查询时间多次执行后稳定在30ms，（3）就是那篇博客推荐的写法，用时稳定在27ms。只有3ms的提升。主要是没能模拟出来那个慢查询。
