indexes auf x mio grossen tabellen

Hallo Cumminity

meine Tabelle table_a hat x Mio of records

timestamp, referenz

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

Besten Dank

das das explain analyse dazu.

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?

ja mich dünkt auch es sei mega lahm beim lesen von disk
handelt sich um ein netapp

wenn ich mit pgstattuple schaue wegen tabelbloat/indexbloat dann hat es schon ein wenig

homegate_prod=# select * from pgstattuple.pgstattuple(‘i_pc_obj_adv’);
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-------------±------------±------------±--------------±-----------------±---------------±-------------------±-----------±-------------
23038369792 | 336061242 | 15885155232 | 68.95 | 201958 | 9695624 | 0.04 | 5320117932 | 23.09
(1 row)

aber so langsam sollte das ja wirklich nicht sein und der index wird wohl nachher statt 20GB dann 15GB

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

  1. der einfach alles beinhaltet und einen
  2. 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.

besten Dank für eure Antworten

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

habe mal den select umgeschrieben
das problem

der select ist noch viel langsamer geworden
ich denke er macht als

  1. zuerst für alle object_refernez den min timestamp
  2. 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’;

Tja. Schade nun, aber ohne die Ausgabe von Explain analyse zu sehen tappen hier alle weiter im Nebel.

wenn ich mit Deine Abfrage anschaue, denke ich,

 
select distinct on (ts, object_referenz) ts, object_referenz, timestamp from pc where timestamp = '2017-01-01' order by ts, object_referenz, timestamp asc;

sollte dasselbe ich kürzerer Zeit liefern.

@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)

du hattest in Deinem SQL auch solche eine Spalte: “select pc.ts”, mir ist aber nicht bekannt, wofür das bei Dir steht :wink:

Btw.: distinct on (…) ist eine PostgreSQL-Erweiterung und nicht SQL-konform. Aber cool :wink:

@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.

Was steht denn in Deinen Timestamps drin?
Ein wirklicher Timestamp oder ein “nacktes” Datum?

Die Parameter in Deinen Beispielen deuten auf bloße Datumswerte hin.

ein datum

gute Frage

ist momentan mal so
sonst müsste man da halt mal refactoren
meinst du das macht extrem viel auf die Performance aus?