Ich arbeite zum ersten Mal mit einer Postgre Datenbank. In einer der Tabellen befinden sich mehrere Millionen Einträge. Ein SELECT * auf diese Tabelle dauert Minuten bzw. überlastet den Rechner so sehr, dass kein Ergebnis mehr kommt. Ist das normal? Muss ich meine Queries optimieren oder sollte sowas eigentlich gehen?
entweder Postgres oder PostgreSQL oder einfach pg.
Du sagst ja auch nicht Microsof oder Appl oder Volkswage
In einer der Tabellen befinden sich mehrere Millionen Einträge. Ein SELECT * auf diese Tabelle dauert Minuten
wenn Du Millionen von Datensätzen anforderst dann wird das auch eine Weile dauern.
Kann denn Deine Anwendung die das SELECT * abschickt überhaupt mit Millionen von Datensätzen umgehen?
überlastet den Rechner so sehr, dass kein Ergebnis mehr kommt.
Welchen Rechner? Der auf dem Postgres läuft, oder der der die Abfrage wegschickt?
Generell gilt: bei solchen Fragen (Performance usw) immer folgendes posten:
Genaue Definition der Tabelle (CREATE TABLE incl. aller Indizes)
Das Statement welches Du verwendest
Idealerweise noch den Execution plan des Statements (den kannst Du mit EXPLAIN anzeigen)
und bitte alles hübsch in
Tags packen, damit man das auch noch vernünftig lesen kann.
Ich möchte an der Stelle auch noch eine weitere Sache anmerken:
Der Datenbankserver muss konfiguriert werden!
Ich gehe mal davon aus, dass dein PostgreSQL-Server frisch installiert ist? Dann nutzt du exakt 32MB deines RAM für Indizies, usw. Übertrieben gesagt gerade soviel, dass der Server läuft, aber nicht mehr! Wahrscheinlich dauert dein Scan deshalb so lange.
Hi, danke für eure Antworten. Sorry, dachte Postgre weil das S in PostgreSQL gross geschrieben ist und im SQL.
Die Tabelle:
CREATE TABLE t_value_time
(
time_id bigint NOT NULL, -- FK aus t_time
mp_id integer NOT NULL, -- FK aus t_mp
current_time_stamp timestamp without time zone,
current_value double precision DEFAULT 0,
current_value_cont double precision DEFAULT 0,
source smallint DEFAULT 0,
CONSTRAINT t_value_time_pkey PRIMARY KEY (time_id, mp_id)
)
Darauf zugreifen wollte ich einfach mal mit:
SELECT * FROM t_value_time
Das dauerte ewig. Meine Anwendung ist in PHP geschrieben. kA ob er noch nicht fertig war oder der Apache nicht mehr klar kam, aber es wurde nach über 10 Minuten noch kein Ergebnis angezeigt.
Meine Frage ist eher so genereller Natur. Was dauert hier so lange, auf mehreren Millionen Records Bedingungen überprüfen, oder mehrere Millionen Records zu liefern? Ist es schneller wenn ich so genau wie möglich SELECTe oder wenn ich möglichst wenig SELECTe?
[edit]
Folgender Query, der sehr spezifisch ist, dauert auch Minuten lang. Die andere Tabelle enthält nur ein paar hundert Einträge.
SELECT mp.current_value FROM t_value_time AS mp, t_og_pv_plant AS pv, t_mp_og AS link WHERE pv.og_id = link.og_id AND pv.og_id = link.og_id AND pv.og_id = '2939'
Ist dieses vergleichen so zeitaufwändig?
PS: Server ist auf 1GB Ram konfiguriert habe ich mir sagen lassen.
Nun, um mehrere Millionen Records aus der DB via PHP auszulesen, ist schon einiges an Arbeit zu leisten. Die DB hat hier einen Full Table Scan zu machen, da wird Speicher allokiert, das ist an PHP zu übergeben, das wandert also den TCP/IP - Stack rauf und runter, PHP hat wieder Speicher zu allokieren und so weiter.
Die Frage, die sich mir stellt, ist: warum machst Du das? Was passiert mit den Daten in PHP?
Wenn du “einfach mal” mehrere Millionen Datensätze ausliest, müssen die aus der Datenbank über das Netzwerk in die Anwendung geschaufelt werden. Zusätzlich müssen beide Seiten genügend Speicher bereitstellen, um die Gesamtmenge der Daten erst mal vorzuhalten. Beim Ausführen der Anfrage werden in der Regel (zumindest in deinem Fall) alle Ergebnisse zum anfragenden Client übertragen, bevor dieser überhaupt weiterarbeiten kann. Du kannst dir vielleicht vorstellen, dass das eine gewaltige Ressourcenverschwendung ist.
Zu deiner mehr spezifizierten Anfrage: poste doch hier bitte mal den Ausführungsplan für diese Anfrage. Den erhälst du, indem du ein EXPLAIN vor die Anfrage setzt. Am besten machst du das in “psql” oder in “pgAdmin III”.
Ok, Millionen auszuwählen ist nicht gut, kapier ich. Deswegen hatte ich ja den spezifischeren Ausdruck probiert, aber auch der kommt zu keinem Ergebnis. Irgendwann hört er einfach auf zu laden und die PHP Seite bleibt weiss.
EXPLAIN SELECT mp.current_value
FROM t_value_time AS mp, t_og_pv_plant AS pv, t_mp_og AS link
WHERE pv.og_id = link.og_id AND pv.og_id = link.og_id AND pv.og_id = '2939'
Diese Abfrage scheint ca. 36 Millionen Rows zu liefern, zumindest vermutet PG dies. Deine Tabelle t_value_time wird in Deiner Abfrage in einem Cross-Join mit dem Resultat vom Join der zwei anderen Tabellen verbunden, möglicherweise willst Du das gar nicht.
Die 2 anderen Tabellen scheinen relativ klein zu sein, stimmts?
SELECT mp.current_value FROM t_value_time AS mp, t_og_pv_plant AS pv, t_mp_og AS link
WHERE **pv.og_id = link.og_id** AND **pv.og_id = link.og_id** AND pv.og_id = '2939'
dadurch liefert er natürlich alles zurück. Ich wollte schreiben:
[CODE]
SELECT mp.current_value
FROM t_value_time AS mp, t_og_pv_plant AS pv, t_mp_og AS link
WHERE pv.og_id = link.og_id AND pv.mp_id = link.mp_id AND pv.og_id = ‘2939’[/CODE]
Mein Vertrauen in Datenbanken ist wieder hergestellt, so dauert es nur 1,4 Sekunden
War meine Datenbankvorlesung doch nicht für’n Arsch, ich kann bloss nicht tippen.
EXPLAIN ANALYSE SELECT mp.current_value
FROM t_value_time AS mp, t_og_pv_plant AS pv, t_mp_og AS link
WHERE pv.og_id = link.og_id AND mp.mp_id = link.mp_id AND pv.og_id = '2939'
Ist da noch der Cross Join? Den macht der ja selber. Ich sage ja nur, welche Ergebnisse ich zurück haben will. Wie er das dann joint weiss er wahrscheinlich besser als ich.
Das ist nun wieder eine andere WHERE-Condition als bisher gezeigt, hier ist auch kein Cross-Join mehr. Hast Du einen Index auf mp.mp_id? Und ja, es werden 70562 Zeilen erwartet, aber real sind es nur 2383. Möglicherweise stimmen die Statistiken nicht mehr so recht. Welche Version ist da eigentlich am Start?
Ach ja, Cross-Join: damit meine ich solch einen Effekt hier:
test=# select * from tab1;
i
---
1
2
3
(3 Zeilen)
Zeit: 0,219 ms
test=*# select * from tab2;
i
---
1
2
3
(3 Zeilen)
Zeit: 0,236 ms
test=*# select * from tab1, tab2;
i | i
---+---
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2
3 | 3
(9 Zeilen)
In deinem letzten select war in etwa solch eine Konstruktion drin, die eine Tabelle war nicht im WHERE genannt. Wenn dann eine der beteiligten Tabellen etwas größer ist, dann kommt bei einem (unbeabsichtigtem) Cross-Join recht schnell sehr viel an Ergebniss zusammen …
Ich würde empfehlen, dass Du Dir angewöhnst die impliziten joins im WHERE durch explizite (ANSI) JOINs zu ersetzen. Dann siehst Du auch sofort, wenn Du eine JOIN Bedingung vergisst und somit ein kartesisches Produkt erzeugst. Wenn ich das obige Statement umschreibe, fällt mir sofort auf, dass da evtl. noch was fehlt:
SELECT mp.current_value
FROM t_value_time mp
JOIN t_mp_og link ON pv.og_id = link.og_id
JOIN t_og_pv_plant pv ON pv.?????
WHERE pv.og_id = '2939'
Was ist denn og_id für ein Datentyp? Wenn das eine Zahl ist, dann solltest Du die single quotes weglassen. Die werden nur für Zeichenketten benötigt.
Wenn Du das bei Zahlen machst, macht Postgres ein type casting und kann einen evtl. vorhandenen Index auf der Spalte nicht mehr verwenden.
test=*# create table casting (i int);
CREATE TABLE
Zeit: 20,792 ms
test=*# insert into casting select generate_Series(1,100000);
INSERT 0 100000
Zeit: 214,014 ms
test=*# commit;
COMMIT
Zeit: 0,500 ms
test=# create index idx_casting on casting (i);
CREATE INDEX
Zeit: 129,286 ms
test=*# analyse casting;
ANALYZE
Zeit: 52,449 ms
test=*# commit;
COMMIT
Zeit: 0,578 ms
test=# explain select * from casting where i = 999;
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_casting on casting (cost=0.00..8.28 rows=1 width=4)
Index Cond: (i = 999)
(2 Zeilen)
Zeit: 0,424 ms
test=*# explain select * from casting where i = '999';
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_casting on casting (cost=0.00..8.28 rows=1 width=4)
Index Cond: (i = 999)
(2 Zeilen)
Zeit: 0,416 ms