Re: LIKE foo% optimization easily defeated by OR?

Hi, Greg!

On Thu, Jan 4, 2018 at 12:57 AM, Greg Stark <stark(at)mit(dot)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