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.
First, I tried a simple query that selects maximum value of released and the author_id, which are grouped by the author_id (then later do a join by these author_id, released columns to get the whole rows). Performance of this query is pretty bad (Execution time around 250-300ms for five authors). See query and query plan in the link below:
https://gist.github.com/jehie/ca9fac16b6e3c19612d815446a0e1bc0
The execution time seems to grow linearly when the number of author_ids increase (50ms per author_id). I don’t completely understand why it takes so long for this query to execute and why it does not use the directional index scan?
I also tried second query using limit (where I can only ask for one author_id at a time, so cannot use this directly when searching for books of multiple author), which performs nicely (0.2ms):
https://gist.github.com/jehie/284e7852089f6debe22e05c63e73027f
So, any ideas how to make multiple-author lookups (like in the first query) perform better? Or any other ideas?
Here is the SQL to create the Table, Index, generate some test data and both queries:
https://gist.github.com/jehie/87665c03bee124f8a96de24cae798194
Thanks,
Jesse