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


Fri, 21 Mar 2003 12:53:44 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
date index problems
> Surely the planner is aware that '2003-01-01'::date - interval '1 week' is
a
> constant???

Actually, turns out that the planner was smarter than me I think. 2003-01-01
occurs very rarely in the system, but other dates occupy 1/7th of the table,
so it's not so easy to plan...

Chris


Fri, 21 Mar 2003 12:59:27 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: date index problems
On Fri, 21 Mar 2003, Christopher Kings-Lynne wrote:

> 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)

I presume the type of plan_next is 'date'. Does casting '2003-01-01'::date
- interval '1 week' to date help?

Gavin


Fri, 21 Mar 2003 16:03:34 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: date index problems
Gavin Sherry <swm@linuxworld.com.au> writes:
> I presume the type of plan_next is 'date'. Does casting '2003-01-01'::date
> - interval '1 week' to date help?

Easier would be
	'2003-01-01'::date - 7
which yields a date to start with.  But yeah, date minus interval yields
a timestamp, which will not automatically downconvert to a date, thus
you don't get to use an index on date.

			regards, tom lane

Fri, 21 Mar 2003 00:46:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: date index problems 