Re: Query Optimizer Failure / Possible Bug

Hannes,

> The query and the corresponding EXPLAIN is at
>
> http://hannes.imos.net/query.txt

The problem is that you're using a complex corellated sub-select in the SELECT
clause:

    SELECT
      d.delivery_id,
      da.article_no,
      da.amount,
      (
          SELECT
            COUNT(*)
          FROM
            serials s
            INNER JOIN rma_ticket_serials rts ON (
                s.serial_id = rts.serial_id
            )
          WHERE
            s.article_no  = da.article_no AND
            s.delivery_id = d.delivery_id AND
            rts.replace   = FALSE
      ) AS replaced_serials

This means that the planner pretty much has to iterate over the subquery,
running it once for each row in the result set.   If you want the optimizer
to use a JOIN structure instead, put the subselect in the FROM clause.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco