Thread

Explain reports unexpected results with inheritance

PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-02-14T18:21:02Z

David Lynn (davidl@ayamba.com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Explain reports unexpected results with inheritance

Long Description
When tab2 inherits tab1, explain in psql will always report a "Seq Scan" on tab2 even if there are appropriate indexes.  Through additional testing, it appears that the index is actually being used based on relative cost numbers, but that explain is just not reporting as such.

Using 7.0.2.

Sample Code
DEV:menu# create table tab1 (col1 int4);
CREATE
DEV:menu# create table tab2 (col2 int4) inherits (tab1);
CREATE
DEV:menu# insert into tab2 (col1, col2) values (1,1);
INSERT 28995 1
DEV:menu# insert into tab2 (col1, col2) values (2,2);
INSERT 28996 1
DEV:menu# create index idx1 on tab2 (col2);
CREATE
DEV:menu# select * from tab2 where col2 = 2;
 col1 | col2
------+------
    2 |    2
(1 row)

DEV:menu# explain select * from tab2 where col2 = 2;
NOTICE:  QUERY PLAN:

Seq Scan on tab2  (cost=0.00..1.02 rows=1 width=8)

EXPLAIN


No file was uploaded with this report

Re: Explain reports unexpected results with inheritance

Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-14T19:13:43Z

pgsql-bugs@postgresql.org writes:
> Explain reports unexpected results with inheritance

I see no unexpected behavior here, nor anything that has anything to do
with inheritance.  The CREATE INDEX updates (some of) the planner's
statistics about the table, so that it now knows tab2 contains only one
page and two rows.  Under those conditions it's never going to select
an index scan --- seqscan will always look cheaper.  (A seqscan will
only need one disk page fetch, indexscan must take at least two.)

Load up more data, do a VACUUM, and try the EXPLAIN again.

			regards, tom lane