Обсуждение: query doesn't always follow 'correct' path..

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

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@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.

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.

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