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(at)sss(dot)pgh(dot)pa(dot)us>
[...]
> > 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

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. --