Re: [NOVICE] Boolean column in multicolumn index

Dima Pavlov <imyfess@gmail.com> writes:
> Test table and indexes:
> ------------------------

> CREATE TABLE public.t (id serial, cb boolean, ci integer, co integer)

> INSERT INTO t(cb, ci, co)
> SELECT ((round(random()*1))::int)::boolean, round(random()*100),
> round(random()*100)
> FROM generate_series(1, 1000000)

> CREATE INDEX "right" ON public.t USING btree (ci, cb, co);
> CREATE INDEX wrong ON public.t USING btree (ci, co);
> CREATE INDEX right_hack ON public.t USING btree (ci, (cb::integer), co);

> The problem is that I can't force PostgreSQL to use the "right" index.

Hmm.  Poking at this, it seems not to realize that the cb column is
rendered irrelevant to the index ordering, ie it doesn't notice that
using "right" would allow skipping the sort step.  That's a bug,
likely due to the hacking that goes on to allow "cb" and "cb = true"
to both be considered indexable conditions.

But probably the reason nobody's noticed before is that it's quite
uncommon to have boolean columns in indexes.  If you're only concerned
about doing this with "cb = TRUE", you might consider

CREATE INDEX partial ON public.t USING btree (ci, co) WHERE cb;

                        regards, tom lane