Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U7SeT-0006KT-2H for pgsql-sql@arkaria.postgresql.org; Mon, 18 Feb 2013 15:29:09 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1U7SeS-0004Yp-4J for pgsql-sql@arkaria.postgresql.org; Mon, 18 Feb 2013 15:29:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U7SeR-0004Yj-1P for pgsql-sql@postgresql.org; Mon, 18 Feb 2013 15:29:07 +0000 Received: from mxin.ulb.ac.be ([164.15.128.112]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U7SeJ-00008Q-6z for pgsql-sql@postgresql.org; Mon, 18 Feb 2013 15:29:06 +0000 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: ApUBAM1GIlGkD30E/2dsb2JhbAANN4ZJhVuzf4EagxIBAQEDASMERwoGCwsYCQQSCAMCAgkDAgECAQ8lERMGAgEBBYd3AwkSrTFxiCwNTIkOjFmCZYItgRMDlFGCeIRBhWWIHQ 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:28:58 +0100 Message-ID: <512248BA.2020807@ulb.ac.be> Date: Mon, 18 Feb 2013 16:28:58 +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> <512246D3.9020006@ulb.ac.be> In-Reply-To: <512246D3.9020006@ulb.ac.be> Content-Type: multipart/alternative; boundary="------------070408050909000108040209" 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. --------------070408050909000108040209 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 02/18/2013 16:20, Julien Cigar wrote: > 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 ... oops.. it's per-table and not per-column > >> 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. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. --------------070408050909000108040209 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
On 02/18/2013 16:20, Julien Cigar wrote:
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 ...

oops.. it's per-table and not per-column


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.


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