On 3/30/20 8:56 AM, Pavel Stehule wrote:
> please, can you send explain in text form?
Sure. With work_mem = 80MB:
QUERY PLAN
po 30. 3. 2020 v 10:12 odesílatel Silvio Moioli napsal:
> On 3/30/20 8:56 AM, Pavel Stehule wrote:
> > please, can you send explain in text form?
>
> Sure. With work_mem = 80MB:
>
>
> QUERY PLAN
>
> -
On 3/30/20 12:12 PM, Pavel Stehule wrote:
> Do you have some planner variables changed - like seq_page_cost?
That one was not changed but another one is - cpu_tuple_cost (to 0.5). Indeed
bringing it back to its default does improve the query time significantly:
po 30. 3. 2020 v 15:09 odesílatel Silvio Moioli napsal:
> On 3/30/20 12:12 PM, Pavel Stehule wrote:
> > Do you have some planner variables changed - like seq_page_cost?
>
> That one was not changed but another one is - cpu_tuple_cost (to 0.5).
> Indeed bringing it back to its default does improve
>
> Is that an unreasonable value? For the sake of this discussison, I am
> targeting fairly average bare-metal SSD-backed servers with recent CPUs
> (let's say 3 year old maximum), with ample available RAM.
>
if you have SSD, then you can decrease RANDOM_PAGE_COST to 2 maybe 1.5. But
probably the
Pavel Stehule writes:
> po 30. 3. 2020 v 10:12 odesílatel Silvio Moioli napsal:
>> -> Sort (cost=299108.00..300335.41 rows=490964 width=79)
>> (actual time=6475.147..6494.111 rows=462600 loops=1)
>> Output: rhnpackagecapability_1.name,
>> rhnpackagecapability_1.version,
po 30. 3. 2020 v 18:02 odesílatel Tom Lane napsal:
> Pavel Stehule writes:
> > po 30. 3. 2020 v 10:12 odesílatel Silvio Moioli napsal:
> >> -> Sort (cost=299108.00..300335.41 rows=490964 width=79)
> >> (actual time=6475.147..6494.111 rows=462600 loops=1)
> >> Output: rhnpackag
Pavel Stehule writes:
> CTE scan has only 1100 rows, public.rhnpackagecapability has 490964 rows.
> But planner does hash from public.rhnpackagecapability table. It cannot be
> very effective.
[ shrug... ] Without stats on the CTE output, the planner is very
leery of putting it on the inside of
po 30. 3. 2020 v 18:36 odesílatel Tom Lane napsal:
> Pavel Stehule writes:
> > CTE scan has only 1100 rows, public.rhnpackagecapability has 490964
> rows.
> > But planner does hash from public.rhnpackagecapability table. It cannot
> be
> > very effective.
>
> [ shrug... ] Without stats on the
