Search Postgresql Archives

Re: scenario with a slow query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux