Skip site navigation (1) Skip section navigation (2)

Re: Using index from sub-query

From: "Mike Mascari" <mascarm(at)mascari(dot)com>
To: "pdaly" <petedaly(at)ix(dot)netcom(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Using index from sub-query
Date: 2000-08-28 15:48:24
Message-ID: 200008281548.LAA21340@corvette.mascari.com (view raw or flat)
Thread:
Lists: pgsql-general
> From: pdaly <petedaly(at)ix(dot)netcom(dot)com>
> As has been talked about recently, I have a int8 field, which when
searched for
> 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
index
> table1.  It will only use the index when the value to be matched is in
quotes.
> How can I force it to use the index, or create a new index which would be
used.
> 
> This causes a HUGE performance hit.  (30-45 seconds, as opposed to less
than 1
> second.)

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
WHERE EXISTS 
(SELECT namelookup.id FROM namelookup WHERE
 namelookup.id = table1.id AND name = '$tname');

Hope that helps,

Mike Mascari


pgsql-general by date

Next:From: Anthony MarinoDate: 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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group