CREATE TABLE ticket
(
wid bigint NOT NULL DEFAULT nextval('tickets_id_seq'::regclass),eid bigint,created timestamp with time zone NOT NULL DEFAULT now(),status integer NOT NULL DEFAULT 0,argsxml text,moduleid character varying(255),source_id bigint,file_type_id bigint,file_name character varying(255),status_reason character varying(255),...
)
我在创建的时间戳上创建了一个索引,如下所示：
CREATE INDEX ticket_1_idx ON ticket USING btree (created );
这是我的疑问
select * from ticket where created between '2012-12-19 00:00:00' and '2012-12-20 00:00:00'
这个工作正常,直到记录数量开始增长(大约500万),现在它将永远回归.
解释分析揭示了这一点：
"Index Scan using ticket_1_idx on ticket (cost=0.00..10202.64 rows=52543 width=1297) (actual time=0.109..125.704 rows=53340 loops=1)" " Index Cond: ((created >= '2012-12-19 00:00:00+00'::timestamp with time zone) AND (created <= '2012-12-20 00:00:00+00'::timestamp with time zone))" "Total runtime: 175.853 ms"
到目前为止,我已经尝试过设置
random_page_cost = 1.75 effective_cache_size = 3
也创建了
create CLUSTER ticket USING ticket_1_idx;
CLUSTER
如果您打算使用CLUSTER,则显示的语法无效.
使用ticket_1_idx创建CLUSTER票证;
运行一次：
CLUSTER ticket USING ticket_1_idx;
对于更大的结果集,这可以帮助很多.返回单行并非如此.
Postgres会记住用于后续调用的索引.如果您的表格不是只读的,则效果会随着时间的推移而恶化,您需要以一定的间隔重新运行：
CLUSTER ticket;
可能只在易失性分区上.见下文.
但是,如果你有很多更新,CLUSTER(或VACUUM FULL)可能实际上对性能有害.适量的膨胀允许UPDATE在同一数据页面上放置新的行版本,并且避免了太频繁地在OS中物理扩展底层文件的需要.您可以使用经过精心调整的FILLFACTOR来充分利用这两个方面：
> Fill factor for a sequential index that is PK
CLUSTER对表进行独占锁定,这可能是多用户环境中的问题. Quoting the manual:
When a table is being clustered,an
ACCESS EXCLUSIVElock is acquired
on it. This prevents any other database operations (both reads and
writes) from operating on the table until theCLUSTERis finished.
大胆强调我的.考虑alternative pg_repack：
Unlike
CLUSTERandVACUUM FULLit works online,without holding an
exclusive lock on the processed tables during processing. pg_repack is
efficient to boot,with performance comparable to usingCLUSTERdirectly.
和：
pg_repack needs to take an exclusive lock at the end of the reorganization.
版本1.3.1适用于：
Postgresql 8.3,8.4,9.0,9.1,9.2,9.3,9.4
版本1.4.2适用于：
Postgresql 9.1,9.4,9.5,9.6,10
询问
但是,关于正确性的说法：BETWEEN构造包括边界.您的查询将选择12月19日的所有内容,以及12月20日00:00时的记录.这是一个非常不可能的要求.机会是,你真的想要：
SELECT * FROM ticket WHERE created >= '2012-12-19 0:0' AND created < '2012-12-20 0:0';
首先,你问：
Why is it selecting sequential scan?
您的EXPLAIN输出清楚地显示了索引扫描,而不是顺序表扫描.必定存在某种误解.
如果你为了更好的表现而努力,你可以改善一些事情.但是必要的背景信息不在问题中.可能的选择包括：
>您只能查询所需的列而不是*来降低传输成本(以及可能的其他性能优势).
>您可以查看partitioning并将实际时间片放入单独的表中.根据需要向分区添加索引.
>如果分区不是一个选项,另一个相关但较少侵入性的技术是添加一个或多个partial indexes.
例如,如果您主要查询当前月份,则可以创建以下部分索引：
CREATE INDEX ticket_created_idx ON ticket(created) WHERE created >= '2012-12-01 00:00:00'::timestamp;
在新月开始之前创建新索引.您可以使用cron作业轻松自动执行任务.
(可选)DROP部分索引在过去几个月之后.
>另外保留CLUSTER的总索引(不能对部分索引进行操作).如果旧记录永远不会更改,表分区将对此任务有很大帮助,因为您只需要重新集群较新的分区.
然后,如果记录永远不会改变,您可能不需要CLUSTER.
如果你结合最后两个步骤,性能应该是很棒的.
性能基础知识
您可能遗漏了其中一个基础知识.所有通常的性能建议都适用：
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
> https://wiki.postgresql.org/wiki/Performance_Optimization
