|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
|
Query with varchar not using functional index
Hello,
I've got a very strange problem that I'm seeing in one of our PostgreSQL databases (7.4.19). Specifically, I have a query that only uses the functional index that it's supposed to use if I cast to text. Here is a slimmed down version of the table definition: Column | Type | Modifiers ++ case_id | character varying(50) | not null case_public_id | character varying(50) | Indexes: "case_data_case_id" unique, btree (case_id) "case_data_squish_public_id" btree (squish((case_public_id)::text)) Here is the query that DES NT work: dev=# explain select * from case_data where squish(case_public_id) like '84D04-0806-SC-06491'; QUERY PLAN Seq Scan on case_data (cost=0.00334288.23 rows=40730 width=228) Filter: (upper(translate((case_public_id)::text, '- \011'::text, ''::text)) ~~ '84D04-0806-SC-06491'::text) And here is the query that DES work: dev=# explain select * from case_data where squish(case_public_id::text) like '84D04-0806-SC-06491'; QUERY PLAN Index Scan using case_data_squish_public_id on case_data (cost=0.00148228.78 rows=40730 width=228) Index Cond: (upper(translate((case_public_id)::text, '- \011'::text, ''::text)) = '84D04-0806-SC-06491'::text) Filter: (upper(translate((case_public_id)::text, '- \011'::text, ''::text)) ~~ '84D04-0806-SC-06491'::text) And here is the definition of the squish() function: dev=# \df+ squish List of functions Result data type | Schema | Name | Argument data types | | Language | Source code | Description text | public | squish | text | postgres | sql | SELECT upper(translate($1,'- ','')); | I think I may see what's going on. The function definition has an argument data type of 'text', so I guess that's why PostgreSQL wants me to cast to 'text'. But what is confusing me is the exact same setup works as expected on our 7.4.7 database server (the problem server is 7.4.19). Has something changed that would cause this behavior since 7.4.7? Also, why is PostgreSQL requiring an explicit cast to 'text'? I thought varchar and text were functionally identical data types. Thank you so much for your help. PostgreSQL is a phenomenal product. Ryan VanMiddlesworth |
![]() |
| Viewing: Web Development Archives > Mailing Lists > Databases > Query with varchar not using functional index |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|