Re: query doesn't always follow 'correct' path..

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
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!

wkr,
Bert

On Mon, Feb 18, 2013 at 2:42 PM, Bert <biertie(at)gmail(dot)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(at)frank(dot)uvena(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
>
>
> --
> Bert Desmet
> 0477/305361
>

--
Bert Desmet
0477/305361