Re: Query with date where clause is very slow - Mailing list pgsql-general

On 2009-02-20, Mike Christensen <imaudi@comcast.net> wrote:
> Hi all -
>
> I have a fairly simple query:
>
> select * from subscriptions s
> inner join notifications n on n.userid = s.userid
> inner join users u on u.userid = s.userid
> where s.subscriberid='affaa328-5b53-430e-991a-22674ede6faf'
> and n.date > (CURRENT_TIMESTAMP - INTERVAL '14 day')::date;

converting timestamp to date is moderately complex (lots of integer division)
try this instead of (CURRENT_TIMESTAMP - INTERVAL '14 day')::date;

 ('today'::date -14)

indexing notifications on (userid,date) may help significantly too,

bye.