Re: Partial indexes on VARCHAR get double converted from text

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