Re: index not used with subselect in where clause ?

Adding a LIMIT 1 in the subplan may also help -- as you only need a
single match to make it true so additional finds are useless -- it'll
stop sooner or will be more likely to use an index than a full table
scan.
--
Rod Taylor

There are always four sides to every story: your side, their side, the
truth, and what really happened.
----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Christian Fritze" <The(dot)Finn(at)sprawl(dot)de>
Cc: "Stephan Szabo" <sszabo(at)megazone23(dot)bigpanda(dot)com>;
<pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, April 17, 2001 10:33 AM
Subject: Re: [GENERAL] index not used with subselect in where clause ?

> Christian Fritze <The(dot)Finn(at)sprawl(dot)de> writes:
> > explain select * from allmain where exists (select distinct
> > dokids_as_int from allslwfull where dokids_as_int = idn and
> > wort_nouml_lower like 'gen%')
>
> Try dropping the "distinct" on the inner select. As a moment's
thought
> will reveal, it's not buying you anything; and it's costing you sort
> and unique passes over the subplan result.
>
> regards, tom lane
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>