X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B73825E40AC for ; Fri, 20 Aug 2004 17:04:26 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69717-03 for ; Fri, 20 Aug 2004 20:04:24 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 45CEF5E3639 for ; Fri, 20 Aug 2004 17:04:23 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i7KK41mV001978; Fri, 20 Aug 2004 16:04:02 -0400 (EDT) To: Gaetano Mendola Cc: "pgsql-performance@postgresql.org" Subject: Re: using an index worst performances In-reply-to: <4126545C.5020203@bigfoot.com> References: <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> Comments: In-reply-to Gaetano Mendola message dated "Fri, 20 Aug 2004 21:43:24 +0200" Date: Fri, 20 Aug 2004 16:04:01 -0400 Message-ID: <1977.1093032241@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/309 X-Sequence-Number: 7945 Gaetano Mendola 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