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.
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 |