Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U7SWb-0005kO-0V for pgsql-sql@arkaria.postgresql.org; Mon, 18 Feb 2013 15:21:01 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1U7SWa-00025G-Ek for pgsql-sql@arkaria.postgresql.org; Mon, 18 Feb 2013 15:21:00 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U7SWY-000243-On for pgsql-sql@postgresql.org; Mon, 18 Feb 2013 15:20:59 +0000 Received: from mxin.ulb.ac.be ([164.15.128.112]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U7SWW-0006wd-GD for pgsql-sql@postgresql.org; Mon, 18 Feb 2013 15:20:58 +0000 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: ApUBAAhGIlGkD30E/2dsb2JhbAANN4ZJhVuzf4EZgxIBAQEDASMERwoGCwsYCQQSCAMCAgkDAgECAQ8lERMGAgEBBYd3AwkSrTVxiCwNTIkOjFmCZYItgRMDlFGCeIRBhWWIHQ Received: from bebif01.ulb.ac.be (HELO [10.0.0.194]) ([164.15.125.4]) by smtp.ulb.ac.be with ESMTP/TLS/DHE-RSA-CAMELLIA256-SHA; 18 Feb 2013 16:20:51 +0100 Message-ID: <512246D3.9020006@ulb.ac.be> Date: Mon, 18 Feb 2013 16:20:51 +0100 From: Julien Cigar User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:17.0) Gecko/20130211 Thunderbird/17.0.2 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: query doesn't always follow 'correct' path.. References: <5122079F.6090306@frank.uvena.de> In-Reply-To: Content-Type: multipart/alternative; boundary="------------090401050506040408030003" X-Pg-Spam-Score: -4.8 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------090401050506040408030003 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 02/18/2013 15:39, Bert wrote: > Hello, > > Thanks the nice people on irc my problem is fixed. > I changed the following settings in the postgres.conf file: > default_statistics_target = 5000 -> and I analyzed the tables after > the change of course -> now I only got 2 plans anymore, in stead of 3 default_statistics_target = 5000 as a default is *way* too high. Such high values should only be set on a per-column basis ... > cpu_tuple_cost = 0.1 -> by setting this value the seq scans were > stopped, and the better index_only scan / bitmap index scan were used > for this query. > > Thank you Robe and Mabe_ for helping me with this issue! s/Mabe_/Mage_ :-) > > wkr, > Bert > > > On Mon, Feb 18, 2013 at 2:42 PM, Bert > wrote: > > Hello, > > yes, the tables are vacuumed every day with the following command: > vacuum analyze schema.table. > The last statistics were collected yesterday evening. I collected > statistics about the statistics, and I found the following: > table_name; starttime; runtime > "st_itemseat";"2013-02-17 23:48:42";"00:01:02" > "st_itemseat_45";"2013-02-17 23:35:15";"00:00:08" > "st_itemzone";"2013-02-17 23:35:33";"00:00:01" > > st_itemseat_45 is a child-partition of st_itemseat. > > They seem to be pretty much up to date I guess? > I also don't get any difference in the query plans when they are > run in the morning, or in the evening. > > I have also run the query with set seq_scan to off, and then I get > the following output: > Total query runtime: 12025 ms. > 20599 rows retrieved. > and the following plan: http://explain.depesz.com/s/yaJK > > These are 3 different plans. And the last one is blazingly fast. > That's the one I would always want to use :-) > > it's also weird that this is default plan for the biggest > partition. But the smaller the partition gets, the smaller the > partition gets. > So I don't think it has anything to do with the memory settings. > Since it already chooses this plan for the bigger partitions... > > wkr, > Bert > > > On Mon, Feb 18, 2013 at 11:51 AM, Frank Lanitz > > wrote: > > Am 18.02.2013 10:43, schrieb Bert: > > Does anyone has an idea what triggers this bad plan, and how > I can fix it? > > Looks a bit like wrong statistics. Are the statistiks for your > tables > correct? > > Cheers, > Frank > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org > ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > > > > -- > Bert Desmet > 0477/305361 > > > > > -- > Bert Desmet > 0477/305361 -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. --------------090401050506040408030003 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
On 02/18/2013 15:39, Bert wrote:
Hello,

Thanks the nice people on irc my problem is fixed.
I changed the following settings in the postgres.conf file:
default_statistics_target = 5000 -> and I analyzed the tables after the change of course -> now I only got 2 plans anymore, in stead of 3

default_statistics_target = 5000 as a default is *way* too high. Such high values should only be set on a per-column basis ...

cpu_tuple_cost = 0.1 -> by setting this value the seq scans were stopped, and the better index_only scan / bitmap index scan were used for this query.

Thank you Robe and Mabe_ for helping me with this issue!

s/Mabe_/Mage_ :-)


wkr,
Bert


On Mon, Feb 18, 2013 at 2:42 PM, Bert <biertie@gmail.com> wrote:
Hello,

yes, the tables are vacuumed every day with the following command: vacuum analyze schema.table.
The last statistics were collected yesterday evening. I collected statistics about the statistics, and I found the following:
table_name; starttime; runtime
"st_itemseat";"2013-02-17 23:48:42";"00:01:02"
"st_itemseat_45";"2013-02-17 23:35:15";"00:00:08"
"st_itemzone";"2013-02-17 23:35:33";"00:00:01"

st_itemseat_45 is a child-partition of st_itemseat.

They seem to be pretty much up to date I guess?
I also don't get any difference in the query plans when they are run in the morning, or in the evening.

I have also run the query with set seq_scan to off, and then I get the following output:
Total query runtime: 12025 ms.
20599 rows retrieved.
and the following plan: http://explain.depesz.com/s/yaJK

These are 3 different plans. And the last one is blazingly fast. That's the one I would always want to use :-)

it's also weird that this is default plan for the biggest partition. But the smaller the partition gets, the smaller the partition gets.
So I don't think it has anything to do with the memory settings. Since it already chooses this plan for the bigger partitions...

wkr,
Bert


On Mon, Feb 18, 2013 at 11:51 AM, Frank Lanitz <frank@frank.uvena.de> wrote:
Am 18.02.2013 10:43, schrieb Bert:
> Does anyone has an idea what triggers this bad plan, and how I can fix it?

Looks a bit like wrong statistics. Are the statistiks for your tables
correct?

Cheers,
Frank


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



--
Bert Desmet
0477/305361



--
Bert Desmet
0477/305361 


-- 
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--------------090401050506040408030003--