pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (11+ messages)  latest in thread

Message-ID: <1977.1093032241@sss.pgh.pa.us>
Permalink:  ../1977.1093032241@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1977.1093032241@sss.pgh.pa.us

 · 

reply

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