> From: pdaly <petedaly(at)ix(dot)netcom(dot)com>
> As has been talked about recently, I have a int8 field, which when
> as 'value' will use the index, but as value, with the quotes will not.
> I subquery to get a list of the values to look up:
> SELECT [fields]
> FROM table1
> WHERE id in (
> select distinct id from namelookup where name = '$tname'
> When I do an explain on this, it is doing a table scan, and not using the
> table1. It will only use the index when the value to be matched is in
> How can I force it to use the index, or create a new index which would be
> This causes a HUGE performance hit. (30-45 seconds, as opposed to less
Unfortunately, PostgreSQL doesn't use indexes with IN clauses.
Its on the list of things TODO though. At the moment, you have
to write the query using EXISTS instead:
SELECT table1.[fields] FROM table1
(SELECT namelookup.id FROM namelookup WHERE
namelookup.id = table1.id AND name = '$tname');
Hope that helps,
pgsql-general by date
|Next:||From: Anthony Marino||Date: 2000-08-28 16:01:34|
|Subject: java.sql.SQLException: ERROR:|
|Previous:||From: Barnes, Sandy (Sandra)||Date: 2000-08-28 15:26:38|
|Subject: lo_creat() failing after 22 iterations|