From: Julien Cigar <jcigar@ulb.ac.be>
To: pgsql-sql@postgresql.org
Subject: Re: query doesn't always follow 'correct' path..
Date: Mon, 18 Feb 2013 16:28:58 +0100
Message-ID: <512248BA.2020807@ulb.ac.be> (raw)
In-Reply-To: <512246D3.9020006@ulb.ac.be>
References: <CAFCtE1nDc3t-2eXAuHatanQREvYnHRkEH2oGxU-nmWLpDj=Lpw@mail.gmail.com>
<5122079F.6090306@frank.uvena.de>
<CAFCtE1=2C3-z020=1Y-Cf3GJEmex8hp3Lg3sqJSzBTRA72AwRg@mail.gmail.com>
<CAFCtE1nT_SP-DOrJR0FT2ua4xBxZ5uUGyUnbiNV7VVXA9b8pqQ@mail.gmail.com>
<512246D3.9020006@ulb.ac.be>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
>> <mailto: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 <mailto: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
>> <mailto: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.
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-sql@postgresql.org
Cc: jcigar@ulb.ac.be
Subject: Re: query doesn't always follow 'correct' path..
In-Reply-To: <512248BA.2020807@ulb.ac.be>
* 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