postgresql – 在timestamp列上按查询排序非常慢

-- From PostgreSQL 9.4; 9.3 is similar.
show shared_buffers; -- 128MB
show temp_buffers; -- 8MB
show work_mem; -- 4MB
show maintenance_work_mem; -- 64MB
show dynamic_shared_memory_type; -- posix
show default_statistics_target; -- 100
show autovacuum; -- on
show random_page_cost; -- 4
show cpu_index_tuple_cost; -- 0.005

制备

让我们建一张桌子. (你应该在你的问题中这样做.)

create table my_table (
  id serial primary key,insert_date timestamp not null
);

-- Round numbers of rows.
insert into my_table(insert_date)
select timestamp '2015-04-02 00:00:00'
from generate_series(1,3000000) n;

insert into my_table(insert_date)
select timestamp '2015-04-03 00:00:00'
from generate_series(1,6000000) n;

insert into my_table(insert_date)
select timestamp '2015-04-04 00:00:00'
from generate_series(1,500000) n;

insert into my_table(insert_date)
select timestamp '2015-04-05 00:00:00'
from generate_series(1,1200000) n;

insert into my_table(insert_date)
select timestamp '2015-04-06 00:00:00'
from generate_series(1,131000) n;

创建索引并更新统计信息.

create index on my_table (insert_date);
analyze my_table;

PostgreSQL 9.4

现在,我们从您的第一个查询中获得了什么样的执行计划？

explain analyze 
select id from my_table order by insert_date offset 0 limit 1;

"Limit  (cost=0.43..0.48 rows=1 width=12) (actual time=0.014..0.014 rows=1 loops=1)"
"  ->  Index Scan using my_table_insert_date_idx on my_table  (cost=0.43..540656.27 rows=11200977 width=12) (actual time=0.012..0.012 rows=1 loops=1)"
"Planning time: 0.195 ms"
"Execution time: 0.032 ms"

PostgreSQL 9.3

explain analyze 
select id from my_table order by insert_date offset 0 limit 1;

"Limit  (cost=0.43..0.47 rows=1 width=12) (actual time=0.058..0.059 rows=1 loops=1)"
"  ->  Index Scan using my_table_insert_date_idx on my_table  (cost=0.43..339814.36 rows=10830995 width=12) (actual time=0.057..0.057 rows=1 loops=1)"
"Total runtime: 0.098 ms"

您的查询

select id from my_table order by insert_date offset 0 limit 1;

是不确定的.根据ORDER BY子句,有300万行具有最低的insert_date(首先出现的日期).你挑选了300万中的一个. PostgreSQL不保证你每次都会得到相同的id.

如果你不关心它返回的300万个ID中的哪一个,你可以用不同的方式表达查询.但我不认为表达不同会给你160k倍的加速.

您可以针对特定查询更改您包含的某些设置.所以你可以做这样的事情.

-- Don't commit or rollback . . . 
begin transaction;
set local work_mem = '8 MB';

explain analyze 
select id from my_table order by insert_date offset 0 limit 1;
-- Displays the result.

手动提交或回滚.

commit;

您的work_mem设置返回到服务器启动时设置的值.

show work_mem; -- 4MB