pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Indexing on a boolean field?
2+ messages / 2 participants
[nested] [flat]

* Indexing on a boolean field?
@ 1998-06-15 15:33  Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Tom Lane @ 1998-06-15 15:33 UTC (permalink / raw)
  To: pgsql-sql

Can anyone suggest to me a better way to handle this?

I want to be able to select out the elements of a table that have
a TRUE value in a boolean field "flag".  (Basically the flag means
the record hasn't been processed yet.)  I can do something like

	SELECT * FROM table WHERE flag = 't';

However, the table is going to get large, and most of the elements
will have flag = 'f' as transaction history accumulates.  It looks
to me like a select done as above will have to be processed by scanning
the whole table; that's not going to do.

I thought of creating an index on the flag field, but soon found that
you can't do it in Postgres (there's no operator for index on boolean).
In any case, I doubt that btree or hash indexes would work well with
only two distinct data values.

Another possibility is to keep the not-yet-processed records in a
separate table, but that seems pretty ugly as well; especially since
I sometimes want to see both processed and unprocessed records.

Has anyone got some advice on how to approach this problem?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] Indexing on a boolean field?
@ 1998-06-15 16:17  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Herouth Maoz @ 1998-06-15 16:17 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

At 18:33 +0300 on 15/6/98, Tom Lane wrote:


> I want to be able to select out the elements of a table that have
> a TRUE value in a boolean field "flag".  (Basically the flag means
> the record hasn't been processed yet.)  I can do something like
>
> 	SELECT * FROM table WHERE flag = 't';

I know I'm nitpicking, but I think "WHERE flag" is more elegant. I have an
aversion to comparing to boolean literals. Flag = 'true' is equivalent to
Flag, and Flag = 'false' is equivalent to NOT Flag in every language I know.

> I thought of creating an index on the flag field, but soon found that
> you can't do it in Postgres (there's no operator for index on boolean).
> In any case, I doubt that btree or hash indexes would work well with
> only two distinct data values.

I don't know that they won't work well. They will both probably reduce to
sequential scan on all the records with the same value - which is what you
need. I mean, as soon as you pick the bucket with all 'true' values and no
'false' values, you've made the saving you needed.

> Another possibility is to keep the not-yet-processed records in a
> separate table, but that seems pretty ugly as well; especially since
> I sometimes want to see both processed and unprocessed records.

Well, you could use a union to do that.

> Has anyone got some advice on how to approach this problem?

If you ask me, I'd just replace the boolean with a char flag. Less elegant,
perhaps (and would make your above query stay the way you wrote it...), but
since I don't think booleans take less space than a whole byte anyway, it's
as space-efficient as booleans, and it has its own index operator family.

In case you're worried about integrity, you can define a constraint on the
char field, making sure it doesn't enter anything except 'T' and 'F', or
't' and 'f', or whatever.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1998-06-15 16:17 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-06-15 15:33 Indexing on a boolean field? Tom Lane <tgl@sss.pgh.pa.us>
1998-06-15 16:17 ` Herouth Maoz <herouth@oumail.openu.ac.il>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox