Postgre Geschwindigkeit?

Hallo,

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?

Danke,
MrBubbles

Was für ein Select statement?
Könntest du es mal posten?

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.

Schau dir mal die Ressourcen-Konfiguration an: PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Resource Consumption

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?

Andreas

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'

Folgendes sagt “EXPLAIN” davor:

"Nested Loop  (cost=10.85..774211.05 rows=36480300 width=8)"
"  ->  Seq Scan on t_value_time mp  (cost=0.00..44594.20 rows=2432020 width=8)"
"  ->  Materialize  (cost=10.85..11.00 rows=15 width=0)"
"        ->  Nested Loop  (cost=0.00..10.84 rows=15 width=0)"
"              ->  Seq Scan on t_og_pv_plant pv  (cost=0.00..1.23 rows=1 width=4)"
"                    Filter: (og_id = 2939)"
"              ->  Seq Scan on t_mp_og link  (cost=0.00..9.46 rows=15 width=4)"
"                    Filter: (link.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?

Andreas

Ach fuck, ich Idiot.

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 :slight_smile:
War meine Datenbankvorlesung doch nicht für’n Arsch, ich kann bloss nicht tippen.

Zeigt mal EXPLAIN ANALYSE zu Deiner Abfrage. Und ja: hast Du PG konfiguriert, oder ist das noch die Default-Config?

Andreas

Die DB ist konfiguriert, aber nicht durch mich. Ich benutze sie nur. Die steht in irgendeinem Rechenzentrum.

Explain:

    Hash Join  (cost=11.03..54430.92 rows=70562 width=8)"
    Hash Cond: (mp.mp_id = link.mp_id)"
    ->  Seq Scan on t_value_time mp  (cost=0.00..44594.20 rows=2432020 width=12)"
    ->  Hash  (cost=10.84..10.84 rows=15 width=4)"
    ->  Nested Loop  (cost=0.00..10.84 rows=15 width=4)"
        ->  Seq Scan on t_og_pv_plant pv  (cost=0.00..1.23 rows=1 width=4)"
            Filter: (og_id = 2939)"
            ->  Seq Scan on t_mp_og link  (cost=0.00..9.46 rows=15 width=8)"
                Filter: (link.og_id = 2939)

Das ist nur ein EXPLAIN, ohne ANALYSE. Egal. Bist Du Dir sicher, da wirklich einen Cross Join zu wollen?

Andreas

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'

So richtig? Dabei kommt raus:

"Hash Join  (cost=11.03..54430.92 rows=70562 width=8) (actual time=526.961..4940.813 rows=2383 loops=1)"
"  Hash Cond: (mp.mp_id = link.mp_id)"
"  ->  Seq Scan on t_value_time mp  (cost=0.00..44594.20 rows=2432020 width=12) (actual time=0.010..2602.364 rows=2432031 loops=1)"
"  ->  Hash  (cost=10.84..10.84 rows=15 width=4) (actual time=0.254..0.254 rows=31 loops=1)"
"        ->  Nested Loop  (cost=0.00..10.84 rows=15 width=4) (actual time=0.166..0.237 rows=31 loops=1)"
"              ->  Seq Scan on t_og_pv_plant pv  (cost=0.00..1.23 rows=1 width=4) (actual time=0.012..0.014 rows=1 loops=1)"
"                    Filter: (og_id = 2939)"
"              ->  Seq Scan on t_mp_og link  (cost=0.00..9.46 rows=15 width=8) (actual time=0.150..0.201 rows=31 loops=1)"
"                    Filter: (link.og_id = 2939)"
"Total runtime: 4941.576 ms"

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 …


Andreas

Aha, d.h. jede Tabelle die ich in FROM erwähne sollte auch in WHERE eingeschränkt werden? Ich dachte sowas macht die DB selber.

Nein, macht sie nicht.

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.

Seit wann denn das?

smile

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

Andreas

Da sehe ich jetzt zwei Mal einen Index Scan. Genau das, was ich erwartet habe.