Re: scenario with a slow query

Tom Lane wrote:
> Volodymyr Kostyrko<c.kworr@gmail.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.