Re: index not used with subselect in where clause ?

Hello and thank you for your quick reply...

> > But when I try doing a
> >
> >   SELECT attr1 FROM table1 WHERE attr1 IN (<SUBSELECT returning list of ints>)
> >                              AND <more conditions>;
> >
> > then the SELECT on table1 uses a sequential scan running 'endlessly'.
>
> >From the FAQ:
>
> 4.23) Why are my subqueries using IN so slow?

OOOPS, I confess that I didn't think of revisiting the source tree
after installing the complete HTML docs. I don't think the FAQ is
in there, is it? Maybe this would be helpful...

Anyway the solution from the FAQ doesn't seem to help me.
Maybe I wasn't clear enough on what I'm trying to do, so here is a
'real world' example:


1. That's my situation now:

   finn@chatsubo:~ > /Projekte/ENDEBIT/packages/pgsql-Test/bin/psql -p 7432
   zewtest_7.1 -c "explain select * from allmain where idn in (select distinct
   dokids_as_int from allslwfull where wort_nouml_lower like 'gen%')"
   NOTICE:  QUERY PLAN:

   Seq Scan on allmain  (cost=0.00..69328.08 rows=19619 width=556)
     SubPlan
       ->  Materialize  (cost=3.45..3.45 rows=1 width=4)
             ->  Unique  (cost=3.45..3.45 rows=1 width=4)
                   ->  Sort  (cost=3.45..3.45 rows=1 width=4)
                         ->  Index Scan using allslwfull_low_idx on allslwfull

   (cost=0.00..3.44 rows=1 width=4)

   EXPLAIN


2. FAQ 4.23 now seems to suggest something like

   finn@chatsubo:~ > /Projekte/ENDEBIT/packages/pgsql-Test/bin/psql -p 7432
   zewtest_7.1 -c "explain select * from allmain where exists (select distinct
   dokids_as_int from allslwfull where dokids_as_int = idn and
wort_nouml_lower
   like 'gen%')"
   NOTICE:  QUERY PLAN:

   Seq Scan on allmain  (cost=0.00..69328.08 rows=19619 width=556)
     SubPlan
       ->  Unique  (cost=3.45..3.46 rows=1 width=4)
             ->  Sort  (cost=3.45..3.45 rows=1 width=4)
                   ->  Index Scan using allslwfull_low_idx on allslwfull
   (cost=0.00..3.44 rows=1 width=4)

   EXPLAIN

Doesn't seem to make much of a difference... ;-)


3. On the other hand:

   finn@chatsubo:~ > /Projekte/ENDEBIT/packages/pgsql-Test/bin/psql -p 7432
   zewtest_7.1 -c "explain select distinct dokids_as_int from allslwfull where
   wort_nouml_lower like 'gen%'"
   NOTICE:  QUERY PLAN:

   Unique  (cost=3.45..3.45 rows=1 width=4)
     ->  Sort  (cost=3.45..3.45 rows=1 width=4)
           ->  Index Scan using allslwfull_low_idx on allslwfull
   (cost=0.00..3.44 rows=1 width=4)

   EXPLAIN
   finn@chatsubo:~ > /Projekte/ENDEBIT/packages/pgsql-Test/bin/psql -p 7432
   zewtest_7.1 -c "explain select * from allmain where idn in (677676, 34487,
   45353)"
   NOTICE:  QUERY PLAN:

   Index Scan using allmainidn_idx, allmainidn_idx, allmainidn_idx on allmain
   (cost=0.00..10.44 rows=1 width=556)

   EXPLAIN

Being far from knowlegeable in terms of database theory and looking just
at the figures returned by EXPLAIN I wondered if it wouldn't be much
faster to simply evaluate the inner query and hand the result over to
the outer query instead of performing an expensive join behind the scenes...

Am I thinking too naively here?


greetings...
Christian


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