Обсуждение: WHERE clause with 40+ conditions
My users are presented with a list of checkbox for 4 different attributes (e.g. type of image, country, etc.) For type of image they can choose JPG, PNG, JP2000, etc. or turn off the check box. My naive implementation would have a WHERE clause with over 40 different tests (type='JPG' OR type='PNG' OR country='USA' ...) There has to be an more efficient way to do this (and gives better hints to the PLANNER). Suggestions? -- View this message in context: http://postgresql.1045698.n5.nabble.com/WHERE-clause-with-40-conditions-tp5686659.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
On 5 May 2012 00:01, DrYSG <ygutfreund@draper.com> wrote: > My users are presented with a list of checkbox for 4 different attributes > (e.g. type of image, country, etc.) > > Suggestions? Not as a DB guy, but as a software guy, I might implement something along these lines: Create a bit array for each of the attribute, of sufficient length. Then assign one bit for each value. Assuming this is an image search system, and you want to search for PNG, JPG and GIF images, create a 3-bit array, with bit 0 turned on means include PNG in search, bit 1 turned on means include JPG in search and bit 2 turned on means include GIF In search. So, based on what the user selects, you will get a 3-bit number for image format. Similarly, create n-bit numbers for each of the other attributes. At the end, concatenate all of them to get one large N-bit number. Also, at the time of storing an image, I would populate such an N-bit number for each image, based on the attributes that are set for that particular image. Now, my query will have just one WHERE clause which would look like: WHERE bitfield_stored_in_db & bitfield_from_search_form <> 0; Binand
My issue is I am also not a DB guy, but a software guy. So while I appreciate the idea of a 40 bit field, that I would use as a mask to test against the query (an AND would do very nicely). I wonder if this would work well in a DB with 20M+ records in the table. The other fields (type, producer, etc.) are indexed as a B-Tree, but if one indexed this field, it is not clear to me if the DB planner would do an effective search. (that is what I do not know). -- View this message in context: http://postgresql.1045698.n5.nabble.com/WHERE-clause-with-40-conditions-tp5686659p5691174.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
On 2012-05-07 10:22, DrYSG wrote: > My issue is I am also not a DB guy, but a software guy. > > So while I appreciate the idea of a 40 bit field, that I would use as > a mask > to test against the query (an AND would do very nicely). I wonder if > this > would work well in a DB with 20M+ records in the table. > > The other fields (type, producer, etc.) are indexed as a B-Tree, but > if one > indexed this field, it is not clear to me if the DB planner would do > an > effective search. (that is what I do not know). Given that Postgresql has a bitstring data type especially for this type of use, I'd think it'd probably do ok... (Though I haven't run any tests.) Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
On 2012-05-04 14:31, DrYSG wrote: > My users are presented with a list of checkbox for 4 different > attributes > (e.g. type of image, country, etc.) > > For type of image they can choose JPG, PNG, JP2000, etc. or turn off > the > check box. > > My naive implementation would have a WHERE clause with over 40 > different > tests (type='JPG' OR type='PNG' OR country='USA' ...) > > There has to be an more efficient way to do this (and gives better > hints to > the PLANNER). Besides the bitmask already mentioned, you can also do something like this: (type IN ( 'JPG', 'PNG' ) OR country='USA') Which might simplify things for you a bit. See: http://www.postgresql.org/docs/9.1/interactive/functions-comparisons.html Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------