性能调优：为布尔列创建索引 [英] Performance Tuning: Create index for boolean column

问题描述

我已经编写了一个守护程序处理器，它将从一个数据库中获取行并将其插入到另一个数据库中进行同步。它将根据 boolean 指示标志 sync_done 获取行。

I have written a daemon processor which will fetch rows from one database and insert them into another for synchronizing. It will fetch rows based on a boolean indication flag sync_done.

我的表有成千上万的行。当我选择所有 sync_done为false 的行时，是否会引起数据库性能问题？我应该为该 sync_done 列应用索引以提高性能，因为只有 sync_done 值小于<$ c $的行c> false 是否被提取？

My table has hundreds of thousands of rows. When I select all rows with sync_done is false, will it cause any database performance issues? Should I apply indexing for that sync_done column to improve performance, since only rows with a sync_done value of false are fetched?

说，我有10000行。其中有9500个已经同步（ sync_done为真），因此不会被选择。

Say, I have 10000 rows. Of those, 9500 have already been synchronized (sync_done is true) and will not be selected.

请提出建议我可能会继续。

Please suggest how I might proceed.

推荐答案

对于这样的查询，请部分索引将最适合您。

For a query like this a partial index would serve you best.

CREATE INDEX ON tbl (id) WHERE sync_done = FALSE;

但是，对于这样的用例，其他同步方法最好以以下内容开头：

However, for a use case like this, other synchronization methods may be preferable to begin with:

• 看看 收听 / 注意 。


• 或将触发与 dblink 。


• 或许多可用的复制方法。
  
  流复制已添加到Postgres 9.0中，并且变得越来越流行。

• Have a look at LISTEN / NOTIFY.
• Or use a trigger in combination with dblink.
• Or one of the many available replication methods.
  Streaming Replication was added with Postgres 9.0 and has become increasingly popular.

这篇关于性能调优：为布尔列创建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！