问题描述
我已经编写了一个守护程序处理器，它将从一个数据库中获取行并将其插入到另一个数据库中进行同步。它将根据 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:
- 看看。
- 或将与。
- 或。
已添加到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.
这篇关于性能调优：为布尔列创建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！