|
直接编写SQL查询，而不是使用多CTID扫描。
- postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl
- where
- crt_time between 2017-07-22 17:59:34 and 2017-07-22 17:59:40
- and (
- c1 in (1,2,3,4,100,200,99,88,77,66,55)
- or
- c2 < 10
- )
- and
- pos <-> point (0,0) < 5;
- Bitmap Heap Scan on postgres.tbl (cost=35022.06..44857.06 rows=2525 width=73) (actual time=205.542..214.547 rows=13 loops=1)
- Output: id, info, crt_time, pos, c1, c2, c3
- Recheck Cond: (((tbl.c1 = ANY ( {1,2,3,4,100,200,99,88,77,66,55} ::integer[])) OR (tbl.c2 < 10)) AND (tbl.crt_time >= 2017-07-22 17:59:34 ::timestamp without time zone) AND (tbl.crt_time <= 2017-07-22 17:59:40 ::timestamp without time zone))
- Filter: ((tbl.pos <-> (0,0) ::point) < 5 ::double precision)
- Rows Removed by Filter: 7834
- Heap Blocks: exact=6983
- Buffers: shared hit=14343
- -> BitmapAnd (cost=35022.06..35022.06 rows=7581 width=0) (actual time=203.620..203.620 rows=0 loops=1)
- Buffers: shared hit=7360
- -> BitmapOr (cost=1618.58..1618.58 rows=153936 width=0) (actual time=71.660..71.660 rows=0 loops=1)
- Buffers: shared hit=141
- -> Bitmap Index Scan on idx_tbl_1 (cost=0.00..806.54 rows=54151 width=0) (actual time=14.861..14.861 rows=54907 loops=1)
- Index Cond: (tbl.c1 = ANY ( {1,2,3,4,100,200,99,88,77,66,55} ::integer[]))
- Buffers: shared hit=88
- -> Bitmap Index Scan on idx_tbl_1 (cost=0.00..810.79 rows=99785 width=0) (actual time=56.797..56.797 rows=95147 loops=1)
- Index Cond: (tbl.c2 < 10)
- Buffers: shared hit=53
- -> Bitmap Index Scan on idx_tbl_2 (cost=0.00..33402.60 rows=2462443 width=0) (actual time=125.255..125.255 rows=2640751 loops=1)
- Index Cond: ((tbl.crt_time >= 2017-07-22 17:59:34 ::timestamp without time zone) AND (tbl.crt_time <= 2017-07-22 17:59:40 ::timestamp without time zone))
- Buffers: shared hit=7219
- Planning time: 0.160 ms
- Execution time: 216.797 ms
- (22 rows)
性能如预期的那样好，之前解释过原因。KNN条件以外的条件已经将结果收敛到7,000条记录，因此没有必要使用包含KNN条件的索引。(即使使用KNN索引也需要195毫秒，因为有60,687条记录满足KNN条件。) （编辑：晋中站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
