RE: RE: Can't get Postgres to use indices

Tom,
The ep_tbl_cache table is a temp table where temporary data of many
different types can be stored, so I don't have the option of making it an
int. However, what I did do is that I changed the join clause to
'int8(x1)=a1.pid' and that took the query from 5-minute region to below two
seconds! Thanks for the help!

-Othman

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, May 03, 2001 9:48 PM
To: Othman Laraki
Cc: Mitch Vincent; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] RE: Can't get Postgres to use indices

"Othman Laraki" <othman(at)epitrope(dot)com> writes:
> THE QUERY
> =========

> SELECT a1.tid, x2 FROM ep_tbl_cache, ep_tbl_page_topic_map as a1 WHERE
> x1=a1.pid AND x1 is not null and x1 <> '' and x1 <> '-' AND
> ep_tbl_cache.identifier = 'bg2hyr0p51_cached_Thu May 03 13:43:07 PDT
2001';

The only available join clause here is x1=a1.pid. Unfortunately,
x1 is VARCHAR(100) and a1.pid is INT8. To get a more reasonable
join plan, try fixing your table declarations so that the join
clause doesn't involve a forced type conversion.

regards, tom lane