BUG #13995: Inconsistent exucution plan while using enable_nestloop

From: eyal(at)impactsoft(dot)co(dot)il
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13995: Inconsistent exucution plan while using enable_nestloop
Date: 2016-02-29 09:24:28
Message-ID: 20160229092428.10548.42478@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13995
Logged by: Eyal
Email address: eyal(at)impactsoft(dot)co(dot)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.

Browse pgsql-bugs by date

  From Date Subject
Next Message Thom Brown 2016-02-29 10:55:40 Re: Server crash with certain encodings
Previous Message Michael Paquier 2016-02-29 06:43:03 Re: Re: BUG #13685: Archiving while idle every archive_timeout with wal_level hot_standby