X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BE90452B87 for ; Wed, 27 Jul 2005 00:48:54 -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 16663-05 for ; Wed, 27 Jul 2005 03:48:43 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.193]) by svr1.postgresql.org (Postfix) with ESMTP id B9BC352AE1 for ; Wed, 27 Jul 2005 00:48:41 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id z6so53923nzd for ; Tue, 26 Jul 2005 20:48:41 -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:cc:in-reply-to:mime-version:content-type:references; b=KUW2td10OvpYhp2oLvQ2QPnrll27Db9JJUYKi93uYeBWCe7X5Zu4vyrk77TwclKIdN3hiMtMScMn0xRrnXrcLtcZBu7ZhGfQlW3gzavfiDETmtnw9KkvhLgj1hZiF6um5OLHT6dZ78YVsfV6qUiwC2hr3dfjd5KA1isZ1pbHw/w= Received: by 10.37.22.50 with SMTP id z50mr249606nzi; Tue, 26 Jul 2005 20:48:41 -0700 (PDT) Received: by 10.36.119.19 with HTTP; Tue, 26 Jul 2005 20:48:41 -0700 (PDT) Message-ID: Date: Wed, 27 Jul 2005 09:18:41 +0530 From: Gnanavel S Reply-To: Gnanavel S To: Dmitri Bichko Subject: Re: Joining two large tables on a tiny subset of rows Cc: pgsql-sql@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_27456_18152577.1122436121133" References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.376 tagged_above=0 required=5 tests=AWL, HTML_30_40, HTML_MESSAGE, RCVD_BY_IP, RCVD_IN_BL_SPAMCOP_NET X-Spam-Level: * X-Archive-Number: 200507/231 X-Sequence-Number: 22209 ------=_Part_27456_18152577.1122436121133 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 7/27/05, Dmitri Bichko wrote: >=20 > Hello, >=20 > I have two tables, one has a foreing key from the other (only showing > the relevant columns and indices here): >=20 > Table "expresso.probes" > Column | Type | Modifiers > -------------+------------------------+----------- > platform_id | integer | not null > probe_num | integer | not null > mrna_acc | character varying(50) | > Indexes: > "idx_probes_mrna_acc" btree (mrna_acc, platform_id) WHERE mrna_acc > IS NOT NULL >=20 > Table "expresso.mrna_info" > Column | Type | Modifiers > -------------+-----------------------+----------- > mrna_acc | character varying(25) | not null > symbol | character varying(50) | not null > Indexes: > "idx_mrna_info_symbol" btree (upper(symbol::text)) WHERE symbol IS > NOT NULL >=20 > I want to select all the records in "probes" which have an "mrna_acc" > that is associated with a specific symbol in "mrna_info". >=20 > This works fine if I do it in two steps: select from "mrna_info" (12 > records out of ~250 thousand) and paste those into an IN clause to > select from "probes" (83 records out of ~4.5 million). >=20 > But if I try to do this as one query I always end up with a seq scan on > "probes". I've tried doing it with joins, subselects, joining with a > dynamic table (select from () as foo), nothing seems to get the desired > result. >=20 > Here are the explains for the two step process: >=20 > expression=3D> EXPLAIN ANALYZE SELECT mrna_acc FROM mrna_info WHERE > upper(symbol) =3D upper('pparg') AND symbol IS NOT NULL; > QUERY PLAN > ------------------------------------------------------------------------ > --------------------------------------------------------------- > Index Scan using idx_mrna_info_symbol on mrna_info (cost=3D0.00..2934.78 > rows=3D930 width=3D12) (actual time=3D0.038..0.089 rows=3D12 loops=3D1) > Index Cond: (upper((symbol)::text) =3D 'PPARG'::text) > Filter: (symbol IS NOT NULL) > Total runtime: 0.123 ms > (4 rows) Whether any records are returned by this query. Moreover "symbol is not=20 null" condition is not needed as that column cannot be null by table=20 definition. EXPLAIN ANALYZE SELECT platform_id, probe_num FROM probes WHERE mrna_acc > IN > ('U10374','U09138','U01841','U01664','NM_015869','NM_013124','NM_011146' > ,'NM_005037','D83233','BC021798','BC006811','AB011365') AND mrna_acc IS > NOT NULL; >=20 > QUERY PLAN >=20 > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > -------------------------------------------------------------------- > Index Scan using idx_probes_mrna_acc, idx_probes_mrna_acc, > idx_probes_mrna_acc, idx_probes_mrna_acc, idx_probes_mrna_acc, > idx_probes_mrna_acc, idx_probes_mrna_acc, idx_probes_mrna_acc, > idx_probes_mrna_acc, idx_probes_mrna_acc, idx_probes_mrna_acc, > idx_probes_mrna_acc on probes (cost=3D0.00..14710.63 rows=3D4151 width=3D= 8) > (actual time=3D0.040..0.719 rows=3D142 loops=3D1) > Index Cond: (((mrna_acc)::text =3D 'U10374'::text) OR ((mrna_acc)::text > =3D 'U09138'::text) OR ((mrna_acc)::text =3D 'U01841'::text) OR > ((mrna_acc)::text =3D 'U01664'::text) OR ((mrna_acc)::text =3D > 'NM_015869'::text) OR ((mrna_acc)::text =3D 'NM_013124'::text) OR > ((mrna_acc)::text =3D 'NM_011146'::text) OR ((mrna_acc)::text =3D > 'NM_005037'::text) OR ((mrna_acc)::text =3D 'D83233'::text) OR > ((mrna_acc)::text =3D 'BC021798'::text) OR ((mrna_acc)::text =3D > 'BC006811'::text) OR ((mrna_acc)::text =3D 'AB011365'::text)) > Filter: (mrna_acc IS NOT NULL) > Total runtime: 0.844 ms > (4 rows) >=20 > And here is the explain of the join (it's essentially the same plan as > the subselect and all the other ways I've tried): >=20 > expression=3D> explain SELECT platform_id, probe_num FROM mrna_info m, > probes p WHERE m.mrna_acc =3D p.mrna_acc and p.mrna_acc is not null and > UPPER(symbol) =3D UPPER('pparg') AND m.symbol IS NOT NULL; Here m.mrna_acc cannot be null, so condition on p.mrna_acc is not null is= =20 useless as m.mrna_acc =3D p.mrna_acc (for null) will not give any records Try to use outer join.=20 QUERY PLAN > ------------------------------------------------------------------------ > ---------------------------------- > Hash Join (cost=3D2937.10..133392.04 rows=3D9939 width=3D8) > Hash Cond: (("outer".mrna_acc)::text =3D ("inner".mrna_acc)::text) > -> Seq Scan on probes p (cost=3D0.00..115434.22 rows=3D2984265 > width=3D19) > Filter: (mrna_acc IS NOT NULL) > -> Hash (cost=3D2934.78..2934.78 rows=3D930 width=3D12) > -> Index Scan using idx_mrna_info_symbol on mrna_info m > (cost=3D0.00..2934.78 rows=3D930 width=3D12) > Index Cond: (upper((symbol)::text) =3D 'PPARG'::text) > Filter: (symbol IS NOT NULL) > (8 rows) >=20 > What am I doing wrong? >=20 > Dmitri >=20 > The information transmitted is intended only for the person or entity to= =20 > which it is addressed and may contain confidential and/or privileged=20 > material. Any review, retransmission, dissemination or other use of, or= =20 > taking of any action in reliance upon, this information by persons or=20 > entities other than the intended recipient is prohibited. If you received= =20 > this in error, please contact the sender and delete the material from any= =20 > computer >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? >=20 > http://archives.postgresql.org >=20 --=20 with regards, S.Gnanavel Satyam Computer Services Ltd. ------=_Part_27456_18152577.1122436121133 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On 7/27/05, Dmitri Bichko <dbi= chko@aveopharma.com> wrote:
Hello,

