Resourcenschonende Lösung gesucht für Monat und Jahrübersicht

[deathdragon]

Hallo, ein frohes neues Jahr wünsche ich allen.

Ich habe hier eine gigantische Datenmenge von Milliarden Daten vorliegen. Diese sollen so schonend wie möglich durchsucht werden.
Ziel ist es von mir ist es in einem Bereich von einem Startzeitstempel bis zu einem Endzeitstempel alle Jahre in einer Spalte und alle Monate in einer Spalte zu haben.
Leider habe ich noch überhaupt nichts gefunden was Postgresql dazu bringt dies zu tun.

Die Zeitstempel bestimme ich einfach mit min und max
Also SELECT MIN(Zeitstempel),MAX(Zeitstempel) from Testtabelle

Ergibt:
min max
2016-09-16 11:11:00+02 2017-01-03 09:35:00+01

so und wie kann ich den Bereich jetzt nutzen das ich am Ende das erhalte

Jahr Monat
2016 9
2016 10
2016 11
2016 12
2017 1
2017 2
usw.

Man könnte das auch über Group by machen aber dafür gehören demjenigen die Finger ...... na ihr wisst schon!

[defo]

Ich habe hier eine gigantische Datenmenge von Milliarden Daten vorliegen. Diese sollen so schonend wie möglich durchsucht werden.
Ziel ist es von mir ist es in einem Bereich von einem Startzeitstempel bis zu einem Endzeitstempel alle Jahre in einer Spalte und alle Monate in einer Spalte zu haben.

Was bedeutet "..in einer Spalte ..zu haben.."
Bei der Ausgabe einer Datenmenge mit Zeitbereichs suche?
Oder die Datenmenge(das Datenmodell) so zu ändern, dass der Zeitstempel redundant vorliegt und gekürzt als Jahr bzw. Monat vorliegt?

Mal als Hinweis:
Wenn es um eine schonende Suche in sehr großen Datenmengen geht, kann man vielleicht die tollen Indexfunktionen von PG nutzen.
Hier bietet sich z.B. die Möglichkeit, eingeschränkte Indizes anzulegen, die nur den gewünschten Wertebereich(!) indizieren. Dazu wird bei der Indexerzeugung dieser Wertebereich mit angegeben.
PG ist dabei so schlau, diesen Index automatisch zu nutzen, wenn eine zum Index passende Einschränkung in der Where Clause vorliegt. Bedeutet in der Praxis, dass man auch problemlos andere Indices auf der gleichen Spalte haben kann, es wird der beste verwendet.
Die Performancesteigerung kann enorm sein, da auf die Art oft nur ein Bruchteil von Indexeinträgen durchlaufen wird- jenach definiertem Range.

Die Funktion ist ungefähr ab V 9.4 oder so verfügbar, weiß ich nicht auswendig.

[deathdragon]

Hallo defo,

das mit den Indizes klingt nicht schlecht. Das schau ich mir mal an. Trotzdem würde ich es gern noch als SQL Abfrage realisieren.
Eigentlich würde es mir auch reichen in einem Bereich aus zwei Zeitstempeln alle Jahre zu erhalten und in einer separaten Abfrage in einem Bereich aus zwei Zeitstempeln alle Monate zu erhalten.

Wie könnte ich das realisieren?? Gibt es da nichts von Seiten Postgresql?

[defo]

Ich glaube, ich habe die Frage immer noch nicht genau verstanden.
So wie Du sie zuletzt gestellt hast und ich verstanden, kommst Du um ein Group by nicht herum, das ist auch bei keinem System anders.
Das ist solange unproblematisch, wie der Range den Du rausbekommen willst, eine deutlich kleinere Menge ergibt, als im Ganzen vorliegt. Es wird nur der Range Bereich gruppiert. *
Alternativ könnte man vielleicht noch mit Partitionierung arbeiten, wenn der abgefragte Bereich im Volumen doch kritisch wird.

Die Indexfrage bezieht sich auf Range Index oder Partial Index- was ich meinte, Partial Index ist schon älter, gibt es mindestens ab 9.2 oder früher.
Hier ist die "Problematik", dass ein Index statisch ist und ggF. angepasst, ergänzt werden muss, wenn sich die "interessanten", also abgefragten Bereiche ändern. Das würde man bspw. über cron jobs (bei Linux Servern) oder entsprechenden Mechanismen unter Windows Servern realisieren.

Range Index ist flexibler, aber in ältereren Versionen nicht verfügbar, hab ich noch nicht ausprobiert.

Hab den * vergessen:
Es kann sein, dass die Performance trotzdem einbricht, weil der Optimizer in einer komplexen Abfrage nicht erkennt, dass er zunächst eine deutliche Reduktion der Menge bewerkstelligen könnte. Weil er also erst gruppiert und danach erst filtert. Dazu müsstest Du mal die tatsächlcihe Datenmenge und die tatsächliche Abfrage zeigen.

[deathdragon]

Hallo defo, eigentlich ist die Sache ganz einfach ich möchte zwischen zwei Zeitstempeln (anfang und ende) genau die Jahre und Monate herausfinden die dazwischen liegen. Jedoch braucht das schon so dermaßen viel Leistung das ich hier eigentlich nicht mit Group by arbeiten muss! ABER! mir ist gerade eine andere Idee gekommen:
Ziel ist es nur das Jahr und den Monat zwischen einen Timestamprange zu wissen.
zb. zwischen 1.1.2016 (min Zeitstempel) und den 25.5.2016 (max Zeitstempel)
dazwischen liegen folgende Ergebnistabelle:
Monat Jahr
1 2016
2 2016
3 2016
4 2016
5 2016

das ist soll also das Ergebnis sein.
Man müsste doch jetzt "nur noch" abfragen if min-Zeitstempel(Jahr) <=max-Zeitstempel(jahr) then
if min-Zeitstempel(Monat) <= max-Zeitstempel(Monat)


USW. das sollte doch gehen?
nur bekomme ich das nicht hin weil ich kein dunst von Postgresql habe

ich hänge schon bei den ersten Gehversuchen fest

DO
$do$
BEGIN

if 2016 <> 2017 THEN
select 'mist';
else
select 'gut';
END if;
END
$do$

[defo]

mmh, was machst Du da?
Hast Du Ahnung von SQL?

Mach mal bitte:
-- Anzahl Datensätze abfragen
[highlight=sql]
select count(*) from <MYBIGTABLENAME>;
[/highlight]

-- vorhandene Indizes abfragen
[highlight=sql]
select t.relname as table_name,
c.relname as index_name,
array_to_string(array_agg(a.attname), ', ') as column_name
from pg_class t, pg_class c, pg_index i, pg_attribute a
where t.oid = i.indrelid
and c.oid = i.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(i.indkey)
and t.relkind = 'r'
and t.relname = '<MYBIGTABLENAME>'
group by t.relname, c.relname
order by t.relname, c.relname
[/highlight]

Das sollte beides nicht weh tun, dann sehen wir weiter.

P.S. natürlich MYBIGTABLE(NAME) ersetzen durch den richtig Namen, klein geschrieben.

[Ralf Jansen]

Hallo defo, eigentlich ist die Sache ganz einfach ich möchte zwischen zwei Zeitstempeln (anfang und ende) genau die Jahre und Monate herausfinden die dazwischen liegen.

Was hat das mit deinen Milliarden Daten zu tun? Die zwischen 2 Zeitpunkten liegenden Monate/Jahre sind unabhängig von weiteren Daten. Da du uns aber was von Milliarden Daten erzählst gibt es zu denen einen Zusammenhang und denn solltest du uns erzählen. Denn davon hängt ab welche Lösung eine gute Lösung wäre.

Einfach die Monate zwischen 2 Zeitpnkten wäre

[Highlight=SQL]SELECT date_part('month', t), date_part('year', t) FROM generate_series( '2016-01-01'::timestamp , '2016-05-25'::timestamp, '1 month') t;[/Highlight]

[deathdragon]

so jungs! da ihr denken müsst das ich hier der letzte Volldepp bin habe ich mich zurück zu den Wurzeln des Übels begeben!
Ich habe eine kleine Tabelle erstellt und mal schlank 5 Werte mit irgendeinen Zeitstempel reingeschossen!
Danach folgende Abfrage darauf abgesetzt:

select /*"Wert",*/

EXTRACT(YEAR from "Timestamp_Messung"), EXTRACT(Month from "Timestamp_Messung") from "Zeiten" group by EXTRACT(YEAR from "Timestamp_Messung"),EXTRACT(Month from "Timestamp_Messung") /*,"Wert" */
order by EXTRACT(YEAR from "Timestamp_Messung") desc

und Bumm siehe da das Ergebnis was ich haben will ist vollständig vorhanden!

Nun zum großen Übeltäter!

Mach ich die gleich Abfrage (natürlich nur passende Stellen geändert)
rennt er knapp 5-10 min und danach stürzt mir alles ab und dabei bin ich noch auf eine sehr kleine DB mit popligen ca 200000 Datensätzen gegangen! hier ist doch irgendwas falsch! Wenn nicht geb ich meinen Job auf

[defo]

Ich kenne solche Effekte von Oracle, wenn
a) ein "schlechter" Client verwendet wird
b) falsche/ungü+ltige Datumsangaben gespeichert sind (unpassend zum Datetyp)

Abhilfe
a) guten Client nehmen, also am einfachsten psql console
b) ungültige Werte finden und korregieren

Ob das hier zutrifft, kann ich natürlich nicht versprechen.

Dann um der Sache auf die Spur zu kommen:
Bei großen Datenmengen Arbeit erleichtern> order by erstmal weglassen
Das Extract ggF. austauschen gegen Ralfs Funktion. (Ich weiß nicht, wie aufwendig das Extract implementiert ist und was es noch alles kann) Die Typ Notation aus dem Statement von Ralf ist jedenfalls empfehlenswert
Die Datenbank Umgebung prüfen. Manchmal sind es ganz doofe Sachen. Group by und Sort brauchen viel Platz, das wird auf Platte ausgelagert, wenn RAM nicht reicht, kann schon mal klemmen, wenn Platte voll ist oder sowas. Ggf. ist das Dateisystem auch fehlerhaft. Prüfen.

Zum Thema Volldepp:
Das hast Du gesagt, ich bin da ganz wertfrei, erlaube mir aber offensichtlich Fragen zum Kenntnisstand. Das halte ich für legitim.
Jeder von uns kommt im EDV Bereich immer wieder an irgendwelche Limits, dafür sind solche Foren ja da. Also nicht persönlich nehmen.

P.S: Sehe gerade, dass ich Blödsinn geschrieben habe. Die Typ Notation in Ralfs Statement wird ja nur beim Generieren verwendet. Typnotation ist trotzdem häufig empfehlenswert, Date_Part ggF. besser implementiert.
Und dann noch vergessen, probier mal statt group by aus, distinct zu verwenden (group by weglassen). Ist ein Voodoo Vorschlag, aber das Verhalten ist auch wirklich ungewöhnlich.

[deathdragon]

@defo persönlich nehme ich schon lang nichts mehr! alles gut - danke für jeden noch so kleinen Tipp! Ich mach mich mal auf die Suche

[Ralf Jansen]

Und dann noch vergessen, probier mal statt group by aus, distinct zu verwenden (group by weglassen).

Solange wir nicht wissen was er genau macht ist das Raterei.
Ich vermute ja mal das sein auskommentierter Wert im Beispiel eine Aggregatfunktion ist dann ohne group by auszukommen wird schwer.

Eine schnelle Version bekommen wir vermutlich nur hin wenn wir weder extract, datepart us.w. brauchen also so das wir einen hoffentlich existierenden Index auf der Timestamp_Messung Spalte auch nutzen können. Für einen sinnvollen Vorschlag fehlt uns aber der eigentliche Problemkontext.

[defo]

Ok, siehe hier:
https://www.postgresql.org/message-i...9%40rcfile.org

date_part wird empfohlen, extract landet sowieso bei date_part

Und wenn Du was gefunden hast, sag bitte Bescheid, interessiert mich.
Und falls Du die Suche abkürzen willst, probier es auf einer anderen DB, die mglw sauberere Daten hält, sofern vorhanden.
Hab mir erlaubt, mal in Deinen Beiträgen zu schauen, da finde ich alle möglichen DB, u.a. Oracle, MSSQL und Replikationskram. Die Annahme, dass die Daten einfach korrupt sind, ist also nicht so ganz an den Haaren herbeigezogen.

[defo]

@Ralph
Das ist richtig, dass im Beispiel entscheidende Teile fehlen können, habe ich nicht bedacht.

@deathdragon
Wenn du bestimmte Dinge nicht veröffentlichen kannst, versuch es umzuformulieren, ggF. mit einem View und Feldumbenennung statt der Originalnamen. Semantisch sollte es möglichst nah am original sein.