Skip site navigation (1) Skip section navigation (2)

Re: sub select performance due to seq scans

From: Rod Taylor <rbt(at)rbt(dot)ca>
To: H Hale <hhale21(at)rogers(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: sub select performance due to seq scans
Date: 2006-07-31 12:09:42
Message-ID: 1154347782.24186.278.camel@home (view raw or flat)
Thread:
Lists: pgsql-performance
> 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
> 
> 
> 
> 


In response to

Responses

pgsql-performance by date

Next:From: Martin LesserDate: 2006-07-31 12:17:08
Subject: Partitioning / constrain exlusion not working with %-operator
Previous:From: Axel RauDate: 2006-07-31 11:54:24
Subject: Re: directory tree query with big planner variation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group