From: Rod Taylor <rbt@rbt.ca>
To: H Hale <hhale21@rogers.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: sub select performance due to seq scans
Date: Mon, 31 Jul 2006 08:09:42 -0400
Message-ID: <1154347782.24186.278.camel@home> (raw)
In-Reply-To: <20060731015014.87910.qmail@web88005.mail.re2.yahoo.com>
References: <20060731015014.87910.qmail@web88005.mail.re2.yahoo.com>
> 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
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rbt@rbt.ca, hhale21@rogers.com
Subject: Re: sub select performance due to seq scans
In-Reply-To: <1154347782.24186.278.camel@home>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox