Thread: Partial indexes on VARCHAR get double converted from text

create table stately ( id int, filename varchar(255), state varchar(255) );

create index on stately(state) where state in ( 'pending', 'waiting',
'done' );

\d stately
            Table "public.stately"
  Column  |          Type          | Modifiers
----------+------------------------+-----------
 id       | integer                |
 filename | character varying(255) |
 state    | character varying(255) |
Indexes:
    "stately_state_idx" btree (state) WHERE state::text = ANY
(ARRAY['pending'::character varying, 'waiting'::character varying,
'done'::character varying]::text[])

There is no combination of typecasting which will prevent this outcome.
 I've tried.

Further, it seems to me from testing that this double type conversion
affects the planner's costing of the index, causing it to not want to
use the index.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Josh Berkus <josh@agliodbs.com> writes:
> create table stately ( id int, filename varchar(255), state varchar(255) );
> create index on stately(state) where state in ( 'pending', 'waiting',
> 'done' );

> \d stately
>             Table "public.stately"
>   Column  |          Type          | Modifiers
> ----------+------------------------+-----------
>  id       | integer                |
>  filename | character varying(255) |
>  state    | character varying(255) |
> Indexes:
>     "stately_state_idx" btree (state) WHERE state::text = ANY
> (ARRAY['pending'::character varying, 'waiting'::character varying,
> 'done'::character varying]::text[])

I see no bug here.  It's doing what's expected.

> Further, it seems to me from testing that this double type conversion
> affects the planner's costing of the index, causing it to not want to
> use the index.

That's not a bug report, that's just unsupported speculation.  Moreover,
if there were any such effect, it would cause the planner to reject the
index entirely, not just penalize it cost-wise.

            regards, tom lane

On 01/16/2015 02:52 PM, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> create table stately ( id int, filename varchar(255), state varchar(255) );
>> create index on stately(state) where state in ( 'pending', 'waiting',
>> 'done' );
>
>> \d stately
>>             Table "public.stately"
>>   Column  |          Type          | Modifiers
>> ----------+------------------------+-----------
>>  id       | integer                |
>>  filename | character varying(255) |
>>  state    | character varying(255) |
>> Indexes:
>>     "stately_state_idx" btree (state) WHERE state::text = ANY
>> (ARRAY['pending'::character varying, 'waiting'::character varying,
>> 'done'::character varying]::text[])
>
> I see no bug here.  It's doing what's expected.

Why have the multiple conversions to and from TEXT?  It's certainly
visually confusing to users.

>
>> Further, it seems to me from testing that this double type conversion
>> affects the planner's costing of the index, causing it to not want to
>> use the index.
>
> That's not a bug report, that's just unsupported speculation.  Moreover,
> if there were any such effect, it would cause the planner to reject the
> index entirely, not just penalize it cost-wise.

I thought I had a test case which showed different costing for varchar
vs. text, but the results are more confusing than that.  Will delve further.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com