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