Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0DE846326D3 for ; Mon, 22 Feb 2010 18:16:32 -0400 (AST) 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 47845-06 for ; Mon, 22 Feb 2010 22:16:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from 30.mail-out.ovh.net (30.mail-out.ovh.net [213.186.62.213]) by mail.postgresql.org (Postfix) with SMTP id 36CB463352F for ; Mon, 22 Feb 2010 18:16:21 -0400 (AST) Received: (qmail 12360 invoked by uid 503); 22 Feb 2010 22:20:49 -0000 Received: from b9.ovh.net (HELO mail635.ha.ovh.net) (213.186.33.59) by 30.mail-out.ovh.net with SMTP; 22 Feb 2010 22:20:48 -0000 Received: from b0.ovh.net (HELO queueout) (213.186.33.50) by b0.ovh.net with SMTP; 22 Feb 2010 22:16:26 -0000 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO localhost) (lists%peufeu.com@88.161.102.87) by ns0.ovh.net with SMTP; 22 Feb 2010 22:16:24 -0000 Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes To: "Joel Jacobson" , "Nikolas Everett" Cc: pgsql-performance@postgresql.org Subject: Re: plpgsql plan cache References: <38E2265E-B38E-4EBD-BB87-23B46EF4FD1C@gluefinance.com> <8AD3F6B6-BCCF-46E1-B255-9EB8E761653B@gluefinance.com> <8bdec0841002221347m1c4556a4xb670877aab429a13@mail.gmail.com> Date: Mon, 22 Feb 2010 23:15:30 +0100 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit From: "Pierre C" Message-ID: In-Reply-To: <8bdec0841002221347m1c4556a4xb670877aab429a13@mail.gmail.com> User-Agent: Opera Mail/10.10 (Linux) X-Ovh-Tracer-Id: 159596313073419865 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|U 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.11 tagged_above=-10 required=5 tests=BAYES_05=-1.11 X-Spam-Level: X-Archive-Number: 201002/305 X-Sequence-Number: 37726 Actually, planner was smart in using a bitmap index scan in the prepared query. Suppose you later EXECUTE that canned plan with a date range which covers say half of the table : the indexscan would be a pretty bad choice since it would have to access half the rows in the table in index order, which is potentially random disk IO. Bitmap Index Scan is slower in your high-selectivity case, but it can withstand much more abuse on the parameters. PG supports the quite clever syntax of EXECUTE 'blah' USING params, you don't even need to mess with quoting.