From: Alvaro Herrera <alvherre@commandprompt.com>
To: Henrik <henke@mac.se>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query taking too long. Problem reading explain output.
Date: Thu, 4 Oct 2007 19:43:31 -0400
Message-ID: <20071004234331.GG28896@alvh.no-ip.org> (raw)
In-Reply-To: <8DEE0C69-B3A8-4ED5-AECB-4C7C398C8421@mac.se>
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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@commandprompt.com, henke@mac.se, tgl@sss.pgh.pa.us
Subject: Re: Query taking too long. Problem reading explain output.
In-Reply-To: <20071004234331.GG28896@alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox