Select * von großen Tabellen > 8 Mio. Zeilen uferlos langsam

Hallo

ich habe ca. 10 Tagestabellen folgender Struktur mit jeweils einem Index auf der Spalte uniqueid.

CREATE TABLE “qa3”.“signal_pred_170612”
(
komkid numeric(38) PRIMARY KEY NOT NULL,
uniqueid int,
herkunft char(3),
endtime timestamp,
interval int,
received timestamp,
creation timestamp DEFAULT now(),
cycle_time int,
sec_offset int,
distribution varchar(4000)
);
CREATE INDEX ix_signal_pred1_170612 ON “qa3”.“signal_pred_170612”(uniqueid);
CREATE INDEX ix_signal_pred2_170612 ON “qa3”.“signal_pred_170612”(endtime);
CREATE UNIQUE INDEX pk_signal_pred_170612 ON “qa3”.“signal_pred_170612”(komkid);

Ich möchte viele selects folgender Art durchführen:
select * from signal_pred_170612 where uniqueid = 320001

Dieses Select benötigt ca. 7 Sekunden.

vacuum bringt keinen Geschwindigkeitsvorteil.
cluster dauert auf einer Tabelle Stunden.

Gibt es in Postgres eine Möglichkeit, die select query zu beschleunigen?

Was sind “Tagestabellen”? Machst Du Partitionierung? Ist das Feld uniqueid unique? Der extra Index auf komkid ist sinnfrei, da bereits als PK definiert.
Was sagt EXPLAIN (ANALYSE)?

numeric als PK ist, ähm, … keine gute Idee, Datentypen von interval, cycle_time und sec_offset möglicherweise auch nicht optimal, dazu müßte man aber mehr über den Sinn dieser Spalten wissen.

Für jeden Tag gibt es eine Tabelle der Form signal_pred_yymmdd. Das ist also keine klassische Partitionierung im Sinn von Postgres. Das Feld uniqueid ist nicht unique.

explain analyze select * from signal_pred_170613 where uniqueid = 320001

ergibt folgendes:

QUERY PLAN

Bitmap Heap Scan on signal_pred_170613 (cost=25.1
Recheck Cond: (uniqueid = 320001)
Heap Blocks: exact=775
-> Bitmap Index Scan on ix_signal_pred1_170613
Index Cond: (uniqueid = 320001)
Planning time: 0.118 ms
Execution time: 13.750 ms

Execution time 13.7ms, das ist etwas weniger als die von Dir genannten 7 Sekunden.

8 Millionen Zeilen / Tabelle ist NIX. gar nix.

Naja, ich habe zuvor auch folgendes ausgeführt:
cluster ix_signal_pred1_170612 on signal_pred_170612
Auf das Ergebnis habe ich Stunden gewartet.

übrigens ist das keine vollständige Ausgabe von EXPLAIN ANALYSE, da fehlen die Schätzwerte und die realen Werte.

Die Tabelle erhält nach dem Ablauf des Tages keine Updates und Inserts.
Alle Tabellen des aktuellen Tages in dem Tablespace in dem diese Tabelle gespeichert ist jedoch schon.
Ich habe ca. 10 solche Tabellen für je einen Tag.

Ich habe das explain analyze für eine Tabelle ausgeführt, dessen Index ich nicht mit cluster neu organisiert habe:
explain analyze select * from switchingtime_170613 where uniqueid = 320001

QUERY PLAN

Bitmap Heap Scan on switchingtime_170613 (cost=40
Recheck Cond: (uniqueid = 320001)
Heap Blocks: exact=485
-> Bitmap Index Scan on pk_switchingtime_170613
Index Cond: (uniqueid = 320001)
Planning time: 14.593 ms
Execution time: 1530.458 ms


Mehr Ausgaben erhalte ich dafür nicht.
select count(*) from switchingtime_170613
Dauert 32 Sekunden und zählt 10133093 Einträge
select * from switchingtime_170613 where uniqueid = 815012
Dauert 2,25 Sekunden.

Die Tabelle switchingtime ist sehr ähnlich wie die Tabelle signal_pred aufgebaut.
Enthält am Ende des Tages jedoch ca. 15 Mio. Einträge.

Allerdings scheine ich im Moment nach der Ausführung von Cluster auf der Tabelle oben insgesamt schnellere Ergebnisse zu erhalten.

Was ist das für eine Version? Explain analyse gibt deutlich mehr Informationen, z.B. Anzahl geschätzter Rows, aktuelle Timing-Werte, Anzahl tatsächlicher Rows.

wie viel Zeilen liefert das SQL denn? Du machst ein select *, damit bekommst Du natürlich alle Felder. Eines der Felder ist relativ groß, varchar(4000). Falls das Resultset recht groß ist, ist die Menge dessen, was zu übertragen ist, natürlich entsprechend groß. Da hast dann zum einen eine gewisse Netzwerklast - selbst wenn es lokal auf derselbe Maschine passiert. Und dann hast Du ja sicherlich eine Anwendung hinten dran, die passend Speicher allokieren muß, Daten umkpieren etc. Falls Du das Feld distribution gar nicht brauchst, so lasse es weg. Das spart dann sehr wahrscheinlich das Scannen der entsprechenden Toast-Tabellen.

Also, die DB braucht möglicherweise (siehe explain, execution time) nicht sehr lange, das Problem liegt woanders. Mehr kann man momentan aufgrund der dürftigen Faktenlage aber nicht sagen.

Die verwendete Postgres-Version ist 9.6 auf Windows Server 64 Bit mit 4 GB Hauptspeicher und 2,4 GHz Xeon CPU
Die gleichen Abfragen mache ich schon mit Oracle-Tabellen, die mit organized Indexes erstellt werden.
Dort dauert genau die selbe Query unter 1 ms.
Genauer: der scan von 15,8 Mio Zeilen mit der Query
select * from switchingtime_170613 where uniqueid = 815012;
Liefert

unter Oracle
2226 Zeilen in 0,17 Sekunden aus.

Unter Postgres dauert die gleiche Abfrage
4,45 Sekunden.

Das wiederum bedeutet, das meine Berechnungen, mit den Daten in den Tabellen
mit Oracle ca. 1,5 Stunden lang dauern
mit Postgres ca. 3 Stunden, teilweise auch sehr viel länger

Oracle sorgt dafür, daß der Organized Index stets optimal auf dem Datenträger gespeichert wird.
Darum gehen dann die Abfragen so schnell.

Bei Postgres soll man angeblich die Tabellen regelmäßig mit analyze, vacuum oder cluster optimieren,
was aber bei einer großen Datenbank sehr lange dauert. Ich habe gelesen, daß Postgres-Benutzer den Durchlauf
von cluster nach 17 Stunden abgebrochen haben.

Cluster hat zudem den enormen Nachteil, daß die Tabelle komplett gelockt wird.
Es soll angeblich eine Extension mit dem Namen pg_repack geben, was auf python basiert.
Dieses Werkzeug soll angeblich mit minimalen Locks auskommen. Aber erstens ist die Installation
unter Windows nicht so reibungslos zu machen und zweitens hat mein Chef damit ein Vertrauensproblem.
Auch weiß ich nicht, wie gut dieses Werkzeug tatsächlich ist.

Am liebsten wäre es mir, wenn Postgres von Anfang an mit eigenen Werkzeugen mit großen Datenmengen gut umgehen könnte.

Nochmal ein Versuch von explain analyze:

QA3DB=> explain analyze select * from switchingtime_170613 where uniqueid = 1001;
QUERY PLAN

Bitmap Heap Scan on switchingtime_170613 (cost=41.34…6131.78 rows=1649 width=44) (actual time=19.040…3478.983 rows=2038 loops=1)
Recheck Cond: (uniqueid = 1001)
Heap Blocks: exact=705
-> Bitmap Index Scan on pk_switchingtime_170613 (cost=0.00…40.93 rows=1649 width=0) (actual time=11.274…11.274 rows=2038 loops=1)
Index Cond: (uniqueid = 1001)
Planning time: 1.946 ms
Execution time: 3482.512 ms
(7 Zeilen)

Optimal wäre eine Execution time von unter 1 ms so wie in Oracle

Ein paar Gedanken dazu:

  • PG läuft unter Windows, sicher. Aber es läuft insgesamt deutlich besser unter einem *NIX-System
  • hast Du die Config angepaßt?
  • es gibt autovacuum, der für Vacuum und Analyse zuständig ist. Da kann man auch z.B. tabellenspezifisch noch optimieren, möglicherweise wäre dies hier sinnvoll
  • es ist i.d.R. weder sinnvoll noch erforderlich, manuell analyse, vacuum oder cluster auszuführen
  • PostgreSQL kommt auch mit großen Datenmengen gut zurecht, ich kenne bei unsere Kunden Tabellen im mehrstelligen TB-Bereich.
  • pg_repack kenne ich, es gehört aus bestimmten Gründen NICHT zu den von uns supporteten Tools, Dein Chef liegt da ganz gut mit seinem Vertrauen
  • wenn Deine Platte für random-Zugriffe (Bitmap Heap Scan) von ~2000 Rows knapp 3,5 Sekunden braucht dann war die offenbar sehr, sehr preiswert.
  • Oracle braucht 0,17 Sekunden, das sind 170ms, nicht unter 1 ms.

Ich hab mal das versucht nachzustellen. Eine Tabelle mit 20.000.000 (20 Millionen) Zeilen:

test=*# select count(*) from demo2;
  count   
----------
 20000000
(1 Zeile)

test=*# \d demo2
                          Tabelle »public.demo2«
 Spalte |   Typ   |                       Attribute                        
--------+---------+--------------------------------------------------------
 id     | integer | not null Vorgabewert nextval('demo2_id_seq'::regclass)
 u_id   | integer | 
 data   | text    | 
Indexe:
    "demo2_pkey" PRIMARY KEY, btree (id)
    "idx_uid" btree (u_id)

test=*#

und eine ähnlichen Abfrage und einer ähnlich großen (sogar größeren nach Zeilenbreite) Ergebnissmenge:

test=*# explain analyse select * from demo2 where u_id = 4711;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on demo2  (cost=43.65..7540.04 rows=1963 width=170) (actual time=5.437..13.799 rows=1986 loops=1)
   Recheck Cond: (u_id = 4711)
   Heap Blocks: exact=1982
   ->  Bitmap Index Scan on idx_uid  (cost=0.00..43.16 rows=1963 width=0) (actual time=4.829..4.829 rows=1986 loops=1)
         Index Cond: (u_id = 4711)
 Planning time: 0.127 ms
 Execution time: 14.252 ms
(7 Zeilen)

0,014 Sekunden, also mehr als 10 mal schneller als Oracle :wink:

Danke für die Antwort, ich versuche das mal auf einem anderen Server bei uns nachzuvollziehen.

Mittlerweile habe ich die Datenbank von einer VM auf einen realen Rechner verschoben.
IMeine Testzeiten sind nun im Moment schneller als bei Oracle mit organized Index Tabellen.

Die Tagestabellen bearbeite ich mit vacuum, bevor ich sie für die Tests zum Lesen verwende.
Das geht sehr schnell und bringt einen enormen Geschwindigkeitsgewinn.
Im Moment bin ich ca. 10mal schneller als mit Postgres auf der alten VM.

Cluster bringt seltsamer weise in meinem Fall keinen Geschwindigkeitsvorteil.

Als Rechner verwende ich einen WindowServer 2012 128 GB, Xeon E-2630 2,4 GHz 2 CPU, 16 Kerne (logisch 32) mit Raid 5
Darauf läuft Oracle und Postgres

Postgres auf einer Test-VM mit Linux centos, natürlich HW nicht vergleichbar, läuft für 1000 Datensätze in 1 commit 100 ms langsamer.
Nach 3 Tagen war allerdings die Festplatte mit 21 GB voll.

Windows hat den Vorteil von komprimierbaren Ordnern.