date index problems

From: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
To: "Hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: date index problems
Date: 2003-03-21 04:53:44
Message-ID: 094501c2ef65$d9d49c00$6500a8c0@fhp.internal
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

This behaviour I find unusual:

usa=# explain analyze select user_id, plan_next from users_profiles where
plan_next = '2003-01-01';
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------------------------------------
Index Scan using users_profiles_plan_next_key on users_profiles
(cost=0.00..614.01 rows=228 width=8) (actual time=0.41..0.41 rows=0 loops=1)
Index Cond: (plan_next = '2003-01-01'::date)
Total runtime: 0.49 msec
(3 rows)

usa=# explain analyze select user_id, plan_next from users_profiles where
plan_next = '2003-01-01'::date - interval '1 week';
QUERY PLAN
----------------------------------------------------------------------------
-----------------------------------
Seq Scan on users_profiles (cost=0.00..1076.08 rows=184 width=8) (actual
time=109.48..109.48 rows=0 loops=1)
Filter: ((plan_next)::timestamp without time zone = '2002-12-25
00:00:00'::timestamp without time zone)
Total runtime: 109.56 msec
(3 rows)

Surely the planner is aware that '2003-01-01'::date - interval '1 week' is a
constant???

Chris

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Christopher Kings-Lynne 2003-03-21 04:59:27 Re: date index problems
Previous Message Bruce Momjian 2003-03-21 04:26:39 Re: Nested transactions: low level stuff