pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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> 1998-06-15 16:17 ` Re: [SQL] Indexing on a boolean field? Herouth Maoz <herouth@oumail.openu.ac.il> 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 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> 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