[postgis-users] Very large WHERE clause?

[DrYSG]

Sounds good to me. the four columns that are being searched (producer,
creator, distributor, type) each have about 10 different values, and are
indexed as a B-Tree. But the size of the table (and there is only one table
is 20M records) so it will be interesting to see the results.

I did get the following idea from someone in a different list, but he has
the same issue I have, I am more a software person than a DB person, so I
suspect that indexing the bitfield value may lead to performance issues:

---


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; 


--
View this message in context: http://postgis.17.n6.nabble.com/Very-large-WHERE-clause-tp4952906p4957891.html
Sent from the PostGIS - User mailing list archive at Nabble.com.