BUG #9007: List comparison
От | stefan.kirchev@gmail.com |
---|---|
Тема | BUG #9007: List comparison |
Дата | |
Msg-id | 20140128085634.26701.33428@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #9007: List comparison
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 9007 Logged by: Stefan Kirchev Email address: stefan.kirchev@gmail.com PostgreSQL version: 9.1.0 Operating system: Linux Ubuntu Server Description: Using two tables to extract differences fails to show any result. Table `table1` has two column of type integer, table `table2` has the same structure. Using the following query fails to show the expected result: select * from table1 where (c1, c2) not in (select c1, c2 from table2); Adding a dummy condition in the internal query helps in getting results: select * from table1 where (c1, c2) not in (select c1, c2 from table2 where c1 <> 0); Consider c1 does not nave values equal to 0: pnp=# select c1, c2 from table2 where c1 = 0; c1 | c2 ----+---- (0 rows) Tested on versions 8.4 and 9.1. Here are the query plans on v8.4: pnp=# explain select * from table1 where (c1, c2) not in (select c1, c2 from table2 where c1 <> 0); QUERY PLAN -------------------------------------------------------------------- Seq Scan on table1 (cost=290.31..681.84 rows=9951 width=8) Filter: (NOT (hashed subplan)) SubPlan -> Seq Scan on table2 (cost=0.00..253.58 rows=14692 width=8) Filter: (c1 <> 0) (5 rows) pnp=# explain select * from table1 where (c1, c2) not in (select c1, c2 from table2); QUERY PLAN -------------------------------------------------------------------- Seq Scan on table1 (cost=253.57..645.11 rows=9951 width=8) Filter: (NOT (hashed subplan)) SubPlan -> Seq Scan on table2 (cost=0.00..216.66 rows=14766 width=8) (4 rows) pnp=# In the query plan the rows shown are half of the real table records: pnp=# select count(*) from table1; count ------- 20880 (1 row) pnp=# select count(*) from table2; count ------- 15557 (1 row) pnp=# Is that a buffers size issue? The server is pretty powerful and handles a much bigger tables of about 100GB without loosing breath.
В списке pgsql-bugs по дате отправления: