Selecting records not present in related tables - Mailing list pgsql-sql
| From | Hector Rosas |
|---|---|
| Subject | Selecting records not present in related tables |
| Date | |
| Msg-id | 25266c5c0510061243g13c3f49cudde160c67f961bca@mail.gmail.com Whole thread Raw |
| Responses |
Re: Selecting records not present in related tables
(Scott Marlowe <smarlowe@g2switchworks.com>)
|
| List | 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). <br />I want to select messages recordsthat aren't present in the other table (usermessages), I got the next two queries, maybe someone can suggest a betterone.<br /><br /><span style="font-family: courier new,monospace;">SELECT <a href="http://m.id">m.id</a> FROM messagesAS m</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">WHERE(SELECT count(um.*) FROM usermessages AS um WHERE um.idmessage=m.id )=0;</span><br /><br /><span style="font-family:courier new,monospace;">SELECT <a href="http://m.id">m.id</a> FROM messages AS m where id NOT IN (selectum.idmessage FROM usermessages um);</span><br /><br />Both queries work, but doing a <span style="font-family: couriernew,monospace;">EXPLAIN ANALYZE</span> I got the next results.<br /><br /><span style="font-family: courier new,monospace;">bd=#explain analyze SELECT <a href="http://m.id">m.id</a> FROM messages AS m <br />bd-# WHERE (SELECT count(um.*)FROM usermessages AS um WHERE um.idmessage=m.id)=0;</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;"> QUERY PLAN </span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">--------------------------------------------------------------------------------------------------------------------------------------------------- </span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> Seq Scan on messagesm (cost=0.00..3915.75 rows=3 width=4) (actual time=40.531..40.531 rows=0 loops=1)</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> Filter: ((subplan) = 0)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> SubPlan</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> -> Aggregate (cost=9.11..9.11 rows=1 width=4) (actual time=0.098..0.104 rows=1 loops=355)</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> -> Index Scanusing message_selection on usermessages um (cost=0.00..9.10 rows=3 width=4) (actual time=0.067..0.078 rows=1 loops=355)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> Index Cond: (idmessage = $0)</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;"> Total runtime: 40.605 ms</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">(7 rows)</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> </span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> bd=# explain analyze select <a href="http://m.id">m.id</a>FROM messages AS m where id NOT IN (select um.idmessage FROM usermessages um);</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> QUERY PLAN</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">---------------------------------------------------------------------------------------------------------------------- </span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> Seq Scan on messagesm (cost=9.68..43.00 rows=213 width=4) (actual time=20.329..20.329 rows=0 loops=1)</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> Filter: (NOT (hashed subplan))</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> SubPlan</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> -> Seq Scan on usermessages um (cost=0.00..8.54 rows=454 width=4) (actual time=0.008..13.094 rows=454 loops=1)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> Totalruntime: 20.386 ms</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">(5 rows)</span><br style="font-family: courier new,monospace;" /><br />In firstquery, cost can be between 0 and almost 4 sec, and also I see that loops value, that I don't know what performance issuescould arise.<br />In second query, I see a seq scan, which I don't like, I think that with too many records this querycould take ages, or maybe not, but loops value is 1. <br /><br />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!<br /><br />Jeziel.<br />