X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 47EA2DA3B9 for ; Thu, 6 Oct 2005 16:43:41 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30513-09 for ; Thu, 6 Oct 2005 19:43:32 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.204]) by svr1.postgresql.org (Postfix) with ESMTP id A89ACDA17D for ; Thu, 6 Oct 2005 16:43:31 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id j2so290237nzf for ; Thu, 06 Oct 2005 12:43:33 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type; b=aeOInrgzjbl451nQm4mDHT+fTxVjpLxzLN2Of/ZmD4gtjdRXr3FGvDwA3oSM9Lh6yDDPO6VxO31WD4KSeNKiVLJyCvOQm2+6DPGMQIHcGvp3yw29U9vVG7DgakCIld0Qp19SKVL9NrQEZBAVIp+7t7rq8Cx61rAnQNq5lXZnAzY= Received: by 10.36.49.12 with SMTP id w12mr1187934nzw; Thu, 06 Oct 2005 12:43:33 -0700 (PDT) Received: by 10.36.89.16 with HTTP; Thu, 6 Oct 2005 12:43:33 -0700 (PDT) Message-ID: <25266c5c0510061243g13c3f49cudde160c67f961bca@mail.gmail.com> Date: Thu, 6 Oct 2005 12:43:33 -0700 From: Hector Rosas Reply-To: Hector Rosas To: pgsql-sql@postgresql.org Subject: Selecting records not present in related tables MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_31271_28025485.1128627813538" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.038 required=5 tests=[AWL=-0.014, HTML_60_70=0.027, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200510/63 X-Sequence-Number: 22916 ------=_Part_31271_28025485.1128627813538 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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=3Dm.id )=3D0; 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=3D# explain analyze SELECT m.id FROM messages AS m bd-# WHERE (SELECT count(um.*) FROM usermessages AS um WHERE um.idmessage=3Dm.id)=3D0; QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------------------------------ Seq Scan on messages m (cost=3D0.00..3915.75 rows=3D3 width=3D4) (actual ti= me=3D 40.531..40.531 rows=3D0 loops=3D1) Filter: ((subplan) =3D 0) SubPlan -> Aggregate (cost=3D9.11..9.11 rows=3D1 width=3D4) (actual time=3D0.098..0.104rows=3D1 loops=3D355) -> Index Scan using message_selection on usermessages um (cost=3D0.00..9.10rows=3D3 width=3D4) (actual time=3D 0.067..0.078 rows=3D1 loops=3D355) Index Cond: (idmessage =3D $0) Total runtime: 40.605 ms (7 rows) bd=3D# explain analyze select m.id FROM messages AS m where i= d NOT IN (select um.idmessage FROM usermessages um); QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------- Seq Scan on messages m (cost=3D9.68..43.00 rows=3D213 width=3D4) (actual ti= me=3D 20.329..20.329 rows=3D0 loops=3D1) Filter: (NOT (hashed subplan)) SubPlan -> Seq Scan on usermessages um (cost=3D0.00..8.54 rows=3D454 width=3D4) (ac= tual time=3D0.008..13.094 rows=3D454 loops=3D1) 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 to= o 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. ------=_Part_31271_28025485.1128627813538 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hello, I'm trying to select records in a table not present in a related tab= le, in example, I've a table with message information (subject, message, da= te, etc) and another (usermessages) with where user(s) has that message, it= s 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 tabl= e (usermessages), I got the next two queries, maybe someone can suggest a b= etter one.

SELEC= T=20 m.id FROM messages AS m
WHERE (SELECT count(um.*) FROM usermessages AS um WHERE um.idmess= age=3Dm.id )=3D0;

SE= LECT m.id FROM messages AS m where id NOT IN (s= elect um.idmessage FROM usermessages um);

Both queries work, = but doing a=20 EXPLAIN ANALYZE = I got the next results.

bd=3D# explain analyze SELECT m.id FROM= messages AS m
bd-# WHERE (SELECT count(um.*) FROM usermessages AS um WHERE um.idmessa= ge=3Dm.id)=3D0;
    &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;            = ; QUERY PLAN          &nb= sp;            =             &nb= sp;            =             &nb= sp;            =             &nb= sp;            =             &nb= sp;            =             =20
-----------------------------------------= ---------------------------------------------------------------------------= -------------------------------
 Seq Scan on messages m  (cost= =3D0.00..3915.75 rows=3D3 width=3D4) (actual time=3D40.531..40.531 rows=3D0= loops=3D1)
   Filter: ((= subplan) =3D 0)
   SubPlan
    = ; ->  Aggregate  (cost=3D9.11..9.11 rows=3D1 width=3D4) (actua= l time=3D0.098..0.104 rows=3D1 loops=3D355)
            &nb= sp;    Index Cond: (idmessage =3D $0)
 Total runtime: 40.605 ms
(7 rows)
 
bd=3D# explain analyze select m.id FROM message= s AS m where id NOT IN (select um.idmessage FROM usermessages um);             &nb= sp;            =             &nb= sp;            =     QUERY PLAN
------------= ---------------------------------------------------------------------------= -------------------------------
 Seq Scan on messages m  (cost= =3D9.68..43.00 rows=3D213 width=3D4) (actual time=3D20.329..20.329 rows=3D0= loops=3D1)
   Filter: (N= OT (hashed subplan))
   SubPlan<= /span>
    = ; ->  Seq Scan on usermessages um  (cost=3D0.00..8.54 rows=3D4= 54 width=3D4) (actual time=3D0.008..13.094 rows=3D454 loops=3D1)
 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 t= oo many records this query could take ages, or maybe not, but loops value i= s 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.
------=_Part_31271_28025485.1128627813538--