From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Partial indexes on VARCHAR get double converted from text |
Date: | 2015-01-15 22:43:04 |
Message-ID: | 54B84278.2000800@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-01-16 01:52:18 | Re: Partial indexes on VARCHAR get double converted from text |
Previous Message | Peter Eisentraut | 2015-01-15 20:35:15 | Re: [BUGS] BUG #12070: hstore extension: hstore_to_json_loose produces invalid JSON |