nested queries with IN statement - Mailing list pgsql-bugs
From | Vladimir N.Silyaev |
---|---|
Subject | nested queries with IN statement |
Date | |
Msg-id | 199904240902.MAA00499@storage.delta.odessa.ua Whole thread Raw |
List | pgsql-bugs |
============================================================================ 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.
pgsql-bugs by date: