LIKE foo% optimization easily defeated by OR?
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
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
On 3 January 2018 at 22:34, Alexander Korotkov
<a.korotkov@postgrespro.ru> wrote:
I've checked similar case on database with PostgreSQL mailing lists. It
works for me.
Wow that's fascinating. I wonder why it's not kicking in for me. I
have checked with enable_seqscan=off but I'll have to do some more
investigations. I'll try it on different instances of the database.
--
greg
Greg Stark <stark@mit.edu> writes:
On 3 January 2018 at 22:34, Alexander Korotkov
<a.korotkov@postgrespro.ru> wrote:I've checked similar case on database with PostgreSQL mailing lists. It
works for me.
Wow that's fascinating. I wonder why it's not kicking in for me.
text vs varchar maybe?
regards, tom lane
I think I found the bug 18" from the monitor.... I'll just be over
here with the paper bag over my head mumbling about running RESET ALL
before running tests...
On Thu, Jan 4, 2018 at 2:06 AM, Greg Stark <stark@mit.edu> wrote:
I think I found the bug 18" from the monitor.... I'll just be over
here with the paper bag over my head mumbling about running RESET ALL
before running tests...
It's no problem. That sometimes happens to everybody.
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
On 04/01/18 12:06, Greg Stark wrote:
I think I found the bug 18" from the monitor.... I'll just be over
here with the paper bag over my head mumbling about running RESET ALL
before running tests...
I think Linus has patented the use of a paper bag in your situation...
So you might have to pay royalties!!!
Cheers,
Gavin