scenario with a slow query

Hi all.

Maybe I'm missing something but I have found a case when planner is
unoptimal.

# Creating table

create table test_stat(id smallint, count smallint, date date);

# Filling table, sorry for php
<?php
$db = new PDO('');

$insert = $db->prepare('insert into test_stat (id, count, date) values
(?, 1, to_timestamp(?)::date)');

$today = mktime(0, 0, 0);

$db->beginTransaction();
for($i = 0; $i < 1500000; $i++) {
$insert(rand(0, 1000), $today);
}
$db->commit();
?>

And now goes the query.

select * from (
select id, sum(count) as today
from test_stat
where date = now()::date
group by id
)a natural full join (
select id, sum(count) as lastday
from test_stat
where date = (now() - interval '1 day')::date
group by id
)b natural full join (
select id, sum(count) as week
from test_stat
where date_trunc('week', now()) = date_trunc('week', date)
and date <> now()::date
group by id
)c natural full join (
select id, sum(count) as whole
from test_stat
where date <> now()::date
or date is null
group by id
)d
where id = ?;

Which yields this explain:

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Full Join (cost=94830.30..126880.73 rows=5 width=48)
Hash Cond: (COALESCE(COALESCE(public.test_stat.id,
public.test_stat.id), public.test_stat.id) = public.test_stat.id)
Filter: (COALESCE(COALESCE(COALESCE(public.test_stat.id,
public.test_stat.id), public.test_stat.id), public.test_stat.id) = 1)
-> Hash Full Join (cost=91193.49..123240.10 rows=1001 width=36)
Hash Cond: (COALESCE(public.test_stat.id, public.test_stat.id)
= public.test_stat.id)
-> Hash Full Join (cost=40259.93..72302.74 rows=1001 width=24)
Hash Cond: (public.test_stat.id = public.test_stat.id)
-> GroupAggregate (cost=0.01..32042.63 rows=1 width=8)
-> Index Scan using test_stat__id_date on
test_stat (cost=0.01..32042.61 rows=1 width=8)
Index Cond: (date = ((now() - '1
day'::interval))::date)
-> Hash (cost=40247.41..40247.41 rows=1001 width=12)
-> HashAggregate (cost=40227.39..40237.40
rows=1001 width=8)
-> Seq Scan on test_stat
(cost=0.00..33089.97 rows=1427484 width=8)
Filter: (date = (now())::date)
-> Hash (cost=50933.55..50933.55 rows=1 width=12)
-> HashAggregate (cost=50933.53..50933.54 rows=1 width=8)
-> Seq Scan on test_stat (cost=0.00..50933.52
rows=1 width=8)
Filter: ((date <> (now())::date) AND
(date_trunc('week'::text, now()) = date_trunc('week'::text,
(date)::timestamp with time zone)))
-> Hash (cost=3636.80..3636.80 rows=1 width=12)
-> GroupAggregate (cost=34.80..3636.79 rows=1 width=8)
-> Bitmap Heap Scan on test_stat (cost=34.80..3636.78
rows=1 width=8)
Recheck Cond: (id = 1)
Filter: ((date <> (now())::date) OR (date IS NULL))
-> Bitmap Index Scan on test_stat__id_date
(cost=0.00..34.80 rows=1378 width=0)
Index Cond: (id = 1)
(25 rows)

The part which yields a Seq scan is a:
select id, sum(count) as today
from test_stat
where date = now()::date
group by id

And it uses index when executed like this:
select * from (
select id, sum(count) as today
from test_stat
where date = now()::date
group by id
)a where id = 1

Where am I wrong here? What I have done so this subquery can't inherit
constraint from outer query?

--
Sphinx of black quartz judge my vow.