Hallo,
die Tabelle:
-- Table: variablen
-- DROP TABLE variablen;
CREATE TABLE variablen
(
id serial NOT NULL,
varid integer NOT NULL,
varname text,
varsuffix text,
value real,
typ integer,
updatezeit timestamp without time zone,
CONSTRAINT variablen_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE variablen
OWNER TO ips;
Abfrage ist eigentlich egal, das Ergebniss ist immer gleich, aber nehmen wir mal diese:
SELECT
variablen.id,
variablen.varid,
variablen.updatezeit,
lag (variablen.value) over(ORDER BY variablen.updatezeit ASC, variablen.id ASC),
variablen.value,
lead (variablen.value) over(ORDER BY variablen.updatezeit ASC, variablen.id ASC),
round (cast (variablen.value - lag (variablen.value) over(ORDER BY variablen.updatezeit ASC, variablen.id ASC) as numeric),3) as diff,
lead (variablen.value) over(ORDER BY variablen.updatezeit ASC, variablen.id ASC) - lag (variablen.value) over(ORDER BY variablen.updatezeit ASC, variablen.id ASC) as leadlag,
lag (variablen.value) over(ORDER BY variablen.updatezeit ASC, variablen.id ASC) + ((lead (variablen.value) over(ORDER BY variablen.updatezeit ASC, variablen.id ASC) - lag (variablen.value) over(ORDER BY variablen.updatezeit ASC, variablen.id ASC)) / 2) as new
FROM
public.variablen
WHERE variablen.varid = 48020
ORDER BY
diff ASC;
Analyse:
"Sort (cost=203921.95..203940.11 rows=7265 width=20) (actual time=44356.921..44368.180 rows=6201 loops=1)"
" Output: id, varid, updatezeit, (lag(value) OVER (?)), value, (lead(value) OVER (?)), (round(((value - lag(value) OVER (?)))::numeric, 3)), ((lead(value) OVER (?) - lag(value) OVER (?))), ((lag(value) OVER (?) + ((lead(value) OVER (?) - lag(value) OVER (? (...)"
" Sort Key: (round(((variablen.value - lag(variablen.value) OVER (?)))::numeric, 3))"
" Sort Method: quicksort Memory: 565kB"
" -> WindowAgg (cost=203056.44..203456.02 rows=7265 width=20) (actual time=43690.509..44230.228 rows=6201 loops=1)"
" Output: id, varid, updatezeit, lag(value) OVER (?), value, lead(value) OVER (?), round(((value - lag(value) OVER (?)))::numeric, 3), (lead(value) OVER (?) - lag(value) OVER (?)), (lag(value) OVER (?) + ((lead(value) OVER (?) - lag(value) OVER (?)) (...)"
" -> Sort (cost=203056.44..203074.61 rows=7265 width=20) (actual time=43690.284..43713.686 rows=6201 loops=1)"
" Output: id, varid, updatezeit, value"
" Sort Key: variablen.updatezeit, variablen.id"
" Sort Method: quicksort Memory: 565kB"
" -> Seq Scan on public.variablen (cost=0.00..202590.51 rows=7265 width=20) (actual time=16.984..43455.976 rows=6201 loops=1)"
" Output: id, varid, updatezeit, value"
" Filter: (variablen.varid = 48020)"
"Total runtime: 44379.447 ms"
Simple Abfrage:
SELECT
variablen.id,
variablen.varid,
variablen.updatezeit
FROM
public.variablen
WHERE
variablen.varid = 48020
Analyse:
"Seq Scan on public.variablen (cost=0.00..202600.12 rows=7265 width=16) (actual time=16.137..40865.291 rows=6201 loops=1)"
" Output: id, varid, updatezeit"
" Filter: (variablen.varid = 48020)"
"Total runtime: 40916.416 ms"
Datenbank mit 9400000 Zeilen.
Statistik:
Sequential Scans 63
Sequential Tuples Read 593137949
Indexscans 46
Index Tuples Fetched 9567980
Tupel-Eingefügt 121676
Tupel-Aktualisiert 0
Tupel-Gelöscht 0
Tupel-HOT aktualisiert 0
Aktive Tupel 121676
Inaktive Tupel 0
Heap Blocks Read 5246046
Heap Blocks Hit 288705
Index Blocks Read 57385
Index Blocks Hit 9006249
Toast Blocks Read 0
Toast Blocks Hit 0
Toast Index Blocks Read 0
Toast Index Blocks Hit 0
Letztes Vacuum
Letztes Autovacuum
Letztes Analyse
Letztes Autoanalyze
VACUUM Zähler 0
Autovacuum Zähler 0
Analyse Zähler 0
Autoanalyze Zähler 0
Tabellengröße 659 MB
Toast-Tabellengröße 8192 bytes
Indexgröße 203 MB
Letzter Statistiken Reset 2014-06-08 14:09:20.801202+02
Schöne Grüße
Thomas