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@sss.pgh.pa.us>
To: "Christian Fritze" <The.Finn@sprawl.de>
Cc: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>;
<pgsql-general@postgresql.org>
Sent: Tuesday, April 17, 2001 10:33 AM
Subject: Re: [GENERAL] index not used with subselect in where clause ?


> Christian Fritze <The.Finn@sprawl.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
>