Thread: update non-indexed value is slow if some non-related index/fk are enabled

Hi all,

I'm look for some hint about this behaviour :

One UPDATE of one boolean value which is non-indexed take some time ( for many tuples .. 3 Millions ).

But if we disable all indexes/fk the UPDATE is 10x faster and do 10x less shared hit .

I don't understand why disabling all index from the table speed up the update because the boolean column is not indexed

For exemple , disabling the index was done like this :

UPDATE pg_index SET indisready=false WHERE indexrelid in (620809,620837,620839,620841,620854,618764,620855,620790,620790,620840);

In both UPDATE with or without index enabled/disable, the PLAN is the same.

One UPDATE of one boolean value which is non-indexed take some time ( for many tuples .. 3 Millions ).

But if we disable all indexes/fk the UPDATE is 10x faster and do 10x less shared hit .

I don't understand why disabling all index from the table speed up the update because the boolean column is not indexed

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, September 3, 2021, Philippe Doussot <philippe.doussot@up.coop>
> wrote:
>> I don't understand why disabling all index from the table speed up the
>> update because the boolean column is not indexed

> Index entries point to physical records.  You just deleted one physical
> record and added another.  The indexes need to be updated with that
> information.

Yeah.  The OP's mental model is apparently update-in-place, but that's
not how Postgres does things.

The index-update overhead is avoided if the update is "HOT", which
requires that (a) no indexed column changes and (b) there is room
on the same page for the new copy of the row.  Ensuring (b) requires
running with a fairly low fill-factor, which bloats your table and
thereby creates its own costs.  Still, that might be worth doing
depending on your particular circumstances.

            regards, tom lane

On Sun, 2021-09-05 at 11:21 -0400, Tom Lane wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Friday, September 3, 2021, Philippe Doussot <
> > philippe.doussot@up.coop>
> > wrote:
> > > I don't understand why disabling all index from the table speed
> > > up the
> > > update because the boolean column is not indexed
> 
> > Index entries point to physical records.  You just deleted one
> > physical
> > record and added another.  The indexes need to be updated with that
> > information.
> 
> Yeah.  The OP's mental model is apparently update-in-place, but
> that's
> not how Postgres does things.
> 
> The index-update overhead is avoided if the update is "HOT", which
> requires that (a) no indexed column changes and (b) there is room
> on the same page for the new copy of the row.  Ensuring (b) requires
> running with a fairly low fill-factor, which bloats your table and
> thereby creates its own costs.  Still, that might be worth doing
> depending on your particular circumstances.
> 
>                         regards, tom lane
> 
> 
If the DDL for that table had the column defined like this:-

my_boolean  BOOLEAN,

instead of:-

my_boolean  BOOLEAN NOT NULL DEFAULT FALSE/TRUE, (whichever is
convenient)

then that column would contain either 'f' or 't' on insert instead of
null.

Then even if a fillfactor was not specified for that table, an update
of that single column (which does not appear in an index) would merely
swap the values.
Surely that would write it back in place?

Also, having boolean columns containing a null makes it difficult for
the getter's of that table deciding if 'null' is true or false.

Just an observation.

Rob

On Sun, 2021-09-05 at 11:21 -0400, Tom Lane wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Friday, September 3, 2021, Philippe Doussot <
philippe.doussot@up.coop>
wrote:

I don't understand why disabling all index from the table speed
up the
update because the boolean column is not indexed

Index entries point to physical records.  You just deleted one
physical
record and added another.  The indexes need to be updated with that
information.

Yeah.  The OP's mental model is apparently update-in-place, but
that's
not how Postgres does things.

The index-update overhead is avoided if the update is "HOT", which
requires that (a) no indexed column changes and (b) there is room
on the same page for the new copy of the row.  Ensuring (b) requires
running with a fairly low fill-factor, which bloats your table and
thereby creates its own costs.  Still, that might be worth doing
depending on your particular circumstances.

                        regards, tom lane

If the DDL for that table had the column defined like this:-

my_boolean  BOOLEAN,

instead of:-

my_boolean  BOOLEAN NOT NULL DEFAULT FALSE/TRUE, (whichever is
convenient)

then that column would contain either 'f' or 't' on insert instead of
null.

Then even if a fillfactor was not specified for that table, an update
of that single column (which does not appear in an index) would merely
swap the values.
Surely that would write it back in place?

Also, having boolean columns containing a null makes it difficult for
the getter's of that table deciding if 'null' is true or false.

Just an observation.

Rob

Yes my columns are NOT NULL DEFAULT FALSE

but the update always change the ctid ( new row in page )

I whas hopping the same optimisation as you: Write in place.

For boolean it is maybe doable because the value is fixed in size but for variable length ..

I was also expecting no row rewrite if value don't change .. easy for boolean but not for bigger fields

Philippe

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Friday, September 3, 2021, Philippe Doussot <philippe.doussot@up.coop>
wrote:

I don't understand why disabling all index from the table speed up the
update because the boolean column is not indexed

Index entries point to physical records.  You just deleted one physical
record and added another.  The indexes need to be updated with that
information.

Yeah.  The OP's mental model is apparently update-in-place, but that's
not how Postgres does things.

The index-update overhead is avoided if the update is "HOT", which
requires that (a) no indexed column changes and (b) there is room
on the same page for the new copy of the row.  Ensuring (b) requires
running with a fairly low fill-factor, which bloats your table and
thereby creates its own costs.  Still, that might be worth doing
depending on your particular circumstances.
			regards, tom lane

I whas hopping the same optimisation as you: Write in place.

On Monday, September 6, 2021, Philippe Doussot <philippe.doussot@up.coop> wrote:

I whas hopping the same optimisation as you: Write in place.

How exactly would you expect “update-in-place” to work given the nature of MVCC?

David J.

Yes right, it is probably not possible due to concurrency.

I don't know enough about it.

My expectations was naive

Again, thanks David for pointing me to the fact that the raw record was new on each update.

Philippe

On 2021-09-06 07:13:07 -0700, David G. Johnston wrote:
> On Monday, September 6, 2021, Philippe Doussot <philippe.doussot@up.coop>
> wrote:
>
>     I whas hopping the same optimisation as you: Write in place.
>
> How exactly would you expect “update-in-place” to work given the nature of
> MVCC?

Some databases (e.g. Oracle) store the old row in a separate place
(called an UNDO segment in Oracle) and then overwrite the row in place.
When another transaction tries to access the (not yet committed) row or
when the transaction is rolled the old row is retrieved from the undo
segment.

There are pros and cons to both approaches. Personally, I had less
problems with PostgreSQL's approach than with Oracle's.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"