Обсуждение: assymetry updating a boolean (=FALSE faster than =TRUE)
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
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
"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
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 >