Databases
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
Go Back   Web Development Archives Mailing Lists Databases

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Web Development Archives Sponsor:
  #1  
Old June 26th, 2008, 02:51 PM
Ryan VanMiddlesworth
Guest
Dev Archives Newbie (0 - 499 posts)
 
Posts: n/a  
Time spent in forums:
Reputation Power:
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

Reply With Quote
Reply

Viewing: Web Development Archives Mailing Lists Databases > Query with varchar not using functional index


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway
Stay green...Green IT