Re: Bug with index-usage?

The OP was complaining about the results of the above script, which I
could readily reproduce on a 8.1.0 installation on debian (see below).
The same select which returned 3 rows will return nothing after creating
the partial indexes, which looks as a bug to me...
I can't tell anything about why it happens, just confirm that I can
reproduce too...

Cheers,
Csaba.

cnagy=> CREATE TABLE test1 (
cnagy(>   id SERIAL PRIMARY KEY,
cnagy(>   name TEXT NOT NULL
cnagy(> );
NOTICE:  CREATE TABLE will create implicit sequence "test1_id_seq" for
serial column "test1.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test1_pkey" for table "test1"
CREATE TABLE
cnagy=>
cnagy=> INSERT INTO test1 (name) VALUES ('test1_1');
INSERT 0 1
cnagy=> INSERT INTO test1 (name) VALUES ('test1_2');
INSERT 0 1
cnagy=> INSERT INTO test1 (name) VALUES ('test1_3');
INSERT 0 1
cnagy=>
cnagy=> CREATE TABLE test2 (
cnagy(>   id SERIAL PRIMARY KEY,
cnagy(>   type TEXT NOT NULL CHECK (type IN ('a','b','c')),
cnagy(>   test1_id INTEGER REFERENCES test1
cnagy(> );
NOTICE:  CREATE TABLE will create implicit sequence "test2_id_seq" for
serial column "test2.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test2_pkey" for table "test2"
CREATE TABLE
cnagy=>
cnagy=> INSERT INTO test2 (type,test1_id) VALUES ('a',1);
INSERT 0 1
cnagy=> INSERT INTO test2 (type,test1_id) VALUES ('a',2);
INSERT 0 1
cnagy=> INSERT INTO test2 (type,test1_id) VALUES ('a',3);
INSERT 0 1
cnagy=> INSERT INTO test2 (type,test1_id) VALUES ('b',1);
INSERT 0 1
cnagy=> INSERT INTO test2 (type,test1_id) VALUES ('b',2);
INSERT 0 1
cnagy=> INSERT INTO test2 (type,test1_id) VALUES ('b',3);
INSERT 0 1
cnagy=> INSERT INTO test2 (type,test1_id) VALUES ('c',1);
INSERT 0 1
cnagy=> INSERT INTO test2 (type,test1_id) VALUES ('c',2);
INSERT 0 1
cnagy=> INSERT INTO test2 (type,test1_id) VALUES ('c',3);
INSERT 0 1
cnagy=>
cnagy=> CREATE OR REPLACE VIEW test AS
cnagy->   SELECT test2.*
cnagy->   FROM test2
cnagy->   LEFT JOIN test2 AS t2 ON
cnagy->     test2.type IN ('c','b') AND
cnagy->     t2.type = 'a';
CREATE VIEW
cnagy=>
cnagy=> SELECT * from test WHERE type = 'a';
 id | type | test1_id
----+------+----------
  1 | a    |        1
  2 | a    |        2
  3 | a    |        3
(3 rows)

cnagy=>
cnagy=> CREATE INDEX index_a ON test2 (id) WHERE type = 'a';
CREATE INDEX
cnagy=> CREATE INDEX index_b ON test2 (id) WHERE type = 'b';
CREATE INDEX
cnagy=> CREATE INDEX index_c ON test2 (id) WHERE type = 'c';
CREATE INDEX
cnagy=>
cnagy=> SET enable_seqscan TO OFF;
SET
cnagy=>
cnagy=> SELECT * from test WHERE type = 'a';
 id | type | test1_id
----+------+----------
(0 rows)



On Mon, 2005-11-14 at 18:17, Scott Marlowe wrote:
> On Mon, 2005-11-14 at 11:07, Sebastian Böck wrote:
> > Hello,
> >
> > I get unpredictibale results selecting from a view depending on
> > index-usage.
>
> PostgreSQL uses a cost based planner.  So, it tends to not use the plan
> you might expect, especially in "toy" test cases with small data sets.
> I.e. why use an index to look up 10 values, when they all fit on the
> same page.  Just seq scan the data from the table.
>
> Fill up your table with REAL data (or a close substitute) and test
> again.  Also, read up on the admin section, specifically the part on the
> postgresql.conf file and what the settings in there mean, then read
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend