When the WHERE clause includes a sub query the query plan seems to ignore
indexes.
See the examples below. 
Table R1684 has one column, stockno, which is the same type as the stockno
in the books_fti table. There is no index on R1684.
In the first case the index on books_fti(stockno) is not used but in the
second case it is.

 

=============================== Query 1
======================================= 
explain select * from books_fti where stockno in (select stockno from R1684);

Seq Scan on books_fti  (cost=79300.27 rows=1024705 width=160)
  SubPlan
    ->  Seq Scan on r1684  (cost=43.00 rows=1000 width=12) 

================================ Query 2
=======================================
 explain select * from books_fti where stockno in
('0815171161','1857281012','0419251901');

Index Scan using allbooks_isbn, allbooks_isbn, allbooks_isbn on books_fti
(cost
=6.15 rows=5 width=160)
                         
-- 
thorNET  - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax:   01454 854412
http://www.thornet.co.uk 

Tue, 08 Aug 2000 12:22:12 +0100
Steve Heaven <steve@thornet.co.uk>
Query plan and sub-queries
Steve Heaven wrote:
> 
> When the WHERE clause includes a sub query the query plan seems to ignore
> indexes.

This is a FAQ:

4.23) Why are my subqueries using IN so slow?

Currently, we join subqueries to outer queries by sequential
scanning the result of the subquery for each row of the outer
query. A workaround is to replace IN with EXISTS: 

        SELECT *
        FROM tab
        WHERE col1 IN (SELECT col2 FROM TAB2)

to: 

        SELECT *
        FROM tab
        WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2)

We hope to fix this limitation in a future release. 

Hope that helps, 

Mike Mascari

Tue, 08 Aug 2000 08:24:28 -0400
Mike Mascari <mascarm@mascari.com>
Re: Query plan and sub-queries
At 08:24 08/08/00 -0400, you wrote:
> A workaround is to replace IN with EXISTS: 

This still does a sequential rather that indexed scan:

explain select * from books_fti where exists 
      (select R1684.stockno from R1684,books_fti where
R1684.stockno=books_fti.stockno ); 

Result  (cost=79300.27 rows=0 width=0)
  InitPlan
    ->  Nested Loop  (cost=2093.00 rows=1024706 width=24)
      ->  Seq Scan on r1684  (cost=43.00 rows=1000 width=12)
      ->  Index Scan using allbooks_isbn on books_fti  (cost=2.05
rows=1024705 width=12)
  ->  Seq Scan on books_fti  (cost=79300.27 rows=1024705 width=160)
                                                                    
-- 
thorNET  - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax:   01454 854412
http://www.thornet.co.uk 

Tue, 08 Aug 2000 13:47:34 +0100
Steve Heaven <steve@thornet.co.uk>
Re: Query plan and sub-queries
Steve Heaven wrote:
> 
> At 08:24 08/08/00 -0400, you wrote:
> > A workaround is to replace IN with EXISTS:
> 
> This still does a sequential rather that indexed scan:
> 
> explain select * from books_fti where exists
>       (select R1684.stockno from R1684,books_fti where
> R1684.stockno=books_fti.stockno );

Firstly, a simple join would yield the same results:

SELECT books_fti.* FROM books_fti, R1684 WHERE
books_fti.stockno = R1684.stockno;

Secondly, you've listed the target table twice in the above
query, which might be causing a problem with the planner.
Instead, it should read:

SELECT * FROM books_fti WHERE EXISTS (
 SELECT R1684.stockno FROM R1684 WHERE R1684.stockno =
books_fti.stockno
);

That should result in 1 sequential scan on one of the tables, and
1 index scan on the inner table. The plan should look something
like:

Seq Scan on R1684  (cost=9.44 rows=165 width=12)
  SubPlan
    ->  Index Scan using allbooks_isbn on books_fti  (cost=490.59
rows=7552 width=12)


Hope that helps, 

Mike Mascari

Tue, 08 Aug 2000 10:17:23 -0400
Mike Mascari <mascarm@mascari.com>
Re: Query plan and sub-queries
At 10:17 08/08/00 -0400, Mike Mascari wrote:
>
>Firstly, a simple join would yield the same results:
>
>SELECT books_fti.* FROM books_fti, R1684 WHERE
>books_fti.stockno = R1684.stockno;

Yes that gives me:
Nested Loop  (cost=2093.00 rows=1024706 width=172)
  ->  Seq Scan on r1689  (cost=43.00 rows=1000 width=12)
  ->  Index Scan using allbooks_isbn on books_fti  (cost=2.05 rows=1024705
width
=160)  


But the 'EXISTS' sub-query you suggest still doesnt use the index.

>SELECT * FROM books_fti WHERE EXISTS (
> SELECT R1684.stockno FROM R1684 WHERE R1684.stockno =
>books_fti.stockno
>);
>
>That should result in 1 sequential scan on one of the tables, and
>1 index scan on the inner table. The plan should look something
>like:
>
>Seq Scan on R1684  (cost=9.44 rows=165 width=12)
>  SubPlan
>    ->  Index Scan using allbooks_isbn on books_fti  (cost=490.59
>rows=7552 width=12)
>

No actually I'm getting:
Seq Scan on books_fti  (cost=79300.27 rows=1024705 width=160)
  SubPlan
    ->  Seq Scan on r1684  (cost=43.00 rows=2 width=12)     
-- 
thorNET  - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax:   01454 854412
http://www.thornet.co.uk 

Tue, 08 Aug 2000 15:36:51 +0100
Steve Heaven <steve@thornet.co.uk>
Re: Query plan and sub-queries
Has something happened to the list server ?

I am only subscribed to the general list, but after two days of nothing I'm
now getting the hackers list stuff.

Steve


-- 
thorNET  - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax:   01454 854412
http://www.thornet.co.uk 

Thu, 14 Sep 2000 08:59:53 +0100
Steve Heaven <steve@thornet.co.uk>
List funnies ?
> Has something happened to the list server ?
> 
> I am only subscribed to the general list, but after two days of nothing I'm
> now getting the hackers list stuff.
> 
So it's not just me?

How sad, I was hoping I had be promoted to Hacker status... ;-)


/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Fabrizio Ermini               Alternate E-mail:
C.so Umberto, 7               faermini@tin.it
loc. Meleto Valdarno          Mail on GSM: (keep it short!)
52020 Cavriglia (AR)          faermini@sms.tin.it

Thu, 14 Sep 2000 12:26:56 +0200
fabrizio.ermini@sysdat.it
Re: List funnies ?

okay, this is most odd ... according to the list software, you are still
only subscribed to the general list:

  Address: fabrizio.ermini@sysdat.it
    Address is valid.
    Address is registered as:
      fabrizio.ermini@sysdat.it
    Registered at Fri Sep  1 15:33:13 2000 GMT.
    Registration data last changed at Fri Sep  1 15:33:13 2000 GMT.
    Address is subscribed to 1 list:
      pgsql-general:
        Subscribed at Fri Sep  1 15:33:13 2000 GMT.
        Receiving each message as it is posted.
        Subscriber flags:
          noeliminatecc
          nohide
          prefix
          replyto
          selfcopy
          norewritefrom
          noackstall
          noackdeny
          noackpost
          noackreject
        Data last changed at Fri Sep  1 15:33:13 2000 GMT.

can you forward me a copy of the next 'hackers' message you receive, along
with its *full* headers?  Just to make sure, pgsql-general@postgresql.org
hasn't been inadvertently subscribed to hackers, so we aren't getting a
cross there:

Majordomo>show pgsql-general@postgresql.org

  Address: pgsql-general@postgresql.org
    Address is valid.
    Address is not registered.



On Thu, 14 Sep 2000 fabrizio.ermini@sysdat.it wrote:

> > Has something happened to the list server ?
> > 
> > I am only subscribed to the general list, but after two days of nothing I'm
> > now getting the hackers list stuff.
> > 
> So it's not just me?
> 
> How sad, I was hoping I had be promoted to Hacker status... ;-)
> 
> 
> /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/
> 
> Fabrizio Ermini               Alternate E-mail:
> C.so Umberto, 7               faermini@tin.it
> loc. Meleto Valdarno          Mail on GSM: (keep it short!)
> 52020 Cavriglia (AR)          faermini@sms.tin.it
> 
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Thu, 14 Sep 2000 09:01:50 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: List funnies ?
When last we left our intrepid adventurers...

 > Has something happened to the list server ?
 >
 > I am only subscribed to the general list, but after two days of nothing I'm
 > now getting the hackers list stuff.
 >
 >So it's not just me?

It's not just you... this morning, I was surprised that my filters hadn't 
filtered the pgsql-hackers messages to another folder, when I realized, 
HEY!  I'm not ON the hackers list...

So for lack of anything better to to, I unsubbed, and got a return message 
that it was successful.  This, in spite of the fact that I'd never 
subscribed.  Hmmm...

Now, back to our regularly scheduled programming.

David Veatch - dvicci@reckoning.org

"Many people would sooner die than think.
In fact, they do." - Bertrand Russell


Thu, 14 Sep 2000 07:31:32 -0500
David Veatch <dveatch@sunflower.com>
Re: List funnies ?
On Thu, Sep 14, 2000 at 09:01:50AM -0300, The Hermit Hacker wrote:
> 
> okay, this is most odd ... according to the list software, you are still
> only subscribed to the general list:

Marc

I can also confirm that I had no message on pgsql-general for about
two days until the thread 'List Funnies' started.  Some -general has
been vanishing into a black hole. (Including one message I know a
friend of mine, 'Richard Poole <richard.poole@vi.net>' sent recently).

Jules

Thu, 14 Sep 2000 13:37:02 +0100
Jules Bean <jules@jellybean.co.uk>
Re: List funnies ?

there was a problem with database corruption in pgsql-general that we
fixed last night ... if anyone else is interested in helping, I'm going to
be working with the Mj2 guys on moving the backend from BerkeleyDB ->
PostgreSQL ... if anyone is interested in helping out, let me know ...

On Thu, 14 Sep 2000, Jules Bean wrote:

> On Thu, Sep 14, 2000 at 09:01:50AM -0300, The Hermit Hacker wrote:
> > 
> > okay, this is most odd ... according to the list software, you are still
> > only subscribed to the general list:
> 
> Marc
> 
> I can also confirm that I had no message on pgsql-general for about
> two days until the thread 'List Funnies' started.  Some -general has
> been vanishing into a black hole. (Including one message I know a
> friend of mine, 'Richard Poole <richard.poole@vi.net>' sent recently).
> 
> Jules
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Thu, 14 Sep 2000 10:02:45 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: List funnies ?