On Thu, Feb 19, 2009 at 9:32 PM, Mike Christensen <imaudi@xxxxxxxxxxx> 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; > > It runs fairly slow (about 1200ms) with 10,000 rows in "users" and 200,000 > rows in "subscriptions" and 500,000 rows in "notifications" and I'm trying > to figure out a way to speed this guy up. However, from what I can tell the > WHERE clause with the date is the thing really being a hog here. > > If I take out the last and just return all dates, the query runs in about > 300ms. I do have an index on notifications.date, btw.. > > Can someone point out exactly why this is running so slow? Perhaps it's > generating a new interval for each row or something? Is there a better way > to query rows by date? Thanks! What does explain analyze select ... have to say about each? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
