Tom Lane wrote:
Volodymyr Kostyrko<c.kworr@xxxxxxxxx> 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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
