CREATE INDEX orders_unbilled_index ON orders (order_nr) WHERE billed is not true;
And the suggested use mode is
SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;
My question is after an update to the billed column is done, will PG automatically add or remove records whose billed are just set to false or true to/from the b-tree?
Thanks in advance.
Hi,
the short answer is: yes, it will work as you expect.
The long answer is: no, it will not simply add/remove because postgres keeps many different versions of the same row, so when you change the column from false to true, the new row version will be added to the index, when you change from true to false, the previous rows will be still stored in the index as well, because there could be some older transaction which should see some older version of the row.
The mechanism is quite internal, and you shouldn't bother. As a database user you should just see, that the index is updated automatically, and it will store all rows where billed = true.