On Thu, May 4, 2017 at 3:52 AM, <jesse.hietanen@xxxxxxxxxxx> wrote:
Hi,
I have a performance problem with my query. As a simplified example, I have a table called Book, which has three columns: id, released (timestamp) and author_id. I have a need to search for the latest books released by multiple authors, at a specific point in the history. This could be latest book between beginning of time and now, or latest book released last year etc. In other words, only the latest book for each author, in specific time window. I have also a combined index for released and author_id columns.
As far as the query itself, I suspect you are paying a penalty for the to_timestamp() calls. Try the same query with hard-coded timestamps:
"AND released<='2017-05-05 00:00:00' AND released>='1970-01-01 00:00:00'"
"AND released<='2017-05-05 00:00:00' AND released>='1970-01-01 00:00:00'"
If you need these queries to be lightning fast then this looks like a good candidate for using Materialized Views: https://www.postgresql.org/docs/current/static/sql-creatematerializedview.html
