Обсуждение: index used when casting to different type?
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? Thanks, Alex.
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> 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 >
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
* 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