From: tv@fuzzy.cz
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Why dose the planner select one bad scan plan.
Date: Wed, 10 Nov 2010 13:48:47 +0100
Message-ID: <507dc896562475ad862c40460520eda7.squirrel@sq.gransy.com> (raw)
In-Reply-To: <tencent_0444078C7AA9F76A491E2841@qq.com>
References: <tencent_0444078C7AA9F76A491E2841@qq.com>
> Okay, 225044.255ms VS 83813.808 ms, it obviously seems that the planner
> select one bad scan plan by default.
Actually no, the planner chose the cheapest plan (more precisely a plan
with the lowest computed cost). The first plan has a cost 600830.86 while
the second one has a cost 634901.28, so the first one is chosen.
To fix this, you'll have to tweak the cost variables, and maybe work_mem.
See this -
http://www.postgresql.org/docs/9.0/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTA...
(but I'm not sure which of those influence the Bitmap Heap Scan /
HashAggregate plans).
So you'll have to modify these values until the hash aggregate plan is
cheaper. And you don't need to reboot the machine between EXPLAIN
executions. And even if you do EXPLAIN ANALYZE it's not necessary - there
are better ways to clear the filesystem cache.
BTW this is not a bug, so it's pointless to send it to 'bugs' mailinglist.
regards
Tomas
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: tv@fuzzy.cz
Subject: Re: Why dose the planner select one bad scan plan.
In-Reply-To: <507dc896562475ad862c40460520eda7.squirrel@sq.gransy.com>
* 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