Tabellenlayout

Gestalten Sie die Tabelle neu und speichern Sie die Öffnungszeiten (Betriebsstunden) als eine Gruppe von tsrange (Bereich der Zeitmarke ohne Zeitzone) Werte. Benötigt Postgres 9.2 oder höher .

Wählen Sie eine zufällige Woche, um Ihre Öffnungszeiten zu planen. Ich mag die Woche:
1996-01-01 (Montag) bis 1996-01-07 (Sonntag)
Das ist das letzte Schaltjahr, in dem der 1. Januar zufällig ein Montag ist. Aber es kann jede zufällige Woche für diesen Fall sein. Sei nur konsequent.

Installieren Sie zuerst das zusätzliche Modul btree_gist . Warum?

Erstellen Sie die Tabelle wie folgt:

Die one Spalte hours ersetzt alle Ihre Spalten:

Beispielsweise werden die Betriebsstunden von Mittwoch, 18:30 bis Donnerstag, 05:00 UTC wie folgt eingegeben:

Die Ausschlussbedingung hoo_no_overlap verhindert überlappende Einträge pro Shop. Es wird mit einem GiST-Index implementiert, der auch Ihre Abfrage unterstützt. Betrachten Sie das Kapitel "Index und Leistung" , in dem die Indexierungsstrategie erläutert wird.

Die Check-Einschränkung hoo_bounds_inclusive erzwingt inklusive Grenzen für Ihre Bereiche, mit zwei bemerkenswerten Konsequenzen:

• Ein Zeitpunkt, der genau auf die untere oder obere Grenze fällt, ist immer enthalten.
• Benachbarte Einträge für den gleichen Shop sind nicht zulässig. Mit inklusiven Grenzen würden sich diese "überschneiden" und die Ausschlussbeschränkung würde eine Ausnahme auslösen. Benachbarte Einträge müssen in einer einzelnen Zeile zusammengeführt werden. Außer wenn sie Sonntag Mitternacht umhüllen, in diesem Fall müssen sie in zwei Reihen aufgeteilt werden. Siehe Tool 2 unten.

Die Check-Einschränkung hoo_standard_week erzwingt die äußeren Grenzen der Staging-Woche mit dem " Bereich ist enthalten durch "operator <@ .

Bei einschließlich -Begrenzungen müssen Sie einen Sonderfall beachten, bei dem die Zeit um Sonntag Mitternacht herumgeht:

Sie müssen beide Zeitstempel gleichzeitig suchen. Hier ist ein ähnlicher Fall mit exklusiver Obergrenze, der diesen Mangel nicht aufweist:

• Verhindern angrenzender / überlappender Einträge mit EXCLUDE in PostgreSQL

Funktion f_hoo_time(timestamptz)

Um eine gegebene timestamp with time zone zu normalisieren:

Die Funktion akzeptiert timestamptz und gibt timestamp zurück. Es fügt das verstrichene Intervall der jeweiligen Woche ( - date_trunc('week', ) in UTC-Zeit (!) Zum Startpunkt unserer Staging-Woche hinzu. ( date + interval erzeugt timestamp .)

Funktion f_hoo_hours(timestamptz, timestamptz)

Um Bereiche zu normalisieren und diese zu teilen, die Mon 00:00 kreuzen. Diese Funktion benötigt ein beliebiges Intervall (als zwei timestamptz ) und erzeugt ein oder zwei normalisierte tsrange -Werte. Es umfasst beliebige legale Eingabe und verbietet den Rest:

To INSERT a einzelne Eingabezeile:

Dies führt zu zwei Zeilen, wenn der Bereich um Mo 00:00 geteilt werden muss.

To INSERT mehrere Eingabezeilen:

Über den impliziten LATERAL Join:

• Was ist das? Unterschied zwischen LATERAL und einer Unterabfrage in PostgreSQL?

Abfrage

Mit dem angepassten Design kann Ihre gesamte große, komplexe, teure Abfrage durch ... ersetzt werden:

  

SELECT *
FROM hoo
WHERE hours @> f_hoo_time(now());

_{Für ein wenig Spannung habe ich eine Spoilerplatte über die Lösung gelegt. Bewegen Sie die Maus über .}

Die Abfrage wird durch den GiST-Index und schnell unterstützt, sogar für große Tabellen.

SQL Fiddle (mit weiteren Beispielen).

Wenn Sie die Gesamtöffnungszeiten (pro Geschäft) berechnen möchten, hier ist ein Rezept:

• Arbeitsstunden zwischen zwei Daten in PostgreSQL berechnen

Index und Leistung

Der Begrenzungsoperator für Bereichstypen kann mit GiST oder SP-GiST index. Beide können verwendet werden, um eine Ausschlussbeschränkung zu implementieren, aber nur GiST unterstützt mehrspaltige Indizes :

  

Derzeit unterstützen nur die B-Tree-, GiST-, GIN- und BRIN-Indextypen mehrspaltige Indizes.

Und die Reihenfolge der Indexspalten ist wichtig :

  

Ein mehrspaltiger GiST-Index kann mit diesen Abfragebedingungen verwendet werden   Beteiligen Sie jede Teilmenge der Spalten des Index. Bedingungen für zusätzliche   Spalten beschränken die vom Index zurückgegebenen Einträge, aber die Bedingung   auf der ersten Spalte ist der wichtigste für die Bestimmung, wie viel   des Index muss gescannt werden. Ein GiST-Index wird relativ sein   unwirksam, wenn seine erste Spalte nur einige eindeutige Werte aufweist   wenn es viele verschiedene Werte in zusätzlichen Spalten gibt.

Wir haben also widerstreitende Interessen hier. Bei großen Tabellen gibt es viel mehr unterschiedliche Werte für shop_id als für hours .

• Ein GiST-Index mit dem führenden shop_id ist schneller zum Schreiben und zum Erzwingen der Ausschlussbeschränkung.
• Aber wir suchen die Spalte hours in unserer Abfrage. Die erste Spalte wäre besser.
• Wenn wir in anderen Abfragen shop_id nachschlagen müssen, ist ein einfacher btree-Index viel schneller dafür.
• Um das Ganze abzurunden, habe ich einen SP-GiST Index für nur hours gefunden, der für die Abfrage am schnellsten sein soll.

Benchmark

Mein Skript zum Erzeugen von Dummy-Daten:

Ergebnisse in 141k zufällig generierten Zeilen, 30k distinct shop_id , 12k distinct hours . (In der Regel ist der Unterschied größer.) Tabellengröße 8 MB.

Ich habe die Ausschlussbeschränkung gelöscht und neu erstellt:

shop_id ist zuerst ~ 4x schneller.

Zusätzlich habe ich zwei weitere für die Leseleistung getestet:

Nach VACUUM FULL ANALYZE hoo; habe ich zwei Abfragen ausgeführt:

• Q1 : spät in die Nacht, finde nur 53 Zeilen
• Q2 : Nachmittags finden Sie 2423 Zeilen .

Ergebnisse

Ich habe einen index-only-Scan für jeden (außer natürlich für "no index"):

• SP-GiST und GiST sind gleichauf für Anfragen, die wenige Ergebnisse finden (GiST ist noch schneller für sehr wenige).
• SP-GiST skaliert besser mit einer wachsenden Anzahl von Ergebnissen und ist auch kleiner.

Wenn Sie viel mehr lesen als Sie schreiben (typischer Anwendungsfall), behalten Sie die Ausschlussbeschränkung, wie zu Beginn vorgeschlagen, und erstellen Sie einen zusätzlichen SP-GiST-Index, um die Leseleistung zu optimieren.