The following bug has been logged online:
Bug reference: 5126
Logged by: Roman Kapusta
Email address: roman.kapu...@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)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
