indexes auf x mio grossen tabellen

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?

Es gibt ein Vorgehen in der Programmierung.
Je weniger Werte zu handhaben sind, desto “luxuriöser” darf der Typ der Variable sein.
Bsp. ein Zähler in einer Schleife. Den gibt es nur einmal. Ich kann mir stundenlang den Kopf zerbrechen, einen idealen Typ zu finden. Groß genug, damit der Algorithmus nicht gegen die Wand rennt, aber so klein wie möglich, um nicht unnötig Speicherplatz zu vergeuden.
Es ist nicht unbedingt sinnvoll, mit ein paar Byte mehr bin ich auf der “sicheren Seite”, die Zeit darüber nachzudenken ist fast verschenkt.

In diesem Fall hier reden wir von dem Gegenteil.
Allein der Thread Titel sagt x Millionenfaches Auftreten des Wertes. Das kann man jetzt ausmultiplizieren mit dem Speicherplatz eines Datetypes oder einem Timestamp Types.
Timestamp braucht doppelt soviel Speicher wie Date.

Eine reine Volumenbetrachtung sagt also:
Hättest Du den passenden Typ genommen, könntest Du im gleichen Volumen, die doppelte Menge Daten speichern.
Indexgröße käme noch dazu.

Oder anders:
Jeder Bufferblock, der Daten dieser Tabelle enthält und cached, enthält nur halb so viel Daten wie möglich wären. Cache hilft nur, wenn er die passenden Daten enthält, die Wahrscheinlichkeit, dass das der Fall ist, ist also halbiert.

Diese Betrachtungen sind relativ abstrakt und verkürzt, aber hoffentlich verständlich.

Table “homegate.test1”
Column | Type | Modifiers
-----------------±-----±----------
tstamp | date |
object_referenz | text |


Table “homegate.test2”
Column | Type | Modifiers
-----------------±-----±----------
tstamp | timestamp|
object_referenz | varchar(300) |

in beide Tabellen die gleichen 100 000 000 rows inserted


SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = ‘r’
and relname in (‘test1’, ‘test2’)
) a
) a;
oid | table_schema | table_name | row_estimate | total_bytes | index_bytes | toast_bytes | table_bytes | total | index | toast | table
-----------±-------------±-----------±-------------±------------±------------±------------±------------±--------±--------±-----------±--------
373985765 | homegate | test2 | 9.99381e+07 | 6025355264 | 0 | | 6025355264 | 5746 MB | 0 bytes | | 5746 MB
373985759 | homegate | test1 | 1.0001e+08 | 5480660992 | 0 | 8192 | 5480652800 | 5227 MB | 0 bytes | 8192 bytes | 5227 MB


okay des test2 tabelle ist leicht kleiner


dann habe ich den gleichen test nochmals gemacht mit nur dem date/timestamp feld wieder mit den 100 000 000

Table “homegate.test1”
Column | Type | Modifiers
-----------------±-----±----------
tstamp | date |

Table “homegate.test2”
Column | Type | Modifiers
-----------------±-----±----------
tstamp | timestamp|

SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = ‘r’
and relname in (‘test1’, ‘test2’)
) a
) a;
oid | table_schema | table_name | row_estimate | total_bytes | index_bytes | toast_bytes | table_bytes | total | index | toast | table
-----------±-------------±-----------±-------------±------------±------------±------------±------------±--------±--------±------±--------
373985765 | homegate | test2 | 0 | 3624812544 | 0 | | 3624812544 | 3457 MB | 0 bytes | | 3457 MB
373985759 | homegate | test1 | 0 | 3624779776 | 0 | | 3624779776 | 3457 MB | 0 bytes | | 3457 MB
(2 rows)


jetzt sind beide Tabellen ungefährt gleich gross
also waren beim ersten vergleich der grössere Platzverbrauch wegen dem 2. Feld das statt varchar(300) --> text war?

denn nun sind ja beide Tabellen gleich gross
das würde ja deiner Aussage widersprechen? oder sehe ich da was falsch?
Besten Dank

Die reine Mathematik mit den Typengrößen ist erstmal etwas enttäuschend.
Es gibt eine Reihe von Effekten, die Overhead produzieren, je auffälliger, um so weniger Spalten.

Wenn Du Dein Experiment auf jeweils 2 Spalten gleichen Typs erweiterst, dürfte sich ein Unterschied bemerkbar machen. Erst Recht mit Index (allerdings auch nur, wenn der Index mehrere Spalten umfasst, sonst gibt es da wieder “Effekte”)
Das Ganze ist hinreichend kompliziert, es gibt Experten, die das aus dem Stand können oder mindestens die richtigen Abfragen und Tricks kennen. Teile der Effekte erklären sich z.B. dadurch, dass pg die 8 mag (typabhängig unterschiedlich). 2 Typen, die zusammen 8 Byte brauchen, könnten als tatsächlich auch nur 8 Byte belegen.
Ich hab mich um die tatsächlichen Implementierungsdetails an der Stelle bei PG noch nie gekümmert. Es war unnötig. Wie auch immer, wer mit 100 Mio Records + arbeitet, darf sich das gerne anschauen und wird, beginnend mit sparsamer Typdefinition einiges an Optimierungsmöglichkeiten finden.
Das rechnet sich, von Tabellengröße über Index bis Backup.
Unabhängig davon ist es sträflich, durch “mangelhafte” Typdefinition Unregelmäßigkeiten in den Abfragen zu riskieren. Eine Datespalte, in die Du “versehentlich” timestamp Werte füllst, wird sich sofort beklagen. Eine Timestampspalte wird das auf ewig schlucken und irgendwann merkst Du vielleicht, dass Deine Abfragen Blödsinn ergeben, weil sie nicht für Timestamp Werte gemacht sind.