Re: Planner regression in 8.0.x: WORKAROUND

I stand corrected; I read through the example too quickly.

As I said, explain analyze would show what the difference is between the
two queries. I suspect that the planner doesn't know these are
equivalent, because in the general case of selecting more than just TRUE
they wouldn't be.

No idea why this is different from 7.4.8, but a lot of code has changed
in that timespan.

In any case, you're more likely to get help from a developer if you post
the full query, and EXPLAIN ANALYZE for each case. Please include any
view definitions as well.

On Mon, Oct 17, 2005 at 11:45:04AM -0700, Dean Gibson (DB Administrator) wrote:
> 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,
> >>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461