Nach seinen Aussagen waren es ‘früher’ 10 Sekunden. Da wäre mal nett zu wissen, was explain analyse von ‘früher’ dazu meint…
Vielleicht einfach nur massig tote Tupel, aber man sollte das mal untersuchen, damit die neue DB nicht irgend wann wieder so lange braucht. Einfach Hardware auf ein Problem zu werfen geht nicht ewig lange gut.
den stand von früher kann ich mir demnächst erst anschauen wenn nen altes backup für mich eingespielt wird.
das problem früher war eine abfrage auf eine Tabelle mit 25 mio datensätzen, keine Partitionierung, schlechte Indizes und ein gnadenlos überforderter Server.
Habe ich bereits genutzt und danach macht er index scans… also nutzt er definitiv die Indizes
Bitmap Heap Scan on filesysdata_200901 filesysdata (cost=97.52..5562.35 rows=5831 width=20) (actual time=2.694..76.238 rows=6002 lo
ops=1)
Recheck Cond: (fsid = 5)
-> [B]Bitmap Index Scan on filesysdata_200901_fsid_idx[/B] (cost=0.00..96.06 rows=5831 width=0) (actual time=1.758..1.758 rows=6002 loo
ps=1)
und er durchsucht auch die Partitionen, jedoch immer alle.
Checks habe ich bei jeder Partition drinne, bis eben war es nur in der Config noch auskommentiert.
Fals du damit meinst, dass ich oben Quote anstatt Code genommen habe. Habe mich einfach verklickt
Solltest du was anderes meinen, dann keine Ahnung was.
Und wie bringe ich ihn dazu nur die korekte Partition zu durchsuchen?
Sind meine Checks vielleicht falsch aufegbaut oder muss noch etwas anderes in der Config aktiviert werden?
Dazu dient ja das constraint_exclusion, da müssen natürlich die Abfragen mit ihren WHERE-Bedingungen zu den Check-Constraints der Partitionierung passen.
Ist jetzt schwer, via Ferndiagnose das zu analysieren, ich kenne ja auch das select nicht oder finde es nicht im Thread…
Also genau stimmen die Where und Check Constraints nicht überein, denn meine Abfragen holen z.B. nur Daten der letzten 60 min aus der aktuellen Patition (bzw. sollten es nur aus der aktuellen holen, er durchsucht ja leider immer alle).
Select aus meinem Cron der alle 60min läuft
Select fsfree, fsused, fstime from filesysdata where fsid = 1 and
date(fstime) <= date('2009/07/01 08:00:00') and
date(fstime) >= date('2009/07/01 07:00:00')
ORDER BY fstime DESC"
Mit den Daten werden dann paar Berechnungen gemacht und in andere Tabellen gelegt.
Könnte es dann daran liegen? Und würde es Sinn machen meine Monats Partitionen auf Stunden Partitionen zu “verkleinern”? Kann mir nicht vorstellen, dass der Nutzen daraus so groß sein könnte das sich der Aufwand lohnt.
habe gerade festgestellt, dass bei anderen Tabellen die Partitionen genutzt werden
monitoring=> explain analyze select * from fs_hourly where datum >= date('2008/05/01') and datum <= date('2008/05/02') and fsid = 250;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..79.81 rows=25 width=48) (actual time=0.136..0.273 rows=24 loops=1)
-> Append (cost=0.00..79.81 rows=25 width=48) (actual time=0.133..0.214 rows=24 loops=1)
-> Index Scan using fs_hourly_fsid_idx on fs_hourly (cost=0.00..8.28 rows=1 width=48) (actual time=0.092..0.092 rows=0 loops=1)
Index Cond: (fsid = 250)
Filter: ((datum >= '2008-05-01'::date) AND (datum <= '2008-05-02'::date))
-> Bitmap Heap Scan on fs_hourly_250 fs_hourly (cost=4.50..71.54 rows=24 width=48) (actual time=0.037..0.067 rows=24 loops=1)
Recheck Cond: ((datum >= '2008-05-01'::date) AND (datum <= '2008-05-02'::date))
Filter: (fsid = 250)
-> Bitmap Index Scan on fs_hourly_250_datum_idx (cost=0.00..4.49 rows=24 width=0) (actual time=0.022..0.022 rows=24 loops=1)
Index Cond: ((datum >= '2008-05-01'::date) AND (datum <= '2008-05-02'::date))
Total runtime: 0.877 ms
(11 rows)
monitoring=> \d fs_hourly_250
Table "nagios.fs_hourly_250"
Column | Type | Modifiers
-----------+-----------------------------+-----------
fsid | integer |
datum | timestamp without time zone |
mean_used | double precision |
sum_used | bigint |
mean_free | double precision |
sum_free | bigint |
n | integer |
Indexes:
"fs_hourly_250_datum_idx" btree (datum)
"fs_hourly_250_fsid_idx" btree (fsid)
Check constraints:
"fs_hourly_250_fsid_check" CHECK (fsid = 250)
"fs_hourly_n_check" CHECK (n > 0)
Inherits: fs_hourly
das heißt dann wohl, dass meine Partitionen auf zu der Tabelle filesysdata schlecht gewählt sind mit den Zeitbegrenzungen. Vielleicht hätte ich diese auch nach den ID’s erstellen sollen
Edit
Ach ja, Du hast nun constraint_exclusion eingeschaltet UND den Server neu gestartet bzw. Reload (sollte reichen)?
habe es in der Config auf on gestellt und einmal “pg_ctl restart” gemacht, wenn du das meinst.
select ... from fs_hourly where [color="#4169e1"][B]datum[/B][/color] >= date('2008/05/01') and datum <= date('2008/05/02') and fsid = 250;
Was fällt Dir auf?!
Eine Partitionierung wählt man nicht irgendwie - wenn Deine Daten sich zeitlich gut abgrenzen lassen - also du z.B. mit “alten” Daten anders umgehen willst als mit “neuen” - dann machen Partitions nach datum/zeit durchaus sinn.
Blieb also nur die Frage, warum die Partitionierung nicht so funktioniert, wie erwartet - siehe oben.
solltest du schon das selbe feld deines check-constraint selektieren
sollte der ausdruck des constraint möglichst mit dem ausdruck in der select-bedingung uebereinstimmen … wenn dein constraint also date(fstime) >= … heisst, dann solltest du auch nach date(fstime) … eingrenzen. So kann postgres feststellen, dass der constraint ueberhaupt zu verwenden ist.
wenn es dann immernoch nicht funzt:
3. schauen ob du nicht andere indizes hast, die den selben ausdruck wie den des check-constraint enthalten - ich gehe aber mal davon aus, dass die partition-constraints bevorzugt werden - wie es sein sollte (da bin ich mir aber nicht sicher)
Wenn du dir die Statements nochmal genau ansiehst, dann fällt dir auf, dass es grad um zwei verschiedene Tabellen geht.
einmal die filesysdata in der die Spalte fstime heißt und die Partitionen nicht funktionieren. Partitionen eingeteilt nach Zeit.
und die andere Tabelle ist fs_hourly, dort heißt die Spalte datum (beim erstellen nicht aufgepasst und daher anders genannt) und die Partitionen funktionieren. Partitionen eingeteilt nach ID’s
Bei den Abfragen zu den Tabellen werden schon die richtigen Spalten genutzt, sonst würde die Abfrage ja nicht laufen.
Select fsfree, fsused, fstime from filesysdata where fsid = 1 and
date(fstime) <= date('2009/07/01 08:00:00') and
date(fstime) >= date('2009/07/01 07:00:00')
ORDER BY fstime DESC"
möglicher weise meint PG, dass fsid in allen partitionen zu selektieren billiger ist, als die partitions zu beleuchten … was ja durchaus auch richtig sein kann …
was sagt denn das explain dazu wenn du es ohne die fsid = 1 absetzt
ein weiterer schwachpunkt KÖNNTE sein, dass die rechte seite des ausdrucks unterschiedlich ist (in select und constraint) … wobei ich das eher unwahrscheinlich (aber nicht fuer unmoeglich) halte
Nein - dass sich PG womöglich durch <= ‘2009-06-27’::date (ein cast) und <= date(‘2009/06/27 07:00:00’) (eine funktion) verwirren laesst … der typ ist bei beiden sicher date - aber man weiss ja nie …
also ich meine - es koennte hilfreich sein nach
date(feld) <= date(‘…’) zu selectieren, wenn das constraint auch diese form hat date(feld) <= date(‘…’)
Jedoch habe ich was anderes interessantes entdeckt.
Bei den Abfragen mein crons
Select fsfree, fsused, fstime from filesysdata where fsid = 500 and
date(fstime) <= date('2009/06/27 08:00:00') and
date(fstime) >= date('2009/06/27 07:00:00')
ORDER BY fstime DESC;
werden die Partitionen genutzt, egal ob im where noch ein fsid=1 drin steht oder nicht.
jedoch nicht bei der Abfrage
SELECT fsused, fsfree from filesysdata
where fsid = 550 order by fstime desc limit 1;
dann wird der constraint / partitions also prinzipiell so genutzt wie man es erwartet - scheint dann doch eine “kostenfrage” zu sein ?!?
jedoch nicht bei der Abfrage
SELECT fsused, fsfree from filesysdata
where fsid = 550 order by fstime desc limit 1;
>
> nicht. Verstehen tue ich das nicht > :smiley: >
[edit]gelöscht
aeh - da ist wieder ein where fsid = ... drin ... natuerlich muss er da in alle partitionen schauen .. denn erst wird alles mit dieser condition gewählt und erst danach sortiert .. trotzdem kann es ncht schaden nach date(fstime) zu sortieren ..
der witz ist ja, dass die partition nach datum nur was bringen, wenn du auch in deinen queries entsprechend die partition “wählst” indem du eine bedingung verwendest, die den partition-constraint berücksichtigen …
so ist das ja acuh gewollt … wenn du die bedingung NICHT angibst, dann ist davon auszugehen, dass alle daten gemeint werden …
dein order by mag dabei grenzwertig sein - da weiss ich gerade nicht wie schlau Postgres an der stelle ist - in deinem beispiel spielt aber auch das keine rolle, weil VORHER ja erstmal die fsid selektiert werden soll, die ja in allen Partitionen auftreten kann.