|
2. 95,147笔记录
- postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where c2<10;
- QUERY PLAN
- ---------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on postgres.tbl (cost=835.73..112379.10 rows=99785 width=73) (actual time=69.243..179.388 rows=95147 loops=1)
- Output: id, info, crt_time, pos, c1, c2, c3
- Recheck Cond: (tbl.c2 < 10)
- Heap Blocks: exact=88681
- Buffers: shared hit=88734
- -> Bitmap Index Scan on idx_tbl_1 (cost=0.00..810.79 rows=99785 width=0) (actual time=53.612..53.612 rows=95147 loops=1)
- Index Cond: (tbl.c2 < 10)
- Buffers: shared hit=53
- Planning time: 0.094 ms
- Execution time: 186.201 ms
- (10 rows)
3. 149930笔记录(为快速得到功效，PostgreSQL行使位图举办归并扫描)
- postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where c1 in (1,2,3,4,100,200,99,88,77,66,55) or c2 <10;
- QUERY PLAN
- ------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on postgres.tbl (cost=1694.23..166303.58 rows=153828 width=73) (actual time=98.988..266.852 rows=149930 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))
- Heap Blocks: exact=134424
- Buffers: shared hit=134565
- -> BitmapOr (cost=1694.23..1694.23 rows=153936 width=0) (actual time=73.763..73.763 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=16.733..16.733 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=57.029..57.029 rows=95147 loops=1)
- Index Cond: (tbl.c2 < 10)
- Buffers: shared hit=53
- Planning time: 0.149 ms
- Execution time: 274.548 ms
- (15 rows)
4. 60,687笔记录(纵然运用精彩的KNN机能优化，如故必要淹灭195毫秒)。
- postgres=# explain (analyze,verbose,timing,costs,buffers) select * from ff(point (0,0) ,5,1000000);
- QUERY PLAN
- ----------------------------------------------------------------------------------------------------------------------
- Function Scan on postgres.ff (cost=0.25..10.25 rows=1000 width=6) (actual time=188.563..192.114 rows=60687 loops=1)
- Output: ff
- Function Call: ff( (0,0) ::point, 5 ::double precision, 1000000)
- Buffers: shared hit=61296
- Planning time: 0.029 ms
- Execution time: 195.097 ms
- (6 rows)
让我们看看不行使KNN优化必要多长时刻。
功效很是令人惊奇——极限优化机能进步了一个数目级。
5. 2,640,751笔记录
行使全部索引逐个扫描数据前提，获得ctid并执行ctid扫描。
此刻，让我们来解析这个进程：
起首，让我们看看时刻和工具属性的归并查询，成就很是惊人。行使位图BitmapOr时，查询可以跳过大大都数据块，而且扫描时刻比单索引扫描要短。 （编辑：湖南网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
