PostgreSQL 7.4 - Verständnisproblem ...

gepeinigter_aon_neukunde — Sun, 01 Jan 2006 15:09:35 GMT

Hi !

Gegeben sei eine Tabelle mit ein paar Millionen Datensatzzeilen - und unter anderem gebe es ein Attribut "fixstop", das ein Boolean not null ist.

Die Histogramme sind noch auf dem Defaultwert 10, und auf den Boolean gebe es einen Index.

Hier ein paar Ausgaben:


SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'lieferliste';
....

fixstop |          1 | {f}
# Es sind also nur 'false-Werte' in der DB gespeichert.

kis2_prod=# EXPLAIN select * from lieferliste where fixstop='t';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using idx_test on lieferliste  (cost=0.00..2.01 rows=1 width=118)
   Index Cond: (fixstop = true)
# Bei einer Query nach 't' erkennt er anhand der Statistic die Selektivität von 't' - und geht über den Index - soweit ok

kis2_prod=# EXPLAIN select * from lieferliste where fixstop='f';
                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on lieferliste  (cost=0.00..55892.88 rows=1379510 width=118)
   Filter: (fixstop = false)
(2 Zeilen)
# Bei einer Query nach 'f' erkennt er anhand der Statistic die Wertlosigkeit der WHERE-Clause - und macht korrekt einen Full Table Scan.


EXPLAIN select * from lieferliste where fixstop;
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on lieferliste  (cost=0.00..52444.10 rows=1 width=118)
   Filter: fixstop
# Das hier versteh ich nicht.

PostgreSQL 7.4 - Verständnisproblem ...

gepeinigter_aon_neukunde — Sun, 01 Jan 2006 15:09:35 GMT


SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'lieferliste';
....

fixstop |          1 | {f}
# Es sind also nur 'false-Werte' in der DB gespeichert.

kis2_prod=# EXPLAIN select * from lieferliste where fixstop='t';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using idx_test on lieferliste  (cost=0.00..2.01 rows=1 width=118)
   Index Cond: (fixstop = true)
# Bei einer Query nach 't' erkennt er anhand der Statistic die Selektivität von 't' - und geht über den Index - soweit ok

kis2_prod=# EXPLAIN select * from lieferliste where fixstop='f';
                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on lieferliste  (cost=0.00..55892.88 rows=1379510 width=118)
   Filter: (fixstop = false)
(2 Zeilen)
# Bei einer Query nach 'f' erkennt er anhand der Statistic die Wertlosigkeit der WHERE-Clause - und macht korrekt einen Full Table Scan.


EXPLAIN select * from lieferliste where fixstop;
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on lieferliste  (cost=0.00..52444.10 rows=1 width=118)
   Filter: fixstop
# Das hier versteh ich nicht.

Nun zu meiner Frage:
In der Tabelle gibt es keinen einzigen True-wert für fixstop - wie man an der ersten Query sieht.
Daher ist für mich der Weg bei der Query nach "fixstop='t'" völlig klar. Mich verblüfft allerdings, daß ein "WHERE fixstop='t'" kein Synonym zu "WHERE fixstop" ist - denn da macht er ja den full table scan.

In der Doku habe ich keinen Hinweis drauf gefunden - wie lautet jetzt die genaue Vorgehensweise des Optimizers ? Und... läßt sich dieses Feature sinnvoll nutzen ? Bleibt das Verhalten konstant oder kann es sein, daß sich das mit der 8er oder 8.1er bereits geändert hat ?

EDIT:
Die Queries wurden direkt nach einem VACUUM ANALYZE abgesetzt, an veralteten Statistiken dürft's aber nicht liegen. Einsatzszenario: Die Millionen Datensatzzeilen werden bleiben - und es werden maximal 50 das fixstop auf 'true' gesetzt bekommen - ein Index-Scan macht also sowohl bei "WHERE fixstop" als auch bei "WHERE fixstop='t'" durchaus Sinn.

PostgreSQL 7.4 - Verständnisproblem ...

gepeinigter_aon_neukunde — Sun, 01 Jan 2006 15:09:35 GMT


SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'lieferliste';
....

fixstop |          1 | {f}
# Es sind also nur 'false-Werte' in der DB gespeichert.

kis2_prod=# EXPLAIN select * from lieferliste where fixstop='t';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using idx_test on lieferliste  (cost=0.00..2.01 rows=1 width=118)
   Index Cond: (fixstop = true)
# Bei einer Query nach 't' erkennt er anhand der Statistic die Selektivität von 't' - und geht über den Index - soweit ok

kis2_prod=# EXPLAIN select * from lieferliste where fixstop='f';
                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on lieferliste  (cost=0.00..55892.88 rows=1379510 width=118)
   Filter: (fixstop = false)
(2 Zeilen)
# Bei einer Query nach 'f' erkennt er anhand der Statistic die Wertlosigkeit der WHERE-Clause - und macht korrekt einen Full Table Scan.


EXPLAIN select * from lieferliste where fixstop;
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on lieferliste  (cost=0.00..52444.10 rows=1 width=118)
   Filter: fixstop
# Das hier versteh ich nicht.


psql test
Willkommen bei psql 7.4.2, dem interaktiven PostgreSQL-Terminal.

Geben Sie ein:  \copyright für Urheberrechtsinformationen
                \h für Hilfe über SQL-Anweisungen
                \? für Hilfe über interne Anweisungen
                \g oder Semikolon, um eine Anfrage auszuführen
                \q um zu beenden

test=# \d
         Liste der Relationen
 Schema | Name |   Typ   | Eigentümer
--------+------+---------+-------------
 public | a    | Tabelle | grueni
(1 Zeile)

test=# CREATE TABLE laaang ( id serial primary key not null, bool boolean not null default 'f');
NOTICE:  CREATE TABLE will create implicit sequence "laaang_id_seq" for "serial" column "laaang.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "laaang_pkey" for table "laaang"
CREATE TABLE
test=# INSERT INTO laaang(wert) values ('f');
ERROR:  column "wert" of relation "laaang" does not exist
test=# INSERT INTO laaang(bool) values ('f');
INSERT 17180 1
test=# INSERT INTO laaang(bool) values ('f');
INSERT 17181 1
test=# INSERT INTO laaang(bool) values ('f');
INSERT 17182 1
test=# INSERT INTO laaang(bool) values ('f');
INSERT 17183 1
test=# INSERT INTO laaang(bool) values ('f');
INSERT 17184 1
test=# SELECT count(*) from pg_statistic ;
 count
-------
   245
(1 Zeile)

test=# INSERT INTO laaang (bool) SELECT 'f' from pg_statistic a, pg_statistic b;

INSERT 0 60025
test=#
test=# SELECT count(*) from laaang;
 count
-------
 60030
(1 Zeile)

test=# INSERT INTO laaang(bool) SELECT bool from laaang;
INSERT 0 60030
test=# INSERT INTO laaang(bool) SELECT bool from laaang;
INSERT 0 120060
test=# VACUUM ANALYZE ;
VACUUM

test=# CREATE INDEX idx_laang on laaang (bool);
CREATE INDEX
test=# EXPLAIN SELECT * from laaang where bool='t';
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using idx_laang on laaang  (cost=0.00..2.01 rows=1 width=5)
   Index Cond: (bool = true)
(2 Zeilen)
--- So soll es sein !

test=# EXPLAIN SELECT * from laaang where bool;
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on laaang  (cost=0.00..3579.20 rows=1 width=5)
   Filter: bool
(2 Zeilen)

-- So soll es nicht sein!