Thread: BUG #13995: Inconsistent exucution plan while using enable_nestloop

The following bug has been logged on the website:

Bug reference:      13995
Logged by:          Eyal
Email address:      eyal@impactsoft.co.il
PostgreSQL version: 9.5.1
Operating system:   centos 6.7
Description:

Hi,

I checked the TODO list and in the FAQ, there are some items that looks
similar, but I do not really know if any of them are actually the same as
the following.

This may sound like a typical performance issue, but it's actually a bug,
because the plan chosen by the optimizer is inconsistent: right after
creating the function, it's execution time is ~0.05 seconds. EXACTLY 5
time's it's ~0.05, and than, from the 6th execution onwards, it executes
~1.3 seconds (and sometimes ~46 seconds, probably right after VACUUM
ANALYZE).

We created a database that reproduce this bug.

We first faced this bug in pg9.3.10 on centos6.7 (16GB ram, shared_buffers =
3072MB ). but we actually reproduced it on other systems:
a. oracle virtualbox: centos6.7 pg9.3.10 and pg9.5.1
b. windows 7 pg9.3.10, pg9.4.6 and pg9.5.1

To reproduce the bug:
1. restore this databse:
https://drive.google.com/file/d/0Byp05k27v6xzUWktNm9ySk1uNVk/view?usp=sharing
2. anslyze all tables (please DO NOT VACUUM. see remark below)
3. create the function:
https://drive.google.com/file/d/0Byp05k27v6xzOFlVRUFPenkyZ1k/view?usp=sharing
4. execute the function 6 times: select
runtest_with_ENABLE_NESTLOOP(320,cast(1 as
smallint),0,200001865232,3202111102000000,cast(-1 as smallint),7045,4);


Remark:
After VACUUM FULL ANALYZE it's always ~46 seconds (from the first run).
than, right after VACUUM ANALYZE (not FULL), it's exactly 5 times ~0.02
seconds, and than, form the 6th onwards, it's ~27seconds

Best regards,
Eyal.