BUG #5126: convert_to preventing index scan - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5126
Logged by:          Roman Kapusta
Email address:      roman.kapusta@gmail.com
PostgreSQL version: 8.3.8-1
Operating system:   fedora 11 i586 (32bit)
Description:        convert_to preventing index scan
Details:

I have table with bytea column, which is indexed (1)
I want to use index during pattern matching (eg. dir like someDirectoryName
|| '/%'), but concatenation of two strings cause error (2)
So I have to use function convert_to (converting text to bytea), but this
has awful explain plan (3)
If I rewrite string concatenation to just one string (4) query plan is
optimal
I found workaround (5), but still it looks like convert_to is causing full
table scan where it should not


(1) # \d paths
Table "paths"
    Column     |            Type             |                     Modifiers

---------------+-----------------------------+------------------------------
----------------------
 dev_id        | bigint                      | not null
 valid_to      | bigint                      | not null default
9223372036854775807::bigint
 name          | character varying(300)      | not null
 dir           | bytea                       | not null
Indexes:
    "paths_dev_id_key" UNIQUE, btree (dev_id, dir, name, valid_to)


(2) # explain select * from paths p where p.dir like E'Multimedia/Videos' ||
E'/%' and p.dev_id = 14 and p.valid_to >= 486629;
ERROR:  operator does not exist: bytea ~~ text
LINE 1: explain select * from paths p where p.dir like E'Multimedia/...
                                                  ^
HINT:  No operator matches the given name and argument type(s). You might
need to add explicit type casts.


(3) # explain select * from paths p where p.dir like
convert_to(E'Multimedia/Videos' || E'/%', 'UTF8') and p.dev_id = 14 and
p.valid_to >= 486629;
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------------
 Seq Scan on paths p  (cost=0.00..212065.54 rows=1 width=333)
   Filter: ((valid_to >= 486629) AND (dev_id = 14) AND (dir ~~
convert_to('Multimedia/Videos/%'::text, 'UTF8'::name)))



(4) # explain select * from paths p where p.dir like E'Multimedia/Videos/%'
and p.dev_id = 14 and p.valid_to >= 486629;
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------------------------------
 Index Scan using paths_dev_id_key on paths p  (cost=0.00..10.02 rows=1
width=333)
   Index Cond: ((dev_id = 14) AND (dir >= 'Multimedia/Videos/'::bytea) AND
(dir < 'Multimedia/Videos0'::bytea) AND (valid_to >= 486629))
   Filter: (dir ~~ 'Multimedia/Videos/%'::bytea)


(5) # explain select * from paths p where p.dir like
E'Multimedia/Videos'::bytea || E'/%'::bytea and p.dev_id = 14 and p.valid_to
>= 486629;
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------------------------------
 Index Scan using paths_dev_id_key on paths p  (cost=0.00..10.02 rows=1
width=333)
   Index Cond: ((dev_id = 14) AND (dir >= 'Multimedia/Videos/'::bytea) AND
(dir < 'Multimedia/Videos0'::bytea) AND (valid_to >= 486629))
   Filter: (dir ~~ 'Multimedia/Videos/%'::bytea)