Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Joining two large tables on a tiny subset of rows
Date: Tue, 26 Jul 2005 15:04:21 -0400
Message-ID: <F18A6F7CF1661F46920F2CF713122FED46CC35@mail.aveo.aveopharma.com>
Thread-Topic: Joining two large tables on a tiny subset of rows
Thread-Index: AcWSFNRYh4aRzDRRSOWPFNhPr5W2hw==
From: "Dmitri Bichko" <dbichko@aveopharma.com>
To: <pgsql-sql@postgresql.org>

Hello,

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

             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

           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

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

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).

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.

Here are the explains for the two step process:

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.7=
8
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)

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

------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------------------------------------------------------
 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)::tex=
t
=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)

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> 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;
                                                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)

What am I doing wrong?

Dmitri

The information transmitted is intended only for the person or entity to =
which it is addressed and may contain confidential and/or privileged mate=
rial. Any review, retransmission, dissemination or other use of, or takin=
g 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 comput=
er