From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rod Taylor <rbt@rbt.ca>
Cc: 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:53:34 -0400
Message-ID: <13708.1154350414@sss.pgh.pa.us> (raw)
In-Reply-To: <1154347782.24186.278.camel@home>
References: <20060731015014.87910.qmail@web88005.mail.re2.yahoo.com>
<1154347782.24186.278.camel@home>
Rod Taylor <rbt@rbt.ca> writes:
>> 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).
No, it's not that, because 7.2 certainly had no idea of "IN Join"s.
But there's something mighty fishy about this plan anyway. The
planner was predicting 5809 rows out from flatomfilesysentry (not
too far off), so why didn't it predict something north of
368.09 + 5809 * 439.05 as the total join cost? There's a special case
in cost_nestloop for IN joins, but it sure shouldn't have reduced the
estimate by a factor of 1800+ ...
regards, tom lane
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: tgl@sss.pgh.pa.us, rbt@rbt.ca, hhale21@rogers.com
Subject: Re: sub select performance due to seq scans
In-Reply-To: <13708.1154350414@sss.pgh.pa.us>
* 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