Re: Planner regression in 8.0.x: WORKAROUND

NOT TRUE!

The second query is effectively "(SELECT TRUE ...WHERE ... LIMIT 1) OR
(SELECT TRUE ...WHERE ... LIMIT 1) AS ..."

The first portion in parentheses can return either a single row of TRUE,
or no row (NULL). Ditto for the second portion. The OR means that you
logically combine TRUEs and/or NULLs into a SINGLE value. This can be
trivially verified by the following SELECT:

select (select true from anytable where TRUE limit 1) or (select true
from anytable where TRUE limit 1);

Vary the capitalized "TRUE"s each between true and false, and you will
see that EXACTLY ONE ROW IS RETURNED IN EACH CASE, having a resultant
(combined) value of either TRUE or NULL.

The real issue here is why the original query executes in a fraction of
a second under 7.4.x, and runs for hours on 8.0.4.

-- Dean

On 2005-10-17 11:17, Jim C. Nasby wrote:
> Those two queries aren't the same. The first one can only return 0 or 1 rows;
> the second one can return 0, 1, or 2 rows.
>
> An explain analyze of each should show why one is much faster than the
> other.
>
> On Mon, Oct 17, 2005 at 10:29:43AM -0700, Dean Gibson (DB Administrator) wrote:
>
>> In the query below, if I replace:
>>
>> (SELECT TRUE FROM archivejb WHERE ( (callsign = gen.callsign AND
>> license_status = 'A' AND prev_callsign = gen.vanity_callsign)
>> OR (callsign =
>> gen.vanity_callsign AND licensee_id =
>> gen.licensee_id))
>> AND grant_date < receipt_date LIMIT
>> 1) AS _verified,
>>
>> with:
>>
>> (SELECT TRUE FROM archivejb WHERE callsign = gen.callsign AND
>> license_status = 'A' AND prev_callsign = gen.vanity_callsign
>> AND grant_date < receipt_date LIMIT
>> 1) OR
>> (SELECT TRUE FROM archivejb WHERE callsign =
>> gen.vanity_callsign AND licensee_id = gen.licensee_id
>> AND grant_date < receipt_date LIMIT
>> 1) AS _verified,
>>