nested queries with IN statement

============================================================================
                        POSTGRESQL BUG REPORT
============================================================================


Your name        :    Vladimir N.Silyaev
Your email address    :    vns@delta.odessa.ua


System Configuration
---------------------
  Architecture (example: Intel Pentium)      : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)     : FreeBSD 4.0-CURRENT

  PostgreSQL version (example: PostgreSQL-6.4.2)  :   PostgreSQL-6.4.2

  Compiler used (example:  gcc 2.8.0)        : gcc 2.7.2


Please enter a FULL description of your problem:
------------------------------------------------
Very weak execute query with IN statement.




Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
1. Create empty database.

2.  Create sample table.
create table test1 (
id1    integer primary key,
val1    integer not null
);
create index test1_val1 on test1(val1);

create table test2 (
id2    integer primary key,
val2    varchar(16)
);
create index test2_val2 on test2(val2);

insert into test1 VALUES (1,'1');
insert into test1 VALUES (2,'1');
insert into test1 VALUES (3,'1');
insert into test1 VALUES (4,'1');
insert into test1 VALUES (5,'1');
insert into test1 VALUES (6,'1');
insert into test1 VALUES (7,'1');
insert into test1 VALUES (8,'1');
insert into test1 VALUES (9,'1');
insert into test1 VALUES (10,'1');
insert into test1 VALUES (11,'1');
insert into test1 VALUES (12,'1');
insert into test1 VALUES (13,'1');
insert into test1 VALUES (14,'1');
insert into test1 VALUES (15,'1');
insert into test1 VALUES (16,'1');
insert into test1 VALUES (17,'1');
insert into test1 VALUES (18,'1');
insert into test1 VALUES (19,'1');
insert into test1 VALUES (20,'1');
insert into test1 VALUES (21,'1');
insert into test1 VALUES (22,'1');
insert into test1 VALUES (23,'1');
insert into test1 VALUES (24,'1');
insert into test1 VALUES (25,'1');
insert into test1 VALUES (26,'1');
insert into test1 VALUES (27,'1');
insert into test1 VALUES (28,'1');
insert into test1 VALUES (29,'1');
insert into test1 VALUES (30,'1');
insert into test1 VALUES (31,'1');
insert into test1 VALUES (32,'1');
insert into test1 VALUES (33,'1');
insert into test1 VALUES (34,'1');
insert into test1 VALUES (35,'1');
insert into test1 VALUES (36,'1');
insert into test1 VALUES (37,'1');
insert into test1 VALUES (38,'1');
insert into test1 VALUES (39,'1');
insert into test1 VALUES (40,'1');

insert into test2 VALUES (1,'1');
insert into test2 VALUES (2,'1');
insert into test2 VALUES (3,'1');
insert into test2 VALUES (4,'1');
insert into test2 VALUES (5,'1');
insert into test2 VALUES (6,'1');
insert into test2 VALUES (7,'1');
insert into test2 VALUES (8,'1');
insert into test2 VALUES (9,'1');
insert into test2 VALUES (10,'1');
insert into test2 VALUES (11,'1');
insert into test2 VALUES (12,'1');
insert into test2 VALUES (13,'1');
insert into test2 VALUES (14,'1');
insert into test2 VALUES (15,'1');
insert into test2 VALUES (16,'1');
insert into test2 VALUES (17,'1');
insert into test2 VALUES (18,'1');
insert into test2 VALUES (19,'1');
insert into test2 VALUES (20,'1');
insert into test2 VALUES (21,'1');
insert into test2 VALUES (22,'1');
insert into test2 VALUES (23,'1');
insert into test2 VALUES (24,'1');
insert into test2 VALUES (25,'1');
insert into test2 VALUES (26,'1');
insert into test2 VALUES (27,'1');
insert into test2 VALUES (28,'1');
insert into test2 VALUES (29,'1');
insert into test2 VALUES (30,'1');
insert into test2 VALUES (31,'1');
insert into test2 VALUES (32,'1');
insert into test2 VALUES (33,'1');
insert into test2 VALUES (34,'1');
insert into test2 VALUES (35,'1');
insert into test2 VALUES (36,'1');
insert into test2 VALUES (37,'1');
insert into test2 VALUES (38,'1');
insert into test2 VALUES (39,'1');
insert into test2 VALUES (40,'1');

3. Execute test query.

select id1 from test1 where id1 in (select id1 from test1 where val1 in (select id2 from test2 where val2='0'));

The time of processing this query is not acceptable.



With best regards,
        V.Silyaev.