From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gaetano Mendola <mendola@bigfoot.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: using an index worst performances
Date: Fri, 20 Aug 2004 16:04:01 -0400
Message-ID: <1977.1093032241@sss.pgh.pa.us> (raw)
In-Reply-To: <4126545C.5020203@bigfoot.com>
References: <cg2nrf$sc1$1@floppy.pyrenet.fr>
<4124ECF3.6040901@archonet.com>
<412505F6.6080000@bigfoot.com>
<4125565D.3060008@familyhealth.com.au>
<4125C657.7000702@bigfoot.com>
<1093006549.75942.61.camel@jester>
<41261E41.3080204@bigfoot.com>
<22026.1093018762@sss.pgh.pa.us>
<4126545C.5020203@bigfoot.com>
Gaetano Mendola <mendola@bigfoot.com> writes:
> Tom Lane wrote:
> | Could we see EXPLAIN ANALYZE EXECUTE output for each case?
> [snip]
> See above.
Okay, so the issue here is choosing between a nestloop or a hash join
that have very nearly equal estimated costs:
> ~ -> Hash Join (cost=1.74..46.14 rows=1 width=760) (actual time=0.342..0.825 rows=3 loops=1)
> ~ Hash Cond: (("outer".vtype)::text = ("inner"."type")::text)
> ~ -> Nested Loop (cost=0.00..46.13 rows=1 width=760) (actual time=0.278..0.933 rows=3 loops=1)
> ~ Join Filter: (("outer".vtype)::text = ("inner"."type")::text)
In the indexed case it's the same choice, but at a different level of joining:
> ~ -> Hash Join (cost=1.74..13.15 rows=1 width=768) (actual time=0.281..0.651 rows=5 loops=1)
> ~ Hash Cond: (("outer".vtype)::text = ("inner"."type")::text)
> ~ -> Nested Loop (cost=0.00..13.14 rows=1 width=768) (actual time=0.268..0.936 rows=5 loops=1)
> ~ Join Filter: (("outer".vtype)::text = ("inner"."type")::text)
With only 0.01 unit of difference in the costs, it's perfectly plausible
for a change in the statistics to change the estimated cost just enough
to give one plan or the other the edge in estimated cost.
Given that the runtimes are in fact pretty similar, it doesn't bother me
that the planner is estimating them as essentially identical.
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, mendola@bigfoot.com
Subject: Re: using an index worst performances
In-Reply-To: <1977.1093032241@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