Re: query doesn't always follow 'correct' path.. - Mailing list pgsql-sql

From Bert
Subject Re: query doesn't always follow 'correct' path..
Date
Msg-id CAFCtE1=2C3-z020=1Y-Cf3GJEmex8hp3Lg3sqJSzBTRA72AwRg@mail.gmail.com
Whole thread Raw
In response to Re: query doesn't always follow 'correct' path..  (Frank Lanitz <frank@frank.uvena.de>)
Responses Re: query doesn't always follow 'correct' path..
List pgsql-sql
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

pgsql-sql by date:

Previous
From: Sebastien FLAESCH
Date:
Subject: HP and libxnet ...
Next
From: Bert
Date:
Subject: Re: query doesn't always follow 'correct' path..