Date: Thu, 4 Oct 2007 19:43:31 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Henrik <henke@mac.se>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: Query taking too long. Problem reading explain
	output.
Message-ID: <20071004234331.GG28896@alvh.no-ip.org>
References: <AD9A1A41-C522-46A5-9756-99410F564EA7@mac.se>
	<15793.1191420918@sss.pgh.pa.us>
	<2E2D02AB-123A-474D-A197-91A61DC64570@mac.se>
	<20071004123038.GA6176@alvh.no-ip.org>
	<8DEE0C69-B3A8-4ED5-AECB-4C7C398C8421@mac.se>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <8DEE0C69-B3A8-4ED5-AECB-4C7C398C8421@mac.se>
User-Agent: Mutt/1.5.16 (2007-06-11)

Henrik wrote:

> Correct. I changed the statistics to 500 in tbl_file.file_name  and now the 
> statistics is better. But now my big seq scan on tbl_file_Structure back 
> and I don't know why.

Hmm, I think the problem here is that it needs to fetch ~200000 tuples
from tbl_file_structure one way or the other.  When it misestimated the
tuples from tbl_file it thought it would only need to do the indexscan
in tbl_file_structure a few times, but now it realizes that it needs to
do it several thousands of times and it considers the seqscan to be
cheaper.

Perhaps you would benefit from a higher effective_cache_size or a lower
random_page_cost (or both).

I think this is a problem in the optimizer: it doesn't correctly take
into account the fact that the upper pages of the index are most likely
to be cached.  This has been discussed a lot of times but it's not a
simple problem to fix.

-- 
Alvaro Herrera                 http://www.amazon.com/gp/registry/CTMLCN8V17R4
Este mail se entrega garantizadamente 100% libre de sarcasmo.