[postgis-users] Very large WHERE clause?

[Ronald Müller]

Keep it simple! Did you try joining tables?

regards

ronald



Am 07.05.2012 16:26, schrieb 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.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users