[postgis-users] Very large WHERE clause?

[Stephen Woodbridge]

On 5/4/2012 2:34 PM, 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).
>
> Suggestions?

I can't speak to the planner and I'm guessing that it has a lot to do 
with what indexes are available and the statistics for any given column, 
but there are some other syntax that you might consider:

type='JPG' OR type='PNG'

could be represented as:

type in ('JPG','PNG')
type ~ '^(JPG|PNG)$'   -- case sensitive regex
type ~* '^(JPG|PNG)$'  -- case insensitive regex

You need to play with the syntax and EXPLAIN in your specific query for 
example is you have a small number of options then:

type='JPG' OR type='PNG'

is faster than

type in ('JPG','PNG')
type ~ '^(JPG|PNG)$'   -- case sensitive regex
type ~* '^(JPG|PNG)$'  -- case insensitive regex

but if you have a very large number of options in your list then the 
later is probably faster. An if you are using IN (...), I think it helps 
if the list is in sorted order.

-Steve