BUG #9007: List comparison

From: stefan(dot)kirchev(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #9007: List comparison
Date: 2014-01-28 08:56:34
Message-ID: 20140128085634.26701.33428@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 9007
Logged by: Stefan Kirchev
Email address: stefan(dot)kirchev(at)gmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Marti Raudsepp 2014-01-28 13:59:04 Re: BUG #9007: List comparison
Previous Message Tom Lane 2014-01-28 02:13:43 Re: BUG #9006: Incorrect update when using where with non-existent column in subselect