Re: 1 char in the world

Tom Lane wrote:
> I don't believe those numbers for a moment. All else being equal,
> comparing a "char" field to a literal should be exactly the same speed
> as comparing a bool field to a literal (and if you'd just said "where bool",
> the bool field would be faster). Both ought to be markedly faster than
> text.
>
> Look for errors in your test procedure. One thing I'd particularly
> wonder about is whether the query plans are the same. In the absence of
> any VACUUM ANALYZE data, I'd fully expect the planner to pick a
> different plan for a bool field than text/char --- because even without
> ANALYZE data, it knows that a bool column has only two possible values.

Well, the previous test was done on REAL data. Everything was indexed
and vacuum analyzed as it should be.

However, I generated some test data under "controlled" circumstances and
did get different results. Bear in mind, though, that the data is no
longer "real", and doesn't represent the system I am concerned about.

[Someone requested some tests with int4/int8, too, so I included them,
as well. However, I would never use 4 or 8 bytes to store one bit.
Since a byte is platform-atomic, however, I will use a whole byte for a
single bit, as bit packing is too expensive.]

create table booltest (
boo boolean,
cha "char",
txt text,
in4 int4,
in8 int8
);

Insert lots of data here, but stay consistent between fields. [If you
insert a TRUE into a boolean, put a 'Y' into a text or "char" field and
a 1 into an int type.] So, I basically had 2 different insert
statements (one for true and one for false), and I used a random number
generator to get a good distribution of them.

create index booidx on booltest(boo);
create index chaidx on booltest(cha);
create index txtidx on booltest(txt);
create index in4idx on booltest(in4);
create index in8idx on booltest(in8);

vacuum full verbose analyze booltest;
INFO: --Relation public.booltest--
INFO: Pages 6897: Changed 0, reaped 0, Empty 0, New 0; Tup 1000000: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 52, MaxLen 52; Re-using: Free/Avail.
Space 362284/362284; EndEmpty/Avail. Pages 0/6897.
CPU 0.53s/0.41u sec elapsed 18.69 sec.
INFO: Index booidx: Pages 2193; Tuples 1000000.
CPU 0.24s/0.11u sec elapsed 3.33 sec.
INFO: Index chaidx: Pages 2193; Tuples 1000000.
CPU 0.23s/0.19u sec elapsed 4.01 sec.
INFO: Index txtidx: Pages 2745; Tuples 1000000.
CPU 0.51s/0.14u sec elapsed 4.07 sec.
INFO: Index in4idx: Pages 2193; Tuples 1000000.
CPU 0.20s/0.17u sec elapsed 3.51 sec.
INFO: Index in8idx: Pages 2745; Tuples 1000000.
CPU 0.26s/0.04u sec elapsed 1.92 sec.
INFO: Rel booltest: Pages: 6897 --> 6897; Tuple(s) moved: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_4327226--
INFO: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Index pg_toast_4327226_index: Pages 1; Tuples 0.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO: Analyzing public.booltest
VACUUM

Count our test set:
select count(*) from booltest; [ALL] output:1000000
select count(*) from booltest where boo; [TRUES] output:498649

TESTS .....

1) INT8=1
explain analyze select count(*) from booltest where in8 = '1';
Aggregate (cost=1342272.26..1342272.26 rows=1 width=0) (actual
time=3434.37..3434.37 rows=1 loops=1)
-> Index Scan using in8idx on booltest (cost=0.00..1340996.42
rows=510333 width=0) (actual time=6.96..2704.45 rows=498649 loops=1)
Index Cond: (in8 = 1::bigint)
Total runtime: 3434.50 msec

2) INT4=1
explain analyze select count(*) from booltest where in4 = 1;
Aggregate (cost=1341990.26..1341990.26 rows=1 width=0) (actual
time=3219.24..3219.24 rows=1 loops=1)
-> Index Scan using in4idx on booltest (cost=0.00..1340714.42
rows=510333 width=0) (actual time=12.92..2548.20 rows=498649 loops=1)
Index Cond: (in4 = 1)
Total runtime: 3219.35 msec

3) TEXT='Y'
explain analyze select count(*) from booltest where txt = 'Y';
Aggregate (cost=1342272.26..1342272.26 rows=1 width=0) (actual
time=4820.06..4820.06 rows=1 loops=1)
-> Index Scan using txtidx on booltest (cost=0.00..1340996.42
rows=510333 width=0) (actual time=15.83..4042.07 rows=498649 loops=1)
Index Cond: (txt = 'Y'::text)
Total runtime: 4820.18 msec

4) BOOLEAN=true
explain analyze select count(*) from booltest where boo = true;
Aggregate (cost=1341990.26..1341990.26 rows=1 width=0) (actual
time=3437.30..3437.30 rows=1 loops=1)
-> Index Scan using booidx on booltest (cost=0.00..1340714.42
rows=510333 width=0) (actual time=28.16..2751.38 rows=498649 loops=1)
Index Cond: (boo = true)
Total runtime: 3437.42 msec

5) BOOLEAN [implied = true]
explain analyze select count(*) from booltest where boo;
Aggregate (cost=100018172.83..100018172.83 rows=1 width=0) (actual
time=2775.40..2775.40 rows=1 loops=1)
-> Seq Scan on booltest (cost=100000000.00..100016897.00 rows=510333
width=0) (actual time=0.10..2138.11 rows=498649 loops=1)
Filter: boo
Total runtime: 2775.50 msec

6) "char"='Y'
explain analyze select count(*) from booltest where cha = 'Y';
Aggregate (cost=1341990.26..1341990.26 rows=1 width=0) (actual
time=3379.71..3379.71 rows=1 loops=1)
-> Index Scan using chaidx on booltest (cost=0.00..1340714.42
rows=510333 width=0) (actual time=32.77..2695.77 rows=498649 loops=1)
Index Cond: (cha = 'Y'::"char")
Total runtime: 3379.82 msec

Average ms over 42 attempts per test, some one-after-the-other, others
mixed with other queries, was:

1) INT8=1 3229.76
2) INT4=1 3194.45
3) TEXT='Y' 4799.23
4) BOOLEAN=true 3283.30
5) BOOLEAN 2801.83
6) "char"='Y' 3290.15

The straight boolean test was the fastest at 2.8 secs, and the TEXT was
the slowest at 4.8 secs. Everything else settled in the same pot at
3.25 secs.

I wasn't too impressed with any of these times, actually, but I'm
bearing in mind that we are talking about an aggregate, which I have
learned much about in the last few days from the mailing list, and which
I expect to be slow in PG.

Since straight-BOOLEAN [not adding "= true" to the condition] is about
15% faster than "char", I will stick with BOOLEAN.

My immediate future plans also include recoding my system to never use
aggregates in "live" queries. I will be tracking statistics in real
time in statistics tables instead of hitting the database with an
aggregate. It is much cheaper to add a few milliseconds per insert than
to slow my whole system down for several seconds during an aggregate query.

In fact, if you have a sizable table, and especially if you are running
an OLTP server, unless you are manually investigating something in the
table, I recommend never using aggregates. Your SQL queries should be
aggregate-free for large tables, if possible.

Thanks!

--
Matt Mello