Thread

nested queries with IN statement

Vladimir N.Silyaev <vns@delta.odessa.ua> — 1999-04-24T09:02:38Z

============================================================================
                        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.