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: Pavel Stehule <pavel.stehule@gmail.com>,
 Hackety Man <hacketyman@gmail.com>, Robert Haas <robertmhaas@gmail.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
References: 
 <CA+1fJ+8vEq+RWpgpqgaxjwq1AvnRSrm0FFgN2_8VoRUq7=asMA@mail.gmail.com>
 <CAFj8pRA5sS+dxBP8ZiDLMw6KUZKtVGa476cgxe5nJ5K1n43xng@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <3fa6c3a7-e85e-8dc5-35b5-70b06f86ffd0@2ndquadrant.com>
Date: Tue, 17 Apr 2018 12:52:45 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.5.2
MIME-Version: 1.0
In-Reply-To: 
 <CAFj8pRA5sS+dxBP8ZiDLMw6KUZKtVGa476cgxe5nJ5K1n43xng@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk


On 04/17/2018 07:17 AM, Pavel Stehule wrote:
> Hi
> 
> 2018-04-16 22:42 GMT+02:00 Hackety Man <hacketyman@gmail.com 
> <mailto:hacketyman@gmail.com>>:
> 
> ...
 >
> A support of parallel query execution is not complete -  it doesn't work 
> in PostgreSQL 11 too. So although EXISTS variant can be faster (but can 
> be - the worst case of EXISTS is same like COUNT), then due disabled 
> parallel execution the COUNT(*) is faster now. It is unfortunate, 
> because I believe so this issue will be fixed in few years.
> 

None of the issues seems to be particularly related to parallel query. 
It's much more likely a general issue with planning EXISTS / LIMIT and 
non-uniform data distribution.


regards

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