User-Agent: Microsoft-Entourage/10.1.4.030702.0
Date: Fri, 30 Sep 2005 18:24:52 -0400
Subject: Query optimization
From: Sean Davis <sdavis2@mail.nih.gov>
To: <pgsql-general@postgresql.org>
Message-ID: <BF633374.F846%sdavis2@mail.nih.gov>
Mime-version: 1.0
Content-type: text/plain; charset="US-ASCII"
Content-transfer-encoding: 7bit

I have a couple of very large tables that I am querying on that gives the
following explain analyze output.  If someone can help out with my mess,
that would be great.

Thanks,
Sean

explain analyze
    select e.*,c.* 
    from u_all_est_mrna c
        join g_rna_acc d on c.accession=d.accession,
        (select a.gene_id,
            b.db_id,
            max(tend-tstart)
        from g_rna_acc a
            join u_all_est_mrna b on a.accession=b.accession
        where gene_id<200
        group by a.gene_id,b.db_id) e
    where abs(tstart-tend)=e.max
        and d.gene_id=e.gene_id
        and c.db_id=e.db_id;
                   
QUERY PLAN         
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----------------------------
 Hash Join  (cost=1054997.38..2472083.62 rows=2 width=219) (actual
time=122796.024..357269.576 rows=327 loops=1)
   Hash Cond: ((("outer".accession)::text = ("inner".accession)::text) AND
(abs(("outer".tstart - "outer".tend)) = "inner".max) AND
(("outer".db_id)::text = ("inner".db_id)::text))
   ->  Seq Scan on u_all_est_mrna c  (cost=0.00..932582.74 rows=24225174
width=179) (actual time=17.384..302484.904 rows=24225174 loops=1)
   ->  Hash  (cost=1054973.98..1054973.98 rows=3119 width=52) (actual
time=11562.968..11562.968 rows=2276 loops=1)
         ->  Nested Loop  (cost=1046393.15..1054973.98 rows=3119 width=52)
(actual time=11546.931..11558.704 rows=2276 loops=1)
               ->  HashAggregate  (cost=1046393.15..1046395.98 rows=567
width=19) (actual time=11546.892..11547.188 rows=276 loops=1)
                     ->  Nested Loop  (cost=4.78..1046285.32 rows=14377
width=19) (actual time=0.148..11537.307 rows=1854 loops=1)
                           ->  Index Scan using g_rna_acc_gene_id on
g_rna_acc a  (cost=0.00..1049.44 rows=519 width=16) (actual
time=0.026..50.006 rows=1139 loops=1)
                                 Index Cond: (gene_id < 200)
                           ->  Bitmap Heap Scan on u_all_est_mrna b
(cost=4.78..2007.57 rows=510 width=26) (actual time=7.100..10.068 rows=2
loops=1139)
                                 Recheck Cond: (("outer".accession)::text =
(b.accession)::text)
                                 ->  Bitmap Index Scan on uaem_accession
(cost=0.00..4.78 rows=510 width=0) (actual time=4.270..4.270 rows=2
loops=1139)
                                       Index Cond:
(("outer".accession)::text = (b.accession)::text)
               ->  Index Scan using g_rna_acc_gene_id on g_rna_acc d
(cost=0.00..15.04 rows=6 width=16) (actual time=0.010..0.037 rows=8
loops=276)
                     Index Cond: (d.gene_id = "outer".gene_id)
 Total runtime: 357270.873 ms
(16 rows)


\d+ u_all_est_mrna
                                                             Table
"public.u_all_est_mrna"
     Column      |       Type        |
Modifiers                                  |            Description
-----------------+-------------------+--------------------------------------
---------------------------------------+------------------------------------
 all_est_mrna_id | integer           | not null default
nextval('public.u_all_est_mrna_all_est_mrna_id_seq'::text) |
 db_id           | character varying |
| 
 seqtype         | character varying |
| 
 matches         | integer           |
| 
 mismatches      | integer           |
| 
 repmatches      | integer           |
| 
 ncount          | integer           |
| 
 qnuminsert      | integer           |
| 
 qbaseinsert     | integer           |
| 
 tnuminsert      | integer           |
| 
 tbaseinsert     | integer           |
| 
 strand          | character(1)      |
| 
 accession       | character varying |
| Genbank Accession without version.
 qsize           | integer           |
| 
 qstart          | integer           |
| 
 qend            | integer           |
| 
 chrom           | character varying |
| Chromosome, notation like "chr1"
 tsize           | integer           |
| 
 tstart          | integer           |
| Blat hit start
 tend            | integer           |
| Blat hit end
 blockcount      | integer           |
| 
 blocksizes      | character varying |
| 
 qstarts         | character varying |
| 
 tstarts         | character varying |
| 
Indexes:
    "u_all_est_mrna_pkey" PRIMARY KEY, btree (all_est_mrna_id)
    "uaem_accession" btree (accession)
    "uaem_chrom" btree (chrom)
    "uaem_db_id" btree (db_id)
    "uaem_seqtype" btree (seqtype)
    "uaem_tend_chrom" btree (tend, chrom)
    "uaem_tstart_chrom" btree (tstart, chrom)
Has OIDs: yes


=== psql 78 ===
\d+ g_rna_acc
                  Table "public.g_rna_acc"
    Column    |       Type        | Modifiers | Description
--------------+-------------------+-----------+-------------
 gene_id      | integer           |           |
 accession    | character varying | not null  |
 version      | integer           |           |
 accession_gi | integer           |           |
Indexes:
    "g_rna_acc_pkey" PRIMARY KEY, btree (accession)
    "g_rna_acc_accession" btree (accession)
    "g_rna_acc_gene_id" btree (gene_id)
Foreign-key constraints:
    "g_rna_acc_gene_id_fkey" FOREIGN KEY (gene_id) REFERENCES
g_main(gene_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
Has OIDs: no