Index wird nicht verwendet

Hallo zusammen,

ich habe ein kleines Problem, vielleicht kann mir jemand von euch weiterhelfen.

Folgende Tabelle:

CREATE TABLE my_table (id serial not null, route_id int, gueltig_von char(10));

SELECT * from my_table WHERE gueltig_von < ‘3’

führt zu einem Sequence-Scan, obwohl ich einen Index auf die Spalte gesetzt habe:

CREATE INDEX I_ROUTEID_GUELTIGVON ON my_table (route_id, gueltig_von);

Kann mir vielleicht jemand weiterhelfen? Dieser SQL brauch unter Postgres ca. 10 Sekunden. Bei Oracle dauert der gleiche SQL auf der gleichen Tabelle nur 50ms, hier wird der Index korrekt verwendet.

Sobald ich allerdings bei Postgresql die Abfrage folgendermaßen verändere:

SELECT * from my_table WHERE gueltig_von = ‘3’

wird der Index verwendet.

Wäre euch für Tipps dankbar!

Viele grüße,
postgre_neuling

Wahrscheinlich kann die Datenbank nichts mit der Tatsache anfangen, dass die Spalte ein TEXT ist und du numerisch vergleichst …

Ist das wirklich so? Wenn ja, gibt es eine Einstellung auf der Datenbank die Typkonvertierung implizit vornimmt?

Ansonsten würde das bedeuten, dass wir im Code unserer Software ca. 1.000 Stellen finden und ändern müssten.

Dein Index ist anscheinend nicht selektiv genug:

ads=# create table text_test (id integer primary key, data1 char(10), data2 integer);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "text_test_pkey" for table "text_test"
CREATE TABLE
ads=# insert into text_test (id, data1) select generate_series (1, 1000000), case when random() < 0.03 then 1 else 2 end;
INSERT 0 1000000
ads=# update text_test set data2 = data1::integer;
UPDATE 1000000
ads=# create index data1_idx on text_test (data1);
CREATE INDEX
ads=# create index data2_idx on text_test (data2);
CREATE INDEX
ads=# analyze text_test;
ANALYZE

Das erstellt eine Tabelle mit einer Menge Einträge. 3% davon sind ‘1’, der Rest ist ‘2’. Beide Spalten enthalten den gleichen Wert, einmal ist es ein Text, einmal ein Integer.

So, testen wir das mal:

ads=# explain select * from text_test where data1 < '2';
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Index Scan using data1_idx on text_test  (cost=0.00..5773.02 rows=28900 width=19)
   Index Cond: (data1 < '2'::bpchar)
(2 Zeilen)

ads=# explain select * from text_test where data1 < '3';
                             QUERY PLAN                             
--------------------------------------------------------------------
 Seq Scan on text_test  (cost=0.00..23788.00 rows=1000000 width=19)
   Filter: (data1 < '3'::bpchar)
(2 Zeilen)

Und der Integer:

ads=# explain select * from text_test where data2 < '2';
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Index Scan using data2_idx on text_test  (cost=0.00..5644.98 rows=28900 width=19)
   Index Cond: (data2 < 2)
(2 Zeilen)

ads=# explain select * from text_test where data2 < '3';
                             QUERY PLAN                             
--------------------------------------------------------------------
 Seq Scan on text_test  (cost=0.00..23788.00 rows=1000000 width=19)
   Filter: (data2 < 3)
(2 Zeilen)

Jedesmal wenn die Datenbank einen selektiven Index verwenden kann, wird die “kleiner als” Frage auch aus dem Index beantwortet.

Stellt sich also die Frage, wie viele Werte bei dir “< 3” sind, wie viele “= 3” sind.


Welche PostgreSQL-Version ist das. Ist Autovacuum aktiviert? Wann hat sich Autovacuum das letzte Mal die Tabelle angeschaut? Wie häufig ändert sich diese Tabelle? Wie viele Einträge hat diese Tabelle?

Kannst Du mal die Ausgaben von EXPLAIN ANALYSE zeigen?

Dein Problem ist vermutlich, daß der Index über 2 Spalten geht, im WHERE aber nur die 2. Spalte auftaucht.

test=# \d foo;
      Table "public.foo"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
 b      | integer |
Indexes:
    "idex_foo" btree (a, b)
--

test=# explain analyse  select * from foo where b=90;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..1693.01 rows=1 width=8) (actual time=41.306..41.306 rows=0 loops=1)
   Filter: (b = 90)
 Total runtime: 41.363 ms
(3 rows)

test=*# create index idex_foo2 on foo(b);
CREATE INDEX
test=*# analyse;
ANALYZE
test=*# explain analyse  select * from foo where b=90;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Index Scan using idex_foo2 on foo  (cost=0.00..8.28 rows=1 width=8) (actual time=0.196..0.196 rows=0 loops=1)
   Index Cond: (b = 90)
 Total runtime: 0.249 ms
(3 rows)

Andreas

@ads:
Die PostgreSQL-Version ist 8.3, installiert auf einem Windows 2008 Server.
Die Datenbank hat ca. 208.000 Datensätze, durch einen ständig laufenden Prozess wird alle 10 Sekunden ein neuer Datensatz auf die Tabelle geschrieben (das ist die Zeit, die der SQL braucht).
Autovacuum ist in den Auslieferungseinstellungen von PostgreSQL (aktiviert, einmal pro Minute laufend). Um sicherzugehen hatte ich auch schon einmal Autovacuum manuell auf der Tabelle ausgeführt - mit keinerlei Auswirkung.

@akretschmer:

Hier der Plan.

Anmerkung: Ich hatte ja Eingangs geschrieben, dass der SQL 10 Sekunden braucht. Dies bezieht sich allerdings auf das Gesamtkonstrukt, ich habe hier also nur den “schuldigen” Teil gepostet.

Zu dem Index: Ich hatte auch mal den Index gedroppt, einen Index nur mit der gultigbis-Spalte angelegt, sowie einen Index wo die beiden Spalten vertauscht sind (gueltigbis zuerst), auch das brachte den Query-Planer nicht dazu, den Index zu verwenden.

"Seq Scan on my_table(cost=0.00..5001.89 rows=101836 width=64) (actual time=0.251..245.894 rows=101799 loops=1)" " Filter: (gueltigbis > '3'::bpchar)" "Total runtime: 253.078 ms"

Das paßt nicht so ganz zur Tabelle:

CREATE TABLE my_table (id serial not null, route_id int, gueltig_von char(10));

So hattest Du das erst geschrieben, nun ist da eine andere Spalte.

Egal, Dein SELECT liefert ja fast die halbe Tabelle als Resultat, da würde ein Index-Scan auch nicht helfen.

Frage: warum char(10), welche Werte kommen da vor?


Andreas

Ja, ich hatte gestern von zuhause aus geschrieben, da hatte ich die Namen der Spalten aus dem Gedächtnis rekonstruiert, was nicht ganz korrekt war :wink:

gueltigbis ist richtig. Diese Spalte hält Datumswerte im Format (YYYY-MM-DD) unserem programmeigenen Format.

Aber wieso kann die Oracle das ohne Probleme?

P.S. Was mir weiterhin aufgefallen ist: Der gesamte SQL wird nicht gecached (jede Ausführung dauert immer 10 Sekunden). Trotz der emfpohlenen Speicheranpassungen.

Was soll denn dann die Abfrage
SELECT * from my_table WHERE gueltigibs = ‘3’
bzw.
SELECT * from my_table WHERE gueltigbis > ‘3’
liefern?

Das wird im Endeffekt sowas wie ‘2010-06-21’ > ‘3’ ausgewertet. Macht für mich überhaupt keinen Sinn (und das wir auch Oracle nicht sinnvoll machen)

Wir halten das Datum ‘3999-31-12’, um zu kennzeichnen das ein Datensatz unbegrenzt gültig ist. Daher die Abfrage > ‘3’ bzw. < ‘3’ um alle gültigen bzw. nicht gültigen zu finden.

Aua. Es gibt Datentypen.

est=*# create table immer (d date);
CREATE TABLE
test=*# insert into immer (d) values ('infinity'::date);
INSERT 0 1
test=*# select * from immer ;
    d
----------
 infinity
(1 row)

Andreas

AUTSCH.

(Und wieso muss man hier 10 Zeichen eingeben)

So, nachdem ich mich von meinem ersten Schreck erholt habe …

  • Ihr verwendet also ein gültiges, aber unwahrscheinliches Datum, um “unendlich” zu kennzeichnen.
  • Ihr verwendet einen String statt einem DATUM.
  • Ihr verwendet <> Operationen auf einem String, um nicht “unendliche” Datensätze zu finden.

Und ihr erwartet auch noch Performance.

  • Wozu gibt es DATE Datentypen?
  • Wozu gibt es NULL?
  • PostgreSQL kann partielle Indexes, damit könnte man selbst in eurer Lösung etwas wie “WHERE SUBSTR(spalte, 1, 1) != ‘3’” aus einem Index beantworten.

Alles in allem ist das aber recht krank, muss ich zugeben.

Leider will die Postgresql auch mit dem partiellen Index sich nicht dazu bewegen lassen, einen Index für den Lookup zu verwenden.

Ich stimme euch zu, dass unser Konstrukt nicht unbedingt sehr optimal ist, allerdings war die Motivation für dieses Vorgehen, eine möglichst datenbankunabhängige Implementation zu erreichen.

Fakt ist aber auch, das andere Datenbanken mit den Abfragen problemlos zurechtkommen (SQL Server, Oracle), nur die Postgresql nicht.

Zeige uns die Tabellen- und Indexdefinition sowie explain analyse der Abfrage.

Andreas

Eventuell reicht hier das raufsetzten des statistics target des Tables um PG für diese Abfragen den Index verwenden zu lassen, siehe PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Statistics Used by the Planner .

Bezgl. Datenmodell etc. hab ich den Vorrednern nichts hinzuzufügen :wink: .

Oh, ein Frischling hier im Forum :wink:
Hallo Robe.

Ja, ev. würden angepasste Statistiken reichen.
postgre_neuling schreibt aber auch, dass sein EXPLAIN ca. 100.000 Zeilen zurückliefert. Und es sind ca. 200.000 Zeilen in der Tabelle.
Damit ist der sequentielle Scan gerechtfertigt, ein Index Scan wäre wahrscheinlich auch nicht schneller.


An der Stelle sollte man sich fragen, was man anderswo optimieren kann.

Wie sind die Einstellungen für “shared_buffers”, “work_mem”, “maintenance_work_mem”, “max_fsm_pages”, “max_fsm_relations”?

Uh… Sequential Scan über 200.000 Rows mit 10 Sekunden Laufzeit? Das macht nicht wirklich Sinn auf aktueller Hardware… Und bei so einer Selectivity müste man den Planner sehr ärgern damit er bei einem halben Table als Resultset einen Index Scan macht.

create table my_route (id serial not null, constraint pkey_my_route primary key (id) );

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),
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’;

“Seq Scan on my_table (cost=10000000000.00…10000004214.09 rows=109367 width=32) (actual time=0.017…274.228 rows=109003 loops=1)”
" Filter: (gueltigbis < ‘3’::bpchar)"
“Total runtime: 281.853 ms”

shared_buffers liegt bei 25$ des Systemarbeitsspeichers, effective_cache_size bei 50%.
Andere Werte wurden bisher nicht verändert.