我对这个状态感到很困惑，我发现查询一个表时
“limit 1”的无索引列的where条件非常
fastT，但是列没有索引。下面是一个示例：
--1创建包含20000000数据的测试表
francs=> create table test_limit (id int4,name varchar(32));
CREATE TABLE
francs=> insert into test_limit select generate_series(1,20000000),generate_series(1,20000000) || 'a';
INSERT 0 20000000
francs=> \d test_limit;
Table "francs.test_limit"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(32) |
--2查询表
francs=> explain analyze select * from test_limit where id=1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on test_limit (cost=0.00..358111.05 rows=1 width=13) (actual time=0.028..3162.477 rows=1 loops=1)
Filter: (id = 1)
Total runtime: 3162.531 ms
(3 rows)
注意，它需要大约3162毫秒，这是非常缓慢的，因为我预期
--3查询表“限制1”原因
francs=> explain analyze select * from test_limit where id=1 limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..358111.05 rows=1 width=13) (actual time=0.019..0.019 rows=1 loops=1)
-> Seq Scan on test_limit (cost=0.00..358111.05 rows=1 width=13) (actual time=0.017..0.017 rows=1 loops=1)
Filter: (id = 1)
Total runtime: 0.047 ms
(4 rows)
注意，它只需要0.047毫秒，速度很快，但是列id没有索引，有人能解释吗？
谢谢！
--4添加试验
francs=> explain analyze select * from test_limit where id=2 limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..358111.05 rows=1 width=13) (actual time=0.023..0.023 rows=1 loops=1)
-> Seq Scan on test_limit (cost=0.00..358111.05 rows=1 width=13) (actual time=0.022..0.022 rows=1 loops=1)
Filter: (id = 2)
Total runtime: 0.066 ms
(4 rows)
francs=> explain analyze select * from test_limit where id=3 limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..358111.05 rows=1 width=13) (actual time=0.022..0.022 rows=1 loops=1)
-> Seq Scan on test_limit (cost=0.00..358111.05 rows=1 width=13) (actual time=0.021..0.021 rows=1 loops=1)
Filter: (id = 3)
Total runtime: 0.060 ms
(4 rows)
francs=> explain analyze select * from test_limit where id=101 limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..358111.05 rows=1 width=13) (actual time=0.035..0.036 rows=1 loops=1)
-> Seq Scan on test_limit (cost=0.00..358111.05 rows=1 width=13) (actual time=0.033..0.033 rows=1 loops=1)
Filter: (id = 101)
Total runtime: 0.075 ms
(4 rows)
francs=> explain analyze select * from test_limit where id=1001 limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..358111.05 rows=1 width=13) (actual time=0.192..0.192 rows=1 loops=1)
-> Seq Scan on test_limit (cost=0.00..358111.05 rows=1 width=13) (actual time=0.190..0.190 rows=1 loops=1)
Filter: (id = 1001)
Total runtime: 0.231 ms
(4 rows)
从加法测试，我们可以看出它也很快。
--5最终试验
francs=> explain analyze select * from test_limit where id=9999999 limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..358111.05 rows=1 width=13) (actual time=1379.153..1379.154 rows=1 loops=1)
-> Seq Scan on test_limit (cost=0.00..358111.05 rows=1 width=13) (actual time=1379.151..1379.151 rows=1 loops=1)
Filter: (id = 9999999)
Total runtime: 1379.206 ms
(4 rows)
从上面可以看出，我使用的是一个后来的id，它是99999999，现在很慢；我现在明白了，谢谢！
最佳答案
也许“id=1”在表中很早，所以当它按顺序读取表时，它将很快命中该行，并且由于您说“limit=1”，它可以在第一个结果之后停止。
或者，也可能涉及一些缓存。
关于postgresql - PostgreSQL:为什么查询条件为“限制为1”的无索引列的where条件的表很快？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12365063/