pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelecting records not present in related tables
4+ messages / 4 participants
[nested] [flat]
* Selecting records not present in related tables
@ 2005-10-06 19:43 Hector Rosas <jeziel.rosas@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Hector Rosas @ 2005-10-06 19:43 UTC (permalink / raw)
To: pgsql-sql
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 <http://m.id; FROM messages AS m
WHERE (SELECT count(um.*) FROM usermessages AS um WHERE um.idmessage=m.id
)=0;
SELECT m.id <http://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 <http://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.104rows=1 loops=355)
-> Index Scan using message_selection on usermessages um
(cost=0.00..9.10rows=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 <http://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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Selecting records not present in related tables
@ 2005-10-06 20:11 Anthony Molinaro <amolinaro@wgen.net>
0 siblings, 0 replies; 4+ messages in thread
From: Anthony Molinaro @ 2005-10-06 20:11 UTC (permalink / raw)
To: Hector Rosas <jeziel.rosas@gmail.com>; pgsql-sql
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: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Selecting records not present in related tables
@ 2005-10-06 20:13 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Hector Rosas <jeziel.rosas@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Scott Marlowe @ 2005-10-06 20:13 UTC (permalink / raw)
To: Hector Rosas <jeziel.rosas@gmail.com>; +Cc: pgsql-sql
On Thu, 2005-10-06 at 14:43, Hector Rosas wrote:
> 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.
A fairly common way to do this is to use a left join and a not null:
select a.id from tablea a left join tableb b on (a.id=b._aid) where
b._aid IS NULL
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Selecting records not present in related tables
@ 2005-10-06 20:28 Frank Bax <fbax@sympatico.ca>
0 siblings, 0 replies; 4+ messages in thread
From: Frank Bax @ 2005-10-06 20:28 UTC (permalink / raw)
To: pgsql-sql
At 03:43 PM 10/6/05, Hector Rosas wrote:
>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 <http://m.id>m.id FROM messages AS m
>WHERE (SELECT count(um.*) FROM usermessages AS um WHERE um.idmessage=m.id )=0;
>
>SELECT <http://m.id>m.id FROM messages AS m where id NOT IN (select
>um.idmessage FROM usermessages um);
select m.id from messages as m left join usermessages as um on
m.id=um.idmessage where um.idmessage is null;
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-10-06 20:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-06 19:43 Selecting records not present in related tables Hector Rosas <jeziel.rosas@gmail.com>
2005-10-06 20:13 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-06 20:11 Re: Selecting records not present in related tables Anthony Molinaro <amolinaro@wgen.net>
2005-10-06 20:28 Re: Selecting records not present in related tables Frank Bax <fbax@sympatico.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox