wenn ich nun einen normalen index mache btree mit object_referenz, timestamp dann ist der über 20GB gross
falls ich nun
select min(timestamp) from table_a where referenz = ‘a’ machen
dauert das ewig, ausser die daten/index sind gecacht nur ist das normalerweise nicht der fall und der index ist ja megagross und ich kann nicht den ganzen index cachen
nun habe ich mir gedacht dass solch ein riesiger index wohl nicht mehr viel bringt da der ja eh fast nie im cache ist und auch megaplatz im cache verbrauchen würde und es wahrscheinlich auch lange dauert bis er den gecacht hat
deshalb habe ich nun gedacht ich mache mal eine gin index
create index adfaf on table_a using gin ( object_referenz, publishing_timestamp);
oder
create index adfaf on table_a using gin ( publishing_timestamp, object_referenz);
welche variante ist wohl effizienter?
ich denke die erste variante wird da bei der suche effizienter sein
aber ob der index viel kleiner wird bezweifle ich
der erste select dauert rund 10 sec da wohl von disk gelesen werden muss und beim 2. mal ist es gecacht (schon erstaunlich wieviel langsamer so diskreads sind)
homegate_prod=> explain (analyze, buffers, verbose) select min(ts) from pc where ref = ‘wic#1100#10#04090’;
QUERY PLAN
Aggregate (cost=98.00…98.01 rows=1 width=8) (actual time=10473.883…10473.883 rows=1 loops=1)
Output: min(ts)
Buffers: shared hit=31 read=135 dirtied=56
-> Index Only Scan using i_pc_obj_adv on homegate.pc (cost=0.70…96.21 rows=714 width=8) (actual time=498.529…10473.725 rows=1
77 loops=1)
Output: ref, adv_id, ts
Index Cond: (pc.ref = ‘wic#1100#10#04090’::text)
Heap Fetches: 134
Buffers: shared hit=31 read=135 dirtied=56
Planning time: 0.176 ms
Execution time: 10473.917 ms
(10 rows)
homegate_prod=> explain (analyze, buffers, verbose) select min(ts) from pc where ref = ‘wic#1100#10#04090’;
QUERY PLAN
Aggregate (cost=98.00…98.01 rows=1 width=8) (actual time=0.336…0.336 rows=1 loops=1)
Output: min(ts)
Buffers: shared hit=166
-> Index Only Scan using i_pc_obj_adv on homegate.pc (cost=0.70…96.21 rows=714 width=8) (actual time=0.033…0.319 rows=177 loo
ps=1)
Output: ref, adv_id, ts
Index Cond: (pc.ref = ‘wic#1100#10#04090’::text)
Heap Fetches: 134
Buffers: shared hit=166
Planning time: 0.170 ms
Execution time: 0.361 ms
(10 rows)
der index der verwendet wird ist ungefähr 20gb gross die Tabelle enthält 200mio datensätze
sind die Keys alle so 17 Byte lang? Dann sollte der Index keine 20GB haben, eher vielleicht 4-5. Sieht nach Index-Bloat aus. Aber 135 Blöcke von Disk zu lesen sollte auch schneller gehen. Sind das noch MFM-Platten?
Was stehen den für Werte in object_reference? Stichwort wäre ‘Selectivität’
Was ist das Hauptkriterium für die Einschränkung, immer object_reference? Wird da immer auf Gleichheit abgefragt?
Ob da unbedingt GIN angesagt ist, weiß ich nicht.
Ein klassischer Index würde dann über object_reference, timestamp gemacht, nicht andersrum.
Welche Abfrage (Kriterien) werden auf den Mio Datensätzen überhaupt benötigt? Immer alles?
Oder nur Bereiche? Dann käme ggF. auch ein (oder mehrere) partial index in Frage, klein und flott.
Oder Partitioning.
also die select die darauf laufen sind
gruppierungen
select count(*), sum(value1), object_referenz from pc
where object_referenz = ‘asdfsdaf#adsfafds#afd#’
and ts >= ‘date1’
and ts < ‘date2’
und so range abfragen
select ts, object_referenz, value1… from pc
where object_referenz = ‘asdfsdaf#adsfafds#afd#’
and ts >= ‘date1’
and ts < ‘date2’
und noch ein fall wäre (immer mit dem kleinsten datum --> könnte man das mit einer window function besser schreiben?)
select ts, object_referenz, value1,
(select min(ts) from pc pc_in
where pc_in.object_referenz = pc.object_referenz) min_ts
from pc
where object_referenz = ‘asdfsdaf#adsfafds#afd#’
and ts = ‘date1’
select ts, object_referenz, value1,
(select min(ts) from pc pc_in
where pc_in.object_referenz = pc.object_referenz) min_ts
from pc
where object_referenz = ‘asdfsdaf#adsfafds#afd#’
and ts >= ‘date1’
and ts < ‘date2’
vielfach ist der von bis range der letzte Monat aber nicht immer
wäre es sinnvoll da nun 2 Indexes zu machen
der einfach alles beinhaltet und einen
mit where clause
die Werte in object_reference sind pro tag rund 100000 verschiedene
und können von tag zu tag variieren
pro tag und object_referenz gibt es aber immer genau 1 eintrag
also ich sehe das es über indexes verfügt aber im falle von diskreads einfach sacklangsam wird
Mmh, also die Mehrheit der Abfragen, die Du aufgeführt hast, hängen am Datum.
Ich habe mit solchen Datenmengen unter PG keine praktische Erfahrung.
Aber im Prinzip:
Ist das wirklich ein echter Timestamp oder “nur” ein Datum?
Hast Du mal versucht, allein das Datum btree/klassisch zu indizieren? Oder wenigstens Datum und Referenz getrennt?
Im Zweifel sind getrennte Indizes flexibler nutzbar, kommt auf die Schlauigkeit des Optimizer an.
M.E. bringt das technisch nichts, die gemeinsam zu indizieren. Bzw. es bringt nur etwas, wenn genau die Feldkombi auch in der Where Clause genutzt wird.
Ein reiner Datumindex dürfte deutlich kleiner sein und viel schneller durchsuchbar.
Einschränkungen auf einen/wenige Tag müssten damit recht flott sein.
Großvolumige Ergebnisse, die nach einer Datumseinschränkung weiter einzudampfen oder zu aggregieren sind, bleiben natürlich ein Problem, ggF. hilft Partitionierung /partielle Indizierung.
Wenn Du GIN nutzt, dann vielleicht mit gin_trgm_ops. Dabei hängt der Erfolg allerdings von der Art der Where Condition ab.
P.S.: Probier erstmal getrennte, klassische Indizes. Vor allem wenn Du nur exakte Gleichheit auf der Referenz Column brauchst.
die Datenmengen sind peanuts. Ein Index über beide Spalten macht Sinn bei den genannten Abfragen. 2 Einzelindexe würden auch gehen, wäre dann ein Bitmap Index Scan, wäre aber langsammer als ein kombinierter Index.
Partitionierung macht nur sinn, wenn die einzelnen Partitionen wenigstens eine 2-stellige Millionenanzahl von Datensätzen hat, ich glaube, in der Liega bist Du noch nicht. Ein partieller Index auf nur die letzten N Tage kann sinnvoll sein, der müßte aber dann täglich erneuert werden.
GIN-Indexe sind hier sehr sicher fehl am Platz, die braucht man für komplexere, zusammengesetzte Datentypen, Arrays und so.
Evtl. könnte es sich lohnen, die Subselects umzuschreiben, sollte auch via JOIN gehen. Um das zu testen wurde EXPLAIN ANALYSE ja zum Glück schon erfunden.
Stimmt, die Subselects hab ich gar nicht beachtet, würde ich auf jeden Fall in die From Clause packen und separat where clausen und dann joinen.
Aber ich hab doch irgendwoher den Gedanken gehabt, dass auch nach beiden Kriterien einzeln eingeschränkt wird.
Zumindest im ersten Post wird nur nach object referenz gefiltert. Dann hilft ein kombinierter BTree entweder nur im einen oder anderen Fall. Getrennte scheinen mir dann sinnvoller.
20 GB Indexgröße find ich nicht mehr unbedingt peanuts, aber meinetwegen.
Vielleicht ist ja der Spaltentyp für object_referenz unnötig großzügig. Könnte auch Indexsize sparen, wenn man den minimiert.
ich denke schon das man kombinierte Indexes verwenden sollte
jedoch habe ich auch schon gehört das nach einem Range nur noch Filter auf die nächsten Felder angewendet werden können dh das
bei einem where timestamp > date1 and timestamp < date2 and object_referenz =‘adfadfs’ bei einem index
(timestamp, object_refernez) den 2. teil des Indexes gar nicht mehr verwendet werden kann
der select ist noch viel langsamer geworden
ich denke er macht als
zuerst für alle object_refernez den min timestamp
und joint erst dann für alle refernezen vom 01.01.2017
somit macht er ja eigentlich viel zu viel was im schlusseffekt überflüssig ist
explain (analyze, buffers) select pc.ts, pc.object_referenz,
pc_min.min_ts
from pc,
(
select min(timestamp) min_ts, object_referenz
from pc
group by object_referenz
) pc_min
where pc_min.object_referenz = pc.object_referenz
and pc.timestamp = ‘2017-01-01’;
select distinct on (ts, object_referenz) ts, object_referenz, timestamp from pc where timestamp = '2017-01-01' order by ts, object_referenz, timestamp asc;
@infromi: Ja, sorry, ich hab nicht aufgepasst @akretschmer: nicht getestet aber cool, kannte ich nicht (ich geh mal davon aus, das ts für Spalte timestamp steht)
@akretschme:
Ich habe nur zitiert und da ist von min_ts als Alias die Rede.
distinct on ():
Klar postgres, hab mir allerdings nicht die Mühe gemacht, es mit anderen System/Ansi Standards zu vergleichen.
Und SQL ANSI ist mir erst mal egal, man muss halt wissen, wie/wo man es einsetzt.