Jeziel,
there are a couple techniques you can try, two I
like are set difference and anti-joins.
here’s the set diff:
select id
from messages
except
select id
from usermessages
that will returns all id from messages not in usermessages
if ID is indexed on both tables, you may wanna try an anti
join:
select m.id
from messages m
left join
usermessages um
on ( m.id = um.id )
where um.id is null
both techniques can be visciously efficient.
good luck,
Anthony
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On
Behalf Of Hector Rosas
Sent: Thursday, October 06, 2005
3:44 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Selecting records
not present in related tables
Hello, I'm trying to select records in a table not
present in a related table, in example, I've a table with message information
(subject, message, date, etc) and another (usermessages) with where user(s) has
that message, its state, etc. Records in this table will be deleted in a
certain time (just some extra info).
I want to select messages records that aren't present in the other table
(usermessages), I got the next two queries, maybe someone can suggest a better
one.
SELECT
m.id FROM messages AS m
WHERE (SELECT count(um.*) FROM usermessages AS um WHERE um.idmessage=m.id )=0;
SELECT m.id FROM messages AS m where id NOT IN (select
um.idmessage FROM usermessages um);
Both queries work, but doing a EXPLAIN ANALYZE I got the next results.
bd=# explain
analyze SELECT m.id FROM messages AS m
bd-# WHERE (SELECT count(um.*) FROM usermessages AS um WHERE
um.idmessage=m.id)=0;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on messages m (cost=0.00..3915.75 rows=3 width=4) (actual
time=40.531..40.531 rows=0 loops=1)
Filter: ((subplan) = 0)
SubPlan
-> Aggregate (cost=9.11..9.11 rows=1
width=4) (actual time=0.098..0.104 rows=1 loops=355)
-> Index
Scan using message_selection on usermessages um (cost=0.00..9.10 rows=3
width=4) (actual time=0.067..0.078 rows=1 loops=355)
Index Cond: (idmessage = $0)
Total runtime: 40.605 ms
(7 rows)
bd=# explain analyze select m.id FROM messages AS m
where id NOT IN (select um.idmessage FROM usermessages um);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on messages m (cost=9.68..43.00 rows=213 width=4) (actual
time=20.329..20.329 rows=0 loops=1)
Filter: (NOT (hashed subplan))
SubPlan
-> Seq Scan on usermessages um
(cost=0.00..8.54 rows=454 width=4) (actual time=0.008..13.094 rows=454 loops=1)
Total runtime: 20.386 ms
(5 rows)
In first query, cost can be between 0 and almost 4 sec, and also I see that
loops value, that I don't know what performance issues could arise.
In second query, I see a seq scan, which I don't like, I think that with too
many records this query could take ages, or maybe not, but loops value is 1.
I hope someone can give some advice with those queries , or maybe a better
query. I've not decided which query I'm going to use, thanks!
Jeziel.
