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@sss.pgh.pa.us]
Sent: Thursday, May 03, 2001 9:48 PM
To: Othman Laraki
Cc: Mitch Vincent; pgsql-general@postgresql.org
Subject: Re: [GENERAL] RE: Can't get Postgres to use indices


"Othman Laraki" <othman@epitrope.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