Re: LIKE foo% optimization easily defeated by OR?

Поиск
Список
Период
Сортировка
От Alexander Korotkov
Тема Re: LIKE foo% optimization easily defeated by OR?
Дата
Msg-id CAPpHfdus0ZrviDqTymXnOFV6iyaQd0trLekLTvEJZQMQgUu3gg@mail.gmail.com
обсуждение исходный текст
Ответ на LIKE foo% optimization easily defeated by OR?  (Greg Stark <stark@mit.edu>)
Ответы Re: LIKE foo% optimization easily defeated by OR?
Список pgsql-hackers
Hi, Greg!

On Thu, Jan 4, 2018 at 12:57 AM, Greg Stark <stark@mit.edu> wrote:
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?

I've checked similar case on database with PostgreSQL mailing lists.  It works for me.

# explain select * from messages where level = 1 or author like 'Greg%';
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Bitmap Heap Scan on messages  (cost=80.01..5967.43 rows=3933 width=1343)
   Recheck Cond: ((level = 1) OR (author ~~ 'Greg%'::text))
   Filter: ((level = 1) OR (author ~~ 'Greg%'::text))
   ->  BitmapOr  (cost=80.01..80.01 rows=3897 width=0)
         ->  Bitmap Index Scan on messages_level_idx  (cost=0.00..73.17 rows=3851 width=0)
               Index Cond: (level = 1)
         ->  Bitmap Index Scan on messages_author_idx  (cost=0.00..4.87 rows=46 width=0)
               Index Cond: ((author ~>=~ 'Greg'::text) AND (author ~<~ 'Greh'::text))
(8 rows)

So, I think in principle optimizer is capable to handle such kind of queries (pgsql 9.6.6).
Did you try setting enable_seqscan = off?  Probably, something is wrong with costing in this case...

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 

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

Предыдущее
От: ilmari@ilmari.org (Dagfinn Ilmari Mannsåker)
Дата:
Сообщение: Re: [PATCH] Comment typo in get_collation_name() comment
Следующее
От: Greg Stark
Дата:
Сообщение: Re: LIKE foo% optimization easily defeated by OR?