scenario with a slow query

[Volodymyr Kostyrko <c.kworr@xxxxxxxxx>]

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.

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general