Hi Andreas,
also ich versuche jetzt das Problem ausführlicher zu erklären. Ich habe zuerst versucht es an einigen Beispielen zu schildern, aber vermutlich ist es besser wenn ich das ganze komplett beschreibe.
Ich habe eine Tabelle in der fortlaufend Messdaten gespeichert werden. Mit einer Taktfrequenz von etwa 5-10 Hz werden die Daten in die Tabelle geschrieben.
Code:
CREATE TABLE data
(
id bigserial NOT NULL,
create_date timestamp without time zone DEFAULT now(),
error integer NOT NULL,
value real NOT NULL,
property_id integer, -- Merkmal
productionlot_id integer, -- Fertigungslos
master_id bigint, -- Gruppe_ID
CONSTRAINT data_pkey PRIMARY KEY (id)
)
Die Daten werden gruppenweise gemessen, stell dir z.b. vor dass eine Fläche an 30-50 unterschiedlichen Punkten vermessen wird, die dann alle logisch zur gleichen Gruppe (master_id) gehören.
Jetzt möchte man dem Benuter einen View zur Verfügung stellen, so dass er alle Daten der Tabelle data sowie die Ergebnisse eines Grubbs-Tests (s. http://www.pg-forum.de/sql/4074-ausr...ostgresql.html) hat, wobei er die Daten über die Selektion von property_id, master_id oder productionId dem View etnehmen könnte:
Code:
create or replace view data_view as select data.*,case when id = any (grubbs_testing(master_id)) then true else false end as is_outlier from data;
select * from data_view where master_id=5383;
Query-Plan:
Seq Scan on data (cost=0.00..2251.59 rows=6 width=51) (actual time=136.647..572.636 rows=10 loops=1)
Filter: (master_id = 5383)
Total runtime: 572.764 ms
select grubbs_testing(master_id) from (select distinct master_id from data where master_id=5383) d;
Query-Plan:
Subquery Scan d (cost=0.00..1001.61 rows=1 width=8) (actual time=133.460..143.296 rows=1 loops=1)
-> Unique (cost=0.00..751.60 rows=1 width=8) (actual time=4.018..13.844 rows=1 loops=1)"
-> Seq Scan on data (cost=0.00..751.59 rows=6 width=8) (actual time=4.002..13.780 rows=10 loops=1)
Filter: (master_id = 5383)"
Total runtime: 143.380 ms" Also wird die Laufzeit der Abfrage im View größtenteils durch die Laufzeit der Aufrufe der Funktion grubbs_testing bestimmt, die wird glaube ich für jeden Datensatz der Ergebnismenge aufgerufen.
Also habe ich mich entschieden, eine custom aggregate funktion zu schreiben, die als final function meine grubbs_testing aufruft, und diese aggreg.fnkt. als window function einzusetzen. So kann ich erzwingen, dass sie nur einmal pro master_id aufgerufen wird:
Code:
CREATE AGGREGATE outliers(bigint) (
SFUNC=last_element,
STYPE=int8,
FINALFUNC=grubbs_testing
);
select data.*,case when id = any (outliers(master_id) over w) then true else false end as is_outlier from data where master_id=5383
window w as (partition by master_id) ;
Query-Plan:
"WindowAgg (cost=0.00..751.74 rows=6 width=51) (actual time=143.805..143.839 rows=10 loops=1)"
" -> Seq Scan on data (cost=0.00..751.59 rows=6 width=51) (actual time=3.965..24.097 rows=10 loops=1)"
" Filter: (master_id = 5383)"
"Total runtime: 144.005 ms"
Wie man sieht ist dieser Aufruf 4-mal schneller als der im View da oben.
Das Problem ist aber wenn ich diese Abfrage in die View-Definition reinpacke (ohne WHERE) dann habe ich der Fall wie am Anfang des Thread beschrieben: Zuerst wird die grubbs_testing() für alle Daten berechnet und erst anschließend die Datensätze die man braucht selektiert.
Ich hoffe, dass ich jetzt alles besser erklärte.
Übrigens habe ich jetzt die Abfragen mit Window AS (Partition by foo) und ohne Order by getestet und kein Fall gefunden wo die Ergebnisse von einem Subselect sich von eines Select unterscheiden (abgesehen von der Laufzeit/Query Plan).
Lesezeichen