DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=YOG+mt5xBbluiNGa6n48CnHyY1RxNObY1i8MqQ25ZmoyG4fzKumVQwgrJsTUBr3hV6/IIHMCzrK1gzHXyDD2mmrAqH4uNHkcYdUQ+nFfkFIDCBun3aTap/PTIZs0/7pErcROyji5W+lEFJoTbcrTmNzl1Pgfr7Sge9aASggkF5E=;
References: <52B311C4.1070108@gmail.com>  
Message-ID: <1387485379.74465.YahooMailNeo@web162902.mail.bf1.yahoo.com>
Date: Thu, 19 Dec 2013 12:36:19 -0800 (PST)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: query plan not optimal
To: Marc Cousin <cousinmarc@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: <52B311C4.1070108@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Marc Cousin <cousinmarc@gmail.com> wrote:

> Then we insert missing paths. This is one of the plans that fail

> insert into path (path)
>=A0=A0 select path from batch
>=A0=A0=A0=A0 where not exists
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (select 1 from path where path.path=3Dbatch=
.path)
>=A0=A0=A0=A0 group by path;

I know you said you wanted to focus on a different query, but this
one can easily be optimized.=A0 Right now it is checking for an
existing row in path for each row in batch; and it only needs to
check once for each path.=A0 One way to write it would be:

insert into path (path)
=A0 select path from (select distinct path from batch) b
=A0=A0=A0 where not exists
=A0=A0=A0=A0=A0=A0=A0=A0=A0 (select 1 from path p where p.path =3D b.path);

> So now we insert into the file table.
>
> insert into file (pathid,filename)
>=A0=A0 select pathid, filename from batch join path using (path);

> What I think is the cause of the problem is that the planner
> doesn't take into account that we are going to fetch the exact
> same values all the time in the path table, so we'll have a very
> good hit ratio.

It kinda takes that into account for the index part of things via
the effective_cache_size setting.=A0 That should normally be set to
50% to 75% of machine RAM.

> Maybe the n_distinct from batch.path could be used to refine the
> caching effect on the index scan ?

Interesting idea.

> For now, we work around this by using very low values for
> seq_page_cost and random_page_cost for these 2 queries.
If you are not already doing so, you might want to try setting
cpu_tuple_cost to something in the 0.03 to 0.05 range.=A0 I have
found that the default is too low compared to other cpu cost
factors, and raising it makes the exact settings for page costs
less sensitive -- that is, you get good plans over a wider range of
page cost settings.=A0 I have sometimes been unable to get a good
plan for a query without boosting this, regardless of what I do
with other settings.

Running with a development build on my 16GB development PC, I got
your fast plan with your "big data" test case by making only this
one adjustment from the postgresql.conf defaults:

set effective_cache_size =3D '2GB';

I also got the fast plan if I left effective_cache_size at the
default and only changed:

set cpu_tuple_cost =3D 0.03;

I know that there have been adjustments to cost calculations for
use of large indexes in both minor and major releases.=A0 If a little
sensible tuning of cost factors to better match reality doesn't do
it for you, you might want to consider an upgrade.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance