Index wird nicht verwendet

Eine Tabelle mit nur einer Spalte, und zwar SERIAL?

Hier nochmal die vollständigen Tabellenstrukuren inklusive des Query-Plans:

create table my_table
(
id serial not null,
route_id int not null,
gueltigbis char(23),

… wird ja immer schlimmer …

constraint pkey_my_table primary key (id),
constraint fkey_routeid foreign key (route_id) references my_route (id)
);

create index i_my_table_gueltigbis_partial on my_table (gueltigbis) WHERE SUBSTR(gueltigbis, 1, 1) != ‘3’;

explain analyse select * from my_table where gueltigbis < ‘3’;

Der Index ist als substr() definiert, das mußt Du hier dann auch so verwenden.

Andreas

Gib uns doch mal das Ergebnis von:

SELECT name, setting, unit FROM pg_settings WHERE name LIKE ‘enable%’;

Hier sind die Werte:

enable_seqscan und enable_nestloop habe ich auf off gestellt.

“enable_bitmapscan”;“on”;""
“enable_hashagg”;“on”;""
“enable_hashjoin”;“on”;""
“enable_indexscan”;“on”;""
“enable_mergejoin”;“on”;""
“enable_nestloop”;“off”;""
“enable_seqscan”;“off”;""
“enable_sort”;“on”;""
“enable_tidscan”;“on”;""

Im übrigen macht die Postgres auch bei anderen SQLs unserer Anwendung Probleme. Ab 6 Joins wird ein SQL sterbenslangsam (ca. 15 Sekunden), und ein einfacher select count(*) auf einer Tabelle dauert ca. 500 Millisekunden.

Ich frage mich immer warum alle Welt ein count() macht um die Performance zu beurteilen.
In 20 Jahren habe noch nie ein Projekt in Produktion gebracht, für das die Laufzeit eine count(
) irgendeine Rolle gespielt hat…

Nun ja, Du hast ja einige Dinge dokumentiert, die man besser machen könnte, z.B. sinnvolle Verwendung von Datentypen und Anfragen passend zu den Indexen gestalten.

Ein select count(*) zieht bei PG (aufgrund von MVCC) einen seq.scan nach sich. Oft braucht man aber gar nicht sooo genau zu wissen, wieviel Zeilen nun wirklich drin sind, und kann sich mit einer Schätzung begnügen. Und falls doch, gibt es Trigger-basierte Lösungen. Ob 15 Sekunden für einen 6-Tabellen-Join lahm sind oder nicht kann ohne Detailwissen auch nicht abgeschätzt werden.

Andreas

Wieso sind dort zwei Werte auf “off”? Damit erstellt der Planer unsinnige Pläne, die in der Regel sogar mehr Performance kosten.

Von “keine weiteren Einstellungen vorgenommen” kann hier wohl auch keine Rede mehr sein.

Wieviel habt ihr da eigentlich noch verbrochen und erwartet nun eine gute Performance?

Um das mit dem “SELECT COUNT(*)” mal klarzustellen:

ads=# create table seqscan (id serial primary key, data integer);
NOTICE:  CREATE TABLE will create implicit sequence "seqscan_id_seq" for serial column "seqscan.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "seqscan_pkey" for table "seqscan"
CREATE TABLE
ads=# insert into seqscan (data) select generate_series(1, 10000000);
INSERT 0 10000000
ads=# create index seqscan_data_idx on seqscan (data);
CREATE INDEX
ads=# analyze seqscan;
ANALYZE

Nun einmal mit normalen Einstellungen:

ads=# set enable_nestloop = on;                                                                                                                             
SET                                                                                                                                                                              
ads=# set enable_seqscan = on;                                                                                                                             
SET                                                                                                                                                                              
ads=# explain analyze select count(*) from seqscan;                                                                                                                              
                                                         QUERY PLAN                                                                                                              
-----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=169247.71..169247.72 rows=1 width=0) (actual time=28514.314..28514.315 rows=1 loops=1)
   ->  Seq Scan on seqscan  (cost=0.00..144247.77 rows=9999977 width=0) (actual time=0.050..14565.531 rows=10000000 loops=1)
 Total runtime: 28514.387 ms
(3 Zeilen)

Und einmal mit deaktivierten:

ads=# set enable_nestloop = off;                                                                                                                                                 
SET                                                                                                                                                                              
ads=# set enable_seqscan = off;                                                                                                                                                  
SET                                                                                                                                                                              
ads=# explain analyze select count(*) from seqscan;                                                                                                                              
                                                                 QUERY PLAN                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------                                     
 Aggregate  (cost=10000169247.72..10000169247.73 rows=1 width=0) (actual time=28845.569..28845.570 rows=1 loops=1)                                                               
   ->  Seq Scan on seqscan  (cost=10000000000.00..10000144247.77 rows=9999977 width=0) (actual time=0.079..14858.603 rows=10000000 loops=1)                                      
 Total runtime: 28845.642 ms                                                                                                                                                     
(3 Zeilen)

Aber wie andere schon sagten: SELECT COUNT(*) beantwortet keine Fragen und wenn doch, ist die Frage schon falsch gestellt.

ads=# drop table seqscan;
DROP TABLE

Erstmal danke für eure Mithilfe und Unterstützung, ich denke wir müssen uns intern hier nochmal zusammensetzen da wir bisher nicht mit der Lösung vorrangekommen sind. Leider ist Postgresql in unserer Firma absolutes Neuland, und das Know-How dementsprechend begrenzt.

Ich werde mich wieder melden, wenn es Fortschritte gibt.

Da kann ich aushelfen :wink:

Sitzt die Firma in der Nähe meine Wohnortes?

:wink:

Andreas

Steigst du jetzt ins Consulting-Geschäft ein oder suchst du einen neuen Job? :wink:

Du kennst die Antwort :wink:

Andreas

Ich werd drauf zurückkommen :wink:

Nicht wirklich. Komme aus Göttingen, Entfernung ca. knapp 400km :wink: