Обсуждение: assymetry updating a boolean (=FALSE faster than =TRUE)

Поиск
Список
Период
Сортировка

assymetry updating a boolean (=FALSE faster than =TRUE)

От
"George Pavlov"
Дата:
Here is something that seems anomalous to me: when I set a boolean field
to FALSE performance is much better than when I set it to TRUE. Any
reason for FALSE to be favored over TRUE?

Some details:

vacuum analyze my_table;
update my_table set is_foo=FALSE where some_id = 47;
--142 rows affected, 8047 ms execution time.
vacuum analyze my_table;
update my_table set is_foo=TRUE where some_id = 47;
--142 rows affected, 48609 ms execution time.

I have run these kinds of queries repeatedly and the timing above is
representative--the setting to FALSE case is about 6 times more
performant. The table my_table has about 105K rows and has many other
columns of various types. Thre is a trigger on the table, but it does
not do anything special based on this column's value. The some_id column
is indexed. This is on PG 8.1.3 on Linux.

George

Re: assymetry updating a boolean (=FALSE faster than =TRUE)

От
"Jim C. Nasby"
Дата:
On Tue, May 23, 2006 at 02:55:03PM -0700, George Pavlov wrote:
> Here is something that seems anomalous to me: when I set a boolean field
> to FALSE performance is much better than when I set it to TRUE. Any
> reason for FALSE to be favored over TRUE?
>
> Some details:
>
> vacuum analyze my_table;
> update my_table set is_foo=FALSE where some_id = 47;
> --142 rows affected, 8047 ms execution time.
> vacuum analyze my_table;
> update my_table set is_foo=TRUE where some_id = 47;
> --142 rows affected, 48609 ms execution time.
>
> I have run these kinds of queries repeatedly and the timing above is
> representative--the setting to FALSE case is about 6 times more
> performant. The table my_table has about 105K rows and has many other
> columns of various types. Thre is a trigger on the table, but it does
> not do anything special based on this column's value. The some_id column
> is indexed. This is on PG 8.1.3 on Linux.

Are there any indexes containing is_foo?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: assymetry updating a boolean (=FALSE faster than =TRUE)

От
Tom Lane
Дата:
"George Pavlov" <gpavlov@mynewplace.com> writes:
> Here is something that seems anomalous to me: when I set a boolean field
> to FALSE performance is much better than when I set it to TRUE. Any
> reason for FALSE to be favored over TRUE?

It isn't.  You're measuring something else than you think you're
measuring ... maybe a partial index?  Varying effects of where the
free space is in the table?  In a test case I see no significant
difference:

regression=# create table foo(f1 int, f2 bool);
CREATE TABLE
regression=# insert into foo select x, true from generate_series(1,100000) x;
INSERT 0 100000
regression=# \timing
Timing is on.
regression=# vacuum foo;
VACUUM
Time: 318.153 ms
regression=# update foo set f2 = true;
UPDATE 100000
Time: 2553.014 ms
regression=# vacuum foo;
VACUUM
Time: 410.356 ms
regression=# update foo set f2 = false;
UPDATE 100000
Time: 2453.620 ms
regression=# vacuum foo;
VACUUM
Time: 405.955 ms
regression=# update foo set f2 = true;
UPDATE 100000
Time: 2497.413 ms
regression=# vacuum foo;
VACUUM
Time: 408.403 ms
regression=# update foo set f2 = false;
UPDATE 100000
Time: 2458.824 ms
regression=#

            regards, tom lane

Re: assymetry updating a boolean (=FALSE faster than =TRUE)

От
"George Pavlov"
Дата:
no partial (or other) index affecting these results. just a trigger the
outcome of which was affected by the value of that particular boolean.
sorry, should have looked more carefully...

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, May 23, 2006 4:00 PM
> To: George Pavlov
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] assymetry updating a boolean (=FALSE
> faster than =TRUE)
>
> "George Pavlov" <gpavlov@mynewplace.com> writes:
> > Here is something that seems anomalous to me: when I set a
> boolean field
> > to FALSE performance is much better than when I set it to TRUE. Any
> > reason for FALSE to be favored over TRUE?
>
> It isn't.  You're measuring something else than you think you're
> measuring ... maybe a partial index?  Varying effects of where the
> free space is in the table?  In a test case I see no significant
> difference:
>
> regression=# create table foo(f1 int, f2 bool);
> CREATE TABLE
> regression=# insert into foo select x, true from
> generate_series(1,100000) x;
> INSERT 0 100000
> regression=# \timing
> Timing is on.
> regression=# vacuum foo;
> VACUUM
> Time: 318.153 ms
> regression=# update foo set f2 = true;
> UPDATE 100000
> Time: 2553.014 ms
> regression=# vacuum foo;
> VACUUM
> Time: 410.356 ms
> regression=# update foo set f2 = false;
> UPDATE 100000
> Time: 2453.620 ms
> regression=# vacuum foo;
> VACUUM
> Time: 405.955 ms
> regression=# update foo set f2 = true;
> UPDATE 100000
> Time: 2497.413 ms
> regression=# vacuum foo;
> VACUUM
> Time: 408.403 ms
> regression=# update foo set f2 = false;
> UPDATE 100000
> Time: 2458.824 ms
> regression=#
>
>             regards, tom lane
>