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
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 |