index used when casting to different type?

adb <adb@Beast.COM> writes:

If I have a table with a key column stored as text and
another table with a key column stored as an int4 and
I join the two by casting the in4 column to text, will the existing
index on the int4 key be used?

Nope. Indexes are associated with a particular datatype and only work
for comparison operators of that datatype. The index on the first
table's text column could possibly be used in this scenario, but not
the one on the int4 column.

regards, tom lane

Ah.. This explains the performance I was getting with rserv, I have
int4 keys on my replicated tables and _rserv_log_ uses a text column
to be generic and support keys of different types. Problem is the
replicate function does a join from the text column to my key cast
as text and thus after running for a little bit, slows to a crawl.

Adding explicit text indexes on my int key columns seems to have
fixed the problem.

Thanks,

Alex.

On Mon, 19 Feb 2001, Tom Lane wrote:

* adb <adb@Beast.COM> [010219 20:43]:

If I have a table with a key column stored as text and
another table with a key column stored as an int4 and
I join the two by casting the in4 column to text, will the existing
index on the int4 key be used?

I doubt it.

Thanks,

Alex.

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

adb <adb@beast.com> writes:

Ah.. This explains the performance I was getting with rserv, I have
int4 keys on my replicated tables and _rserv_log_ uses a text column
to be generic and support keys of different types. Problem is the
replicate function does a join from the text column to my key cast
as text and thus after running for a little bit, slows to a crawl.

Adding explicit text indexes on my int key columns seems to have
fixed the problem.

Hmm. Sounds like room exists for improvement in rserv here ...

regards, tom lane