From: | Csaba Nagy <nagy(at)ecircle-ag(dot)com> |
---|---|
To: | Scott Marlowe <smarlowe(at)g2switchworks(dot)com> |
Cc: | Sebastian Böck <sebastianboeck(at)freenet(dot)de>, Postgres general mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Bug with index-usage? |
Date: | 2005-11-14 17:30:52 |
Message-ID: | 1131989452.10890.3.camel@coppola.muc.ecircle.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jaime Casanova | 2005-11-14 17:35:13 | Re: Bug with index-usage? |
Previous Message | Scott Marlowe | 2005-11-14 17:28:28 | Re: Bug with index-usage? |