Query with varchar not using functional index

Started by Ryan VanMiddlesworthalmost 18 years ago2 messagesgeneral
Jump to latest
#1Ryan VanMiddlesworth
ryanv+postgresql@ontko.com

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 DOES NOT 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.00..334288.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 DOES 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.00..148228.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 | Owner |
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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ryan VanMiddlesworth (#1)
Re: Query with varchar not using functional index

"Ryan VanMiddlesworth" <ryanv+postgresql@ontko.com> writes:

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.

Yeah, 7.4 is not very bright about realizing that these cases are
equivalent and so it could match the query to the index either way.
(8.0 and up get this right.) I think about all you can do in 7.4 is
create the index to match the case you prefer to use, either with
or without an explicit cast there.

... 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).

I suspect that the index was created with an explicit cast on one
server, and not on the other. \d shows the two cases alike so it's
hard to tell for sure, though if you want proof you could look into
the expression strings in pg_index.

Also, why is PostgreSQL
requiring an explicit cast to 'text'? I thought varchar and text were
functionally identical data types.

The cast is in fact a no-op (it's just a "RelabelType" node), but it's
still got to be represented in the expression tree because some
functions examine the declared type of their input expressions.
\d (and also pg_dump) show implicit casts explicitly in certain contexts
to be sure that the expression is interpreted the same way on reload,
and not matched to some other function with the same name and different
argument types.

regards, tom lane