LIKE foo% optimization easily defeated by OR?

Поиск
Список
Период
Сортировка
От Greg Stark
Тема LIKE foo% optimization easily defeated by OR?
Дата
Msg-id CAM-w4HMX4P=wCi-3C8MQYSj7RGPDPwgBZ6Y7j7mYtuvZBV22uA@mail.gmail.com
обсуждение исходный текст
Ответы Re: LIKE foo% optimization easily defeated by OR?
Список pgsql-hackers
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


В списке pgsql-hackers по дате отправления:

Предыдущее
От: Remi Colinet
Дата:
Сообщение: [Patch v2] Make block and file size for WAL and relations defined atcluster creation
Следующее
От: ilmari@ilmari.org (Dagfinn Ilmari Mannsåker)
Дата:
Сообщение: Re: [PATCH] Comment typo in get_collation_name() comment