Re: Postgres query completion status?
От | Richard Neill |
---|---|
Тема | Re: Postgres query completion status? |
Дата | |
Msg-id | 4B06E744.5070603@cam.ac.uk обсуждение исходный текст |
Ответ на | Re: Postgres query completion status? ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
Список | 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 по дате отправления: