LIKE foo% optimization easily defeated by OR?

From: Greg Stark <stark(at)mit(dot)edu>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: LIKE foo% optimization easily defeated by OR?
Date: 2018-01-03 21:57:01
Message-ID: CAM-w4HMX4P=wCi-3C8MQYSj7RGPDPwgBZ6Y7j7mYtuvZBV22uA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dagfinn Ilmari =?utf-8?Q?Manns=C3=A5ker?= 2018-01-03 21:58:27 Re: [PATCH] Comment typo in get_collation_name() comment
Previous Message Remi Colinet 2018-01-03 21:45:36 [Patch v2] Make block and file size for WAL and relations defined at cluster creation