scenario with a slow query - Mailing list pgsql-general

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.