Re: [PERFORM] Query Optimizer Failure / Possible Bug

[Hannes Dorbath]

Thank you very much for your reply. I'll try to modify it.
Josh Berkus wrote:
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.

--
imos  Gesellschaft fuer Internet-Marketing und Online-Services mbH
Alfons-Feifel-Str. 9 // D-73037 Goeppingen  // Stauferpark Ost
Tel: 07161 93339-14 // Fax: 07161 93339-99 // Internet: www.imos.net
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly