Geht das auch ohne Subselect?

[Thallius]

Hi,

folgende Tabellen:

CREATE TABLE "public"."orders" (
  "id" int4 NOT NULL DEFAULT nextval('order_id_seq'::regclass),
  "type" varchar(255) NOT NULL,
  "start_date" date NOT NULL,
  "end_date" date NOT NULL,
  "deleted" int2 DEFAULT 0
)

CREATE TABLE "public"."order_tools" (
  "order_id" int4 NOT NULL,
  "tool_number" varchar(50) NOT NULL,
  "deliver_date" date default NULL,
  "return_date" date default NULL,
)

Foreign Keys und Indexe lass ich mal weg.

Eine Order kann mehrere Tools haben. Jedes Tool kann zu anderen Zeiten verschickt und zurück geschickt werden. Wichtig ist nur, das alle Tools in dem Zeitraum der Order vor Ort sind. Initial sind deliver_date und return_date NULL.

Daraus ergibt sich für die Order folgende Kombinationen die ich mal Status nenne:

deleted = 1 => "Deleted"
deliver_date = NULL und return_date = NULL => "Planned"
deliver_date != NULL und return_date = NULL => "In Execution"
deliver date != NULL und return_date != NULL => "Done"

Wenn ich nun eine liste der jobs mit ihrem Status haben will, fällt mir im Moment nur folgendes ein:

SELECT id, type, job_type AS jobType, start_date as startDate, end_date AS endDate,
                                CASE WHEN deleted = 1 THEN 'deleted' ELSE
                                   CASE WHEN
                                       (
                                           SELECT COUNT(ot1.order_id)
                                           FROM order_tools ot1
                                           WHERE ot1.order_id = o.id
                                             AND ot1.deliver_date IS NOT NULL
                                       ) = 0
                                       THEN 'planned'
                                       ELSE
                                           CASE WHEN
                                                       (
                                                           SELECT COUNT(ot2.order_id)
                                                           FROM order_tools ot2
                                                           WHERE ot2.order_id = o.id
                                                             AND ot2.return_date IS NULL
                                                       ) <> 0
                                               THEN 'executing'
                                               ELSE 'done'
                                           END
                                    END
                                END AS status
                              FROM orders o

dessen explain folgendes ergibt (Logischerweise aber der vollständigkeit halber will ich es erwähnen)

Seq Scan on orders o  (cost=0.00..3989021.25 rows=8807 width=91)
  SubPlan 1
    ->  Aggregate  (cost=226.43..226.44 rows=1 width=8)
          ->  Seq Scan on order_tools ot1  (cost=0.00..226.43 rows=1 width=4)
                Filter: ((deliver_date IS NOT NULL) AND (order_id = o.id))
  SubPlan 2
    ->  Aggregate  (cost=226.43..226.44 rows=1 width=8)
          ->  Seq Scan on order_tools ot2  (cost=0.00..226.43 rows=1 width=4)
                Filter: ((return_date IS NULL) AND (order_id = o.id))

Leider ist das bei 20000 Aufträgen doch recht langsam. Jemand einen Vorschlag wie man es schneller bekommt?

Gruß

Thallius

 

[castorp]

Ich würde es so machen:

select o.*,
       case 
          when ot.deliver_date is not null then 'planned'
          when ot.return_date is null then 'executing'
          else 'done'
       end as status
from orders o
  left join ( 
     select order_id, 
            max(deliver_date) as deliver_date,
            max(return_date) as return_date
     from order_tools 
     group by order_id
  ) ot on ot.order_id = o.id;

Es ist typischerweise schneller viele Datesätze auf einmal zu verarbeiten, anstatt jeden Datensatz einzeln. Und es interessiert ja nur, ob es ein deliver_date oder return_date gibt, der eigentlich Wert ist dabei unerheblich. Gibt es für eine order_id ausschliesslich NULL Werte in den Spalten so liefert max() auch NULL zurück.

Wenn Du Dir sicher seien kannst, dass es für jede order immer mindestens einen Eintrag in order_tools gibt, dann kannst Du den outer join auch weglassen (und JOIN anstatt LEFT JOIN verwenden)