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

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(at)gmail(dot)com
> <mailto: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 <mailto: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
> <mailto: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

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