I have two tables, one has a foreing key from the other (only= showing
the relevant columns and indices here):

  &nbs= p;          Table "expres= so.probes"
   Column    |       &n= bsp;  Type         &= nbsp;| Modifiers
-------------+------------------------+-----------
platfor= m_id | integer          &= nbsp;     | not null
probe_num   | integer           &n= bsp;    | not null
mrna_acc    | character varying(50) &= nbsp;|
Indexes:
    "idx_probes_mrna_acc&quo= t; btree (mrna_acc, platform_id) WHERE mrna_acc
IS NOT NULL

 = ;          Table "express= o.mrna_info"
   Column    |       &n= bsp; Type          | Modifiers
-------------+-----------------------+-----------
mrna_acc=     | character varying(25) | not null
symbol =      | character varying(50) | not null
Indexes= :
    "idx_mrna_info_symbol" btree (upper(= symbol::text)) WHERE symbol IS
NOT NULL

I want to select all the records in "probes" = which have an "mrna_acc"
that is associated with a specific sy= mbol in "mrna_info".

This works fine if I do it in two ste= ps: select from "mrna_info" (12
records out of ~250 thousand) and paste those into an IN clause to
s= elect from "probes" (83 records out of ~4.5 million).

But = if I try to do this as one query I always end up with a seq scan on
"probes".  I've tried doing it with joins, subselects, = joining with a
dynamic table (select from () as foo), nothing seems to g= et the desired
result.

Here are the explains for the two step pro= cess:

expression=3D> EXPLAIN ANALYZE SELECT mrna_acc FROM mrna_info WHERE<= br>upper(symbol) =3D upper('pparg') AND symbol IS NOT NULL;
  =             &nb= sp;            =             &nb= sp;            =           QUERY PLAN
-------------------------------------------------------------------= -----
--------------------------------------------------------------- Index Scan using idx_mrna_info_symbol on mrna_info  (cost=3D0.0= 0..2934.78
rows=3D930 width=3D12) (actual time=3D0.038..0.089 rows=3D12 loops=3D1)=
   Index Cond: (upper((symbol)::text) =3D 'PPARG'::text)
&= nbsp;  Filter: (symbol IS NOT NULL)
Total runtime: 0.123 ms
(4 = rows)

Whether any records are returned by this query. Moreover "symbol is no= t null" condition is not needed as that column cannot be null by table= definition.

EXPLAIN= ANALYZE SELECT platform_id, probe_num FROM probes WHERE mrna_acc
IN
('U10374','U09138','U01841','U01664','NM_015869','NM_013124','NM_011146','NM_005037','D83233','BC021798','BC006811','AB011365') AND mrna_acc ISNOT NULL;

QUERY PLAN

---------------------------------------= ---------------------------------
-----------------------------------------------------------------------= -
----------------------------------------------------------------------= --
---------------------------------------------------------------------= ---
-----------------------------------------------------------------------= -
----------------------------------------------------------------------= --
--------------------------------------------------------------------
Index Scan using idx_probes_mrna_acc, idx_probes_mrna_acc,
idx_prob= es_mrna_acc, idx_probes_mrna_acc, idx_probes_mrna_acc,
idx_probes_mrna_a= cc, idx_probes_mrna_acc, idx_probes_mrna_acc,
idx_probes_mrna_acc, idx_p= robes_mrna_acc, idx_probes_mrna_acc,
idx_probes_mrna_acc on probes  (cost=3D0.00..14710.63 rows=3D= 4151 width=3D8)
(actual time=3D0.040..0.719 rows=3D142 loops=3D1)
&nb= sp;  Index Cond: (((mrna_acc)::text =3D 'U10374'::text) OR ((mrna_acc)= ::text
=3D 'U09138'::text) OR ((mrna_acc)::text =3D 'U01841'::text) OR
((mrna_acc)::text =3D 'U01664'::text) OR ((mrna_acc)::text =3D
'NM_0= 15869'::text) OR ((mrna_acc)::text =3D 'NM_013124'::text) OR
((mrna_acc)= ::text =3D 'NM_011146'::text) OR ((mrna_acc)::text =3D
'NM_005037'::text= ) OR ((mrna_acc)::text =3D 'D83233'::text) OR
((mrna_acc)::text =3D 'BC021798'::text) OR ((mrna_acc)::text =3D
'BC= 006811'::text) OR ((mrna_acc)::text =3D 'AB011365'::text))
   = Filter: (mrna_acc IS NOT NULL)
Total runtime: 0.844 ms
(4 rows)
<= br>And here is the explain of the join (it's essentially the same plan as
the subselect and all the other ways I've tried):

expression=3D&= gt; explain SELECT platform_id, probe_num FROM mrna_info m,
probes p WHE= RE m.mrna_acc =3D p.mrna_acc and p.mrna_acc is not null and
UPPER(symbol= ) =3D UPPER('pparg') AND=20 m.symbol IS NOT NULL;

Here m.mrna_acc cannot be null, so condition on p.mrna_acc is not null is useless as m.mrna_acc =3D p.mrna_acc (for null) will not give any records
Try to  use outer join.

 &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;         QUERY PLAN
-------------------------------------------------------------------= -----
----------------------------------
Hash Join  (cost= =3D2937.10..133392.04 rows=3D9939 width=3D8)
   Hash Cond: ((&= quot;outer".mrna_acc)::text =3D ("inner".mrna_acc)::text)
   ->  Seq Scan on probes p  (cost=3D0= .00..115434.22 rows=3D2984265
width=3D19)
    &nb= sp;    Filter: (mrna_acc IS NOT NULL)
   ->&= nbsp; Hash  (cost=3D2934.78..2934.78 rows=3D930 width=3D12)<= br>         ->  Index = Scan using idx_mrna_info_symbol on mrna_info m
(cost=3D0.00..2934.78 rows=3D930 width=3D12)
    = ;           Index Cond: (upper((symbol)::text) =3D 'PPARG'::text)
   =             Filter: = (symbol IS NOT NULL)
(8 rows)

What am I doing wrong?

Dmitr= i

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer

---------------------------(end of broadc= ast)---------------------------
TIP 4: Have you searched our list archiv= es?

          &nbs= p;    http://arch= ives.postgresql.org



--
with regards,S.Gnanavel
Satyam Computer Services Ltd. ------=_Part_27456_18152577.1122436121133--