Re: scenario with a slow query

Tom Lane wrote:
> Volodymyr Kostyrko<c(dot)kworr(at)gmail(dot)com> writes:
>> Maybe I'm missing something but I have found a case when planner is
>> unoptimal.
>
> The planner knows next to nothing about optimizing FULL JOIN, and
> I would not recommend holding your breath waiting for it to get better
> about that, because there's basically no demand for the work that'd
> be involved. I'd suggest refactoring this query instead. A nest of
> full joins seems like a rather unintuitive way to get the result
> anyway ...

That's not about FULL JOIN, that's seems to be about all JOIN's:

select * from (
select 1 as id
)x natural left join (
select id, sum(count) as today
from test_stat
where date = now()::date group by id
)a natural left join (
select id, sum(count) as lastday
from test_stat
where date = (now() - interval '1 day')::date group by id
)b natural left join (
select id, sum(count) as week
from test_stat
where date between (now() - interval '1 day') and (now() - interval
'7 day')
group by id
)c natural left join (
select id, sum(count) as whole
from test_stat
where date <> now()::date
group by id
)d;

This query exhibits the same seq scan.

By refactoring did you mean something like this:

select
(select sum(count) from test_stat
where date = now()::date and id = 1
group by id) as today,
( select sum (count) from test_stat
where date = (now() - interval '1 day')::date and id = 1
group by id) as lastday,
( select sum(count) from test_stat
where date between (now() - interval '1 day')
and (now() - interval '7 day') and id = 1
group by id) as week,
(select sum(count) from test_stat
where date <> now()::date and id = 1
group by id) as whole;

This one works much better requiring mostly no planner involvment...
Yielding the same result though.

--
Sphinx of black quartz judge my vow.