Re: question about partial index

On 18 March 2014 22:26, Yu Zhao <yzhao81(at)gmail(dot)com> wrote:

> In PostgreSQL 9.3.3 Documentation 11.8. Partial Indexes Example 11-2
> (http://www.postgresql.org/docs/9.3/interactive/indexes-partial.html)
> the partial index is created
>
> 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.

regards,
Szymon