X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 1CAFD9FB33B
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 31 Jul 2006 09:40:42 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 82728-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 31 Jul 2006 09:40:28 -0300 (ADT)
X-Greylist: delayed 00:25:28.973072 by SQLgrey-
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 579089FB363
	for <pgsql-performance@postgresql.org>;
	Mon, 31 Jul 2006 09:40:12 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from keymaster.look.ca (delta1.look.ca [207.136.80.9])
	by svr4.postgresql.org (Postfix) with ESMTP id 99DB15AFB38
	for <pgsql-performance@postgresql.org>;
	Mon, 31 Jul 2006 12:14:31 +0000 (GMT)
Received: from 216-154-21-111.dsl.look.ca ([216.154.21.111])
	by keymaster.look.ca with esmtp (Exim 4.20)
	id 1G7Wez-0003aC-RZ; Mon, 31 Jul 2006 12:14:13 +0000
From: Rod Taylor <rbt@rbt.ca>
To: H Hale <hhale21@rogers.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20060731015014.87910.qmail@web88005.mail.re2.yahoo.com>
References: <20060731015014.87910.qmail@web88005.mail.re2.yahoo.com>
Date: Mon, 31 Jul 2006 08:09:42 -0400
Message-Id: <1154347782.24186.278.camel@home>
Mime-Version: 1.0
X-Mailer: Evolution 2.6.2 FreeBSD GNOME Team Port 
X-SA-Exim-Mail-From: rbt@rbt.ca
Subject: Re: sub select performance due to seq scans
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-SA-Exim-Version: 3.1 (built Tue Feb 24 05:09:27 GMT 2004)
X-SA-Exim-Scanned: Yes
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=3.951 tagged_above=0 required=5
	tests=FORGED_RCVD_HELO, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL
X-Spam-Level: ***
X-Archive-Number: 200607/304
X-Sequence-Number: 20164

> capsa=# explain analyze select name from capsa.flatomfilesysentry
> where objectid in ( select dstobj from capsa.flatommemberrelation
> where srcobj = 'c1c7304a-1fe1-11db-8af7-001143214409');
> 
> 
>                                                            QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------
>  Nested Loop IN Join  (cost=0.00..1386.45 rows=5809 width=14) (actual
> time=2.933..101467.463 rows=5841 loops=1)
>    Join Filter: ("outer".objectid = "inner".dstobj)
>    ->  Seq Scan on flatomfilesysentry  (cost=0.00..368.09 rows=5809
> width=30) (actual time=0.007..23.451 rows=5844 loops=1)
>    ->  Seq Scan on flatommemberrelation  (cost=0.00..439.05 rows=5842
> width=16) (actual time=0.007..11.790 rows=2922 loops=5844)

A loop for an IN indicates that you are using a very old version of
PostgreSQL (7.2 or earlier).  Please double check that the server is
8.1.3 as you indicated and not just the client.

From psql:
        select version();

Hmm... Perhaps it is an 8.1.3 server with mergejoin and hashjoin
disabled?
        show enable_mergejoin;
        show enable_hashjoin;

You can try this query syntax:
        
        select name from capsa.flatomfilesysentry join
        capsa.flatommemberrelation on (objectid = dstobj) where srcobj =
        'c1c7304a-1fe1-11db-8af7-001143214409';


>          Filter: (srcobj =
> 'c1c7304a-1fe1-11db-8af7-001143214409'::capsa_sys.uuid)
>  Total runtime: 101482.256 ms
> (6 rows)
> 
> capsa=# select count(*) from capsa.flatommemberrelation ;
>  count
> -------
>  11932
> (1 row)
> 
> capsa=# select count(*) from capsa.flatomfilesysentry ;
>  count
> -------
>   5977
> 
> 
> 
>