Subject: Re: Unexplainable execution time difference between two test
 functions...one using IF (SELECT COUNT(*) FROM...) and the other using IF
 EXISTS (SELECT 1 FROM...)
To: Hackety Man <hacketyman@gmail.com>,
 Pgsql Performance <pgsql-performance@lists.postgresql.org>
References: 
 <CA+1fJ+8vEq+RWpgpqgaxjwq1AvnRSrm0FFgN2_8VoRUq7=asMA@mail.gmail.com>
 <e189a2ea-8612-339b-8de5-1ed567118670@2ndquadrant.com>
 <CA+1fJ+-ibpa4puzKpQm6EYNiXOkD5rx0wqkO7ktOG9jVdY_zBw@mail.gmail.com>
 <d29d4f62-5b10-bc1f-3efd-c208dbffb0d3@2ndquadrant.com>
 <CA+1fJ+9NePLibRzw+Q6wTjcG9ZpjpuDmx0m=zr+FrmP2Wx6LSw@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <b95edafe-7ab4-02c4-6096-8e04c815513e@2ndquadrant.com>
Date: Tue, 17 Apr 2018 22:29:16 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.4.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+1fJ+9NePLibRzw+Q6wTjcG9ZpjpuDmx0m=zr+FrmP2Wx6LSw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 04/17/2018 05:43 PM, Hackety Man wrote:
> 
> 
> On Tue, Apr 17, 2018 at 10:23 AM, Tomas Vondra
> <tomas.vondra@2ndquadrant.com <mailto:tomas.vondra@2ndquadrant.com>> wrote:
> 
> 
> 
>     On 04/17/2018 04:01 PM, Hackety Man wrote:
> 
>  ...
>         Right.  I was more wondering why it switched over to a generic
>         plan, as you've stated, like clockwork starting with the 6th
>         execution run.
> 
> 
>     That's a hard-coded value. The first 5 executions are re-planned
>     using the actual parameter values, and then we try generating a
>     generic plan and see if it's cheaper than the non-generic one. You
>     can disable that, though.
> 
> 
> 
> So on that note, in the planner's eyes, starting with the 6th execution,
> it looks like the planner still thinks that the generic plan will
> perform better than the non-generic one, which is why it keeps using the
> generic plan from that point forward?
> 

Yes. The point of prepared statements (which also applies to plpgsql, as
it uses prepared statements automatically) is to eliminate the planning
overhead. So we try planning it with actual parameter values for the
first 5 plans, and then compare it to the generic plan.

> Similar to the parallel scans, any chance of the planner possibly being
> enhanced in the future to come to a better conclusion as to whether, or
> not, the generic plan will perform better than the non-generic plan?  :-)

There's always hope, but it's hard to say if/when an enhancement will
happen, unfortunately.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services