Re: Postgres query completion status? - Mailing list pgsql-performance

From Richard Neill
Subject Re: Postgres query completion status?
Date
Msg-id 4B06E744.5070603@cam.ac.uk
Whole thread Raw
In response to Re: Postgres query completion status?  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
List pgsql-performance
Kevin Grittner wrote:
> Richard Neill  wrote:
>
>> SELECT ( core.demand.qty - viwcs.wave_end_demand.qty_remaining )
>>   FROM
>>     core.demand,
>>     viwcs.previous_wave
>>     LEFT OUTER JOIN viwcs.wave_end_demand USING ( wid )
>>   WHERE core.demand.id = viwcs.wave_end_demand.demand_id;
>
> For comparison, how does this do?:
>
> SELECT (core.demand.qty - viwcs.wave_end_demand.qty_remaining)
>   FROM core.demand
>   JOIN viwcs.previous_wave
>     ON (core.demand.id = viwcs.wave_end_demand.demand_id)
>   LEFT OUTER JOIN viwcs.wave_end_demand USING (wid);
>


Thanks for your help,

Unfortunately, it just complains:

ERROR:  missing FROM-clause entry for table "wave_end_demand"
LINE 4:   ON (core.demand.id = viwcs.wave_end_demand.demand_id)

Incidentally, I don't think that this particular re-ordering will make
much difference: viwcs.previous_wave is a table with a single row, and 3
columns in it. Here are the bits of schema, if they're helpful.


            View "viwcs.wave_end_demand"
     Column     |         Type          | Modifiers
---------------+-----------------------+-----------
  wid           | character varying(10) |
  storeorderid  | character varying(30) |
  genreorderid  | character varying(30) |
  target_id     | bigint                |
  sid           | character varying(30) |
  material_id   | bigint                |
  demand_id     | bigint                |
  eqa           | integer               |
  aqu           | bigint                |
  qty_remaining | bigint                |
View definition:
  SELECT wave_gol.wid, wave_gol.storeorderid, wave_gol.genreorderid,
wave_genreorders_map.target_id, wave_gol.sid,
product_info_sku_map.material_id, demand.id AS demand_id, wave_gol.eqa,
COALESCE(du_report_sku_sum.aqu, 0::bigint) AS aqu, wave_gol.eqa -
COALESCE(du_report_sku_sum.aqu, 0::bigint) AS qty_remaining
    FROM viwcs.wave_gol
    LEFT JOIN viwcs.wave_genreorders_map USING (wid, storeorderid,
genreorderid)
    LEFT JOIN viwcs.product_info_sku_map USING (sid)
    LEFT JOIN core.demand USING (target_id, material_id)
    LEFT JOIN ( SELECT du_report_sku.wid, du_report_sku.storeorderid,
du_report_sku.genreorderid, du_report_sku.sid, sum(du_report_sku.aqu) AS aqu
    FROM viwcs.du_report_sku
   GROUP BY du_report_sku.wid, du_report_sku.storeorderid,
du_report_sku.genreorderid, du_report_sku.sid) du_report_sku_sum USING
(wid, storeorderid, genreorderid, sid);



          View "viwcs.previous_wave"
  Column |         Type          | Modifiers
--------+-----------------------+-----------
  wid    | character varying(10) |
View definition:
  SELECT wave_rxw.wid
    FROM viwcs.wave_rxw
   WHERE wave_rxw.is_previous;




                   Table "core.demand"
    Column    |  Type   |           Modifiers
-------------+---------+--------------------------------
  id          | bigint  | not null default core.new_id()
  target_id   | bigint  | not null
  material_id | bigint  | not null
  qty         | integer | not null
  benefit     | integer | not null default 0
Indexes:
     "demand_pkey" PRIMARY KEY, btree (id)
     "demand_target_id_key" UNIQUE, btree (target_id, material_id)
     "demand_material_id" btree (material_id)
     "demand_target_id" btree (target_id)
Foreign-key constraints:
     "demand_material_id_fkey" FOREIGN KEY (material_id) REFERENCES
core.__material_id(id)
     "demand_target_id_fkey" FOREIGN KEY (target_id) REFERENCES
core.waypoint(id)
Referenced by:
     TABLE "core.inventory" CONSTRAINT "inventory_demand_id_fkey"
FOREIGN KEY (demand_id) REFERENCES core.demand(id)






Thanks,

Richard


pgsql-performance by date:

Previous
From: Richard Neill
Date:
Subject: Re: SSD + RAID
Next
From: Richard Neill
Date:
Subject: Re: Postgres query completion status?