Query with date where clause is very slow

Поиск
Список
Период
Сортировка
От Mike Christensen
Тема Query with date where clause is very slow
Дата
Msg-id 499E3263.4040606@comcast.net
обсуждение исходный текст
Ответы Re: Query with date where clause is very slow  (Scott Marlowe <scott.marlowe@gmail.com>)
Список pgsql-general
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!

Mike

В списке pgsql-general по дате отправления:

Предыдущее
От: Erik Jones
Дата:
Сообщение: Re: How to pipe the psql copy command to Unix 'Date' command
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: Query with date where clause is very slow