Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 5AC5E635C72
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 19 Mar 2010 10:59:16 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 61913-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 19 Mar 2010 13:59:05 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id AD62B633BB6
	for <pgsql-performance@postgresql.org>;
	Fri, 19 Mar 2010 10:59:05 -0300 (ADT)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Fri, 19 Mar 2010 08:59:03 -0500
Message-Id: <4BA33CD0020000250002FF7D@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Fri, 19 Mar 2010 08:58:56 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Corin" <wakathane@gmail.com>,<pgsql-performance@postgresql.org>
Subject: Re: too complex query plan for not exists query and
	multicolumn indexes
References: <4BA36D7B.6010506@gmail.com>
In-Reply-To: <4BA36D7B.6010506@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/239
X-Sequence-Number: 38064

Corin <wakathane@gmail.com> wrote:
 
> It's already faster, which is great, but I wonder why the query
> plan is that complex.
 
Because that's the plan, out of all the ways the planner knows to
get the requested result set, which was estimated to cost the least.
If it isn't actually the fastest, that might suggest that you
should adjust your costing model.  Could you tell us more about the
machine?  Especially useful would be the amount of RAM, what else is
running on the machine, and what the disk system looks like.  The
default configuration is almost never optimal for serious production
-- it's designed to behave reasonably if someone installs on their
desktop PC to try it out.
 
> I read in the pqsql docs that using a multicolumn key is almost
> never needed and only a waste of cpu/space.
 
Where in the docs did you see that?
 
> As in my previous tests, this is only a testing environment: so
> all data is in memory, no disk activity involved at all, no swap
> etc.
 
Ah, that suggests possible configuration changes.  You can try these
out in the session to see the impact, and modify postgresql.conf if
they work out.
 
seq_page_cost = 0.01
random_page_cost = 0.01
effective_cache_size = <about 3/4 of your machine's RAM>
 
Also, make sure that you run VACUUM ANALYZE against the table after
initially populating it and before your benchmarks; otherwise you
might inadvertently include transient or one-time maintenance costs
to some benchmarks, or distort behavior by not yet having the
statistics present for sane optimizer choices.
 
-Kevin