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

I'm not sure if I understand you correctly here: the subplan uses an
index scan already. It's the seq. scan in the outer query that makes
me whine.


> There are always four sides to every story: your side, their side, the
> truth, and what really happened.

Off Topic: I like that .sig, thoughts of "The man who shot Liberty Valance"
come to mind... :-)


> From: "Tom Lane" <tgl@sss.pgh.pa.us>
[...]
> > 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

Yep, that increases performance...
...by about 0.35 % according to EXPLAIN :-{

Well, what I'm doing right now is the following:

I perform the inner query (which is reasonably fast) and pump the result
through the JDBC driver into my application. There I build the outer query
with an explicit list of integers for the WHERE clause and hand that query
back to the data base.

But that doesn't seem very smart either: in cases where the inner query
returns only a few results it's not really necessary. In cases where it
returns a few thousands, I need to split the outer query in order not to
run into a 'query too long' error (which comes from the jdbc driver rather
than from pgsql if I'm right? Maybe I should try to tweak that driver? Hmmm...)
That splitting however eats away much (if not all) of the intended
performance gain.

greetings...
Christian


--
"The sky above the port was the color of television,
 tuned to a dead channel."
                                         -- W.G. --