Skip site navigation (1) Skip section navigation (2)

Re: Query Optimizer Failure / Possible Bug

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Hannes Dorbath <light(at)theendofthetunnel(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query Optimizer Failure / Possible Bug
Date: 2005-03-28 19:51:17
Message-ID: 200503281151.17344.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Karim A NassarDate: 2005-03-28 20:03:12
Subject: Re: Delete query takes exorbitant amount of time
Previous:From: Simon RiggsDate: 2005-03-28 19:25:54
Subject: Re: Delete query takes exorbitant amount of time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group