Skip site navigation (1) Skip section navigation (2)

Re: sequence scan problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: John Beaver <john(dot)e(dot)beaver(at)gmail(dot)com>
Cc: Pgsql-Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: sequence scan problem
Date: 2008-06-30 03:31:35
Message-ID: 10566.1214796695@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
John Beaver <john(dot)e(dot)beaver(at)gmail(dot)com> writes:
> Can anyone explain this? There should only ever be a maximum of about 50 
> rows returned when the query is executed.

Is the estimate that 197899 rows of gene_prediction_view have
go_term_ref = 2 about right?  If not, then we need to talk about
fixing your statistics.  If it is in the right ballpark then I do
not see *any* plan for this query that runs in small time.
The only way to avoid a seqscan on functional_linkage_scores would
be to do 198K^2 index probes into it, one for each combination of
matching fs1 and fs2 rows; I can guarantee you that that's not a win.

The fact that the planner is estimating 352770803726 result rows
compared to your estimate of 50 offers some hope that it's a stats
problem, but ...

			regards, tom lane

In response to

pgsql-performance by date

Next:From: Nimesh SatamDate: 2008-06-30 04:20:15
Subject: Out of memory for Select query.
Previous:From: John BeaverDate: 2008-06-29 22:32:03
Subject: Re: sequence scan problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group