Re: Partial indexes on VARCHAR get double converted from text

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