DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=BeW3ZNepOGzwRTS+LSR7GK0UwsV/uy2Bk8tHld6tcbeTEJqwx90QKkYzrEmGyajuwz
	bittDuGtnJbCd0ba4hWOZZdTJaGX7Vvr2O6hxljK19slKbouTegmrRw+AguLe9kLVPGa
	dPCXLodaWHVb1+YUxrck3OfbSgDn4VzMp1S6A=
Message-ID: <4868BC74.6020006@gmail.com>
Date: Mon, 30 Jun 2008 06:59:00 -0400
From: John Beaver <john.e.beaver@gmail.com>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)
MIME-Version: 1.0
To: Jeremy Harris <jgh@wizmail.org>
CC: Pgsql-Performance <pgsql-performance@postgresql.org>
Subject: Re: sequence scan problem
References: <48680418.7030708@gmail.com> <48680860.2000300@wizmail.org>
In-Reply-To: <48680860.2000300@wizmail.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Ok, here's the explain analyze result. Again, this is Postgres 8.3.3 and 
I vacuumed-analyzed both tables directly after they were created.


# explain analyze select fls.function_verified, fls.score, 
fls.go_category_group_ref, fs1.gene_ref, fs1.function_verified_exactly, 
fs2.gene_ref, fs2.function_verified_exactly from 
functional_linkage_scores fls, gene_prediction_view fs1, 
gene_prediction_view fs2 where fls.gene_ref1 = fs1.gene_ref and 
fls.gene_ref2 = fs2.gene_ref and fs1.go_term_ref = 2 and fs2.go_term_ref 
= 2;
                                                                                              
QUERY 
PLAN                                                                                              

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=1399203593.41..6702491234.74 rows=352770803726 
width=22) (actual time=6370194.467..22991303.434 rows=15610535128 loops=1)
   Merge Cond: (fs2.gene_ref = fls.gene_ref2)
   ->  Index Scan using gene_prediction_view_gene_ref on 
gene_prediction_view fs2  (cost=0.00..12111899.77 rows=197899 width=5) 
(actual time=29.592..469838.583 rows=180629 loops=1)
         Index Cond: (go_term_ref = 2)
   ->  Materialize  (cost=1399069432.20..1483728633.52 rows=6772736105 
width=21) (actual time=6370164.864..16623552.417 rows=15610535121 loops=1)
         ->  Sort  (cost=1399069432.20..1416001272.47 rows=6772736105 
width=21) (actual time=6370164.860..13081970.248 rows=1897946790 loops=1)
               Sort Key: fls.gene_ref2
               Sort Method:  external merge  Disk: 61192240kB
               ->  Merge Join  (cost=40681244.97..154286110.62 
rows=6772736105 width=21) (actual time=592112.778..2043161.851 
rows=1897946790 loops=1)
                     Merge Cond: (fs1.gene_ref = fls.gene_ref1)
                     ->  Index Scan using gene_prediction_view_gene_ref 
on gene_prediction_view fs1  (cost=0.00..12111899.77 rows=197899 
width=5) (actual time=0.015..246613.129 rows=180644 loops=1)
                           Index Cond: (go_term_ref = 2)
                     ->  Materialize  (cost=40586010.10..43490582.70 
rows=232365808 width=20) (actual time=592112.755..1121366.375 
rows=1897946783 loops=1)
                           ->  Sort  (cost=40586010.10..41166924.62 
rows=232365808 width=20) (actual time=592112.721..870349.308 
rows=232241678 loops=1)
                                 Sort Key: fls.gene_ref1
                                 Sort Method:  external merge  Disk: 
7260856kB
                                 ->  Seq Scan on 
functional_linkage_scores fls  (cost=0.00..3928457.08 rows=232365808 
width=20) (actual time=14.221..86455.902 rows=232241678 loops=1)
 Total runtime: 24183346.271 ms
(18 rows)


Jeremy Harris wrote:
> John Beaver wrote:
>> I'm having a strange problem with a query. The query is fairly 
>> simple, with a few constants and two joins. All relevant columns 
>> should be indexed, and I'm pretty sure there aren't any type 
>> conversion issues. But the query plan includes a fairly heavy seq 
>> scan. The only possible complication is that the tables involved are 
>> fairly large - hundreds of millions of rows each.
>>
>> Can anyone explain this? There should only ever be a maximum of about 
>> 50 rows returned when the query is executed.
>
> You didn't say when you last vacuumed?
> If there should only be 50 rows returned then the estimates from the
> planner are way out.
>
> If that doesn't help, we'll need version info, and (if you can afford
> the time) an "explain analyze"
>
> Cheers,
>  Jeremy
>