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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
