Re: Partial indexes on VARCHAR get double converted from text

On 01/16/2015 02:52 PM, Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)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