Skip site navigation (1) Skip section navigation (2)

BUG #5126: convert_to preventing index scan

From: "Roman Kapusta" <roman(dot)kapusta(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5126: convert_to preventing index scan
Date: 2009-10-19 11:05:22
Message-ID: 200910191105.n9JB5MJQ033895@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5126
Logged by:          Roman Kapusta
Email address:      roman(dot)kapusta(at)gmail(dot)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)

Responses

pgsql-bugs by date

Next:From: Dave PageDate: 2009-10-19 13:49:03
Subject: Re: Re: BUG #5065: pg_ctl start fails as administrator, with "could not locate matching postgres executable"
Previous:From: Robert HaasDate: 2009-10-17 11:00:37
Subject: Re: BUG #5098: Levenshtein with costs is broken

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group