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)
@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.
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.
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
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.
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.
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)
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.
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.
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…