Our database has a query that looks like this -- note the OR between a
simple equality qual and a LIKE qual:
=> explain SELECT 1 AS one FROM "redirect_routes" WHERE
redirect_routes.path = 'foobar' OR redirect_routes.path LIKE
'foobar/%';
QUERY PLAN
-----------------------------------------------------------------------------------
Seq Scan on redirect_routes (cost=0.00..1776.23 rows=5 width=4)
Filter: (((path)::text = 'foobar'::text) OR ((path)::text ~~
'foobar/%'::text))
(2 rows)
The database uses a sequential scan even though both of the sides of
that OR have valid indexes that can satisfy them (and for much lower
costs):
=> explain SELECT 1 AS one FROM "redirect_routes" WHERE
redirect_routes.path = 'foobar' ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Only Scan using index_redirect_routes_on_path_text_pattern_ops
on redirect_routes (cost=0.41..4.43 rows=1 width=4)
Index Cond: (path = 'foobar'::text)
(2 rows)
=> explain SELECT 1 AS one FROM "redirect_routes" WHERE
redirect_routes.path LIKE 'foobar/%';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Only Scan using index_redirect_routes_on_path_text_pattern_ops
on redirect_routes (cost=0.41..4.44 rows=4 width=4)
Index Cond: ((path ~>=~ 'foobar/'::text) AND (path ~<~ 'foobar0'::text))
Filter: ((path)::text ~~ 'foobar/%'::text)
(3 rows)
I'm guessing the LIKE optimization isn't clever enough to kick in when
it's buried under an OR? Does it only kick in at the top level of the
quals?
--
greg
