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

问题描述

我编写了一个守护进程处理器，它将从一个数据库中获取记录并将它们插入另一个数据库进行同步。它将根据每个记录指示标志获取记录，该标志是布尔数据类型。

I have written a daemon processor which will fetch the records from one database and insert them into another database for synchronizing. It will fetch records based on each record indication flag which is boolean datatype.

我的表有数十万条记录。当我选择sync_done为false的记录时，是否会导致任何数据库性能问题？或者我应该为该sync_done列（布尔数据类型）应用索引，以提高性能，因为它将对sync_done值为false的记录应用select操作？

My tables has hundreds of thousands of records. When I select the record whichever sync_done is false, will it cause any database performance issues? Or should I apply indexing for that sync_done column (boolean datatype), to improve performance, since it will apply select operation on records with a sync_done value of false?

例如，说我有10000条记录。其中，9500已经同步（sync_done为true），将仅选择记录的其余部分（sync_done为false）。
最终9500条记录不会受到选择操作的影响。

For example, say I have 10000 records. Of those, 9500 have already been synchronized (sync_done is true), will select only rest of the record (sync_done is false). Ultimately 9500 records would not come under the select operation.

请建议我如何进行。

推荐答案

对于像这样的查询部分索引将为您提供最佳服务。

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.

• 看看 LISTEN / NOTIFY 。


• 或使用< a href =http://www.postgresql.org/docs/current/interactive/triggers.html\"rel =noreferrer>触发器与 dblink 。


• 或其中一个许多可用的复制方法。

• Have a look at LISTEN / NOTIFY.
• Or use a trigger in combination with dblink.
• Or one of the many available replication methods.

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