What are the latest Odds?

The main problem the following query is solving, is the following:

Each Offer has multiple Odds (Odds has foreignkey to Offer). For each offer I want the latest Odds (ORDER BY time).

I'm pretty sure this is too heavy, so any input would be great.

SELECT "odds_odds"."id", "odds_odds"."offer_id", "odds_odds"."time", "odds_odds"."o1", "odds_odds"."o2", "odds_odds"."o3", "odds_odds"."o4", MAX(T3."time") AS "max_time"
FROM "odds_odds"
LEFT OUTER JOIN "odds_offer" ON ( "odds_odds"."offer_id" = "odds_offer"."id" )
LEFT OUTER JOIN "odds_odds" T3 ON ( "odds_offer"."id" = T3."offer_id" )
GROUP BY "odds_odds"."id", "odds_odds"."offer_id", "odds_odds"."time", "odds_odds"."o1", "odds_odds"."o2", "odds_odds"."o3", "odds_odds"."o4"
HAVING "odds_odds"."time" =  MAX(T3."time")


This is constructed in Django using the following syntax:

Odds.objects.annotate(
max_time=Max('offer__odds__time')
).filter(
time=F('max_time')
)


This is my schema:

odds_offer
id (int), .....

odds_odds
id (int), time (datetime), offer_id(int), .....


Now what I want to retrieve The Odds row with the latest time for each Offer. There is a unique constraint on (time, offer_id). One Offer can have multiple odds (historic odds).

This can be radically simpler and faster with the Postgres specific DISTINCT ON:

SELECT DISTINCT ON (offer_id)
offer_id, id, time, o1, o2, o3, o4, time  -- or just * to include all columns
FROM   odds_odds
ORDER  BY offer_id, time DESC;


You don't need to touch the table odds_offer at all. Note that you only get odds of offers that have at least one related row in odds_odds this way (obviously). If you'd want to include offers without any related odds somehow, you'd need to do a bit more, but that doesn't seem to be the case.

Depending on data distribution there may be even faster variants.

Formatting

I think your query could really use some white space to make it easier to read. I realize there is no official standard for SQL, but here is my preferred style:

SELECT
"odds_odds"."id",
"odds_odds"."offer_id",
"odds_odds"."time",
"odds_odds"."o1",
"odds_odds"."o2",
"odds_odds"."o3",
"odds_odds"."o4",
MAX(T3."time") AS "max_time"
FROM "odds_odds"
LEFT OUTER JOIN "odds_offer"
ON ( "odds_odds"."offer_id" = "odds_offer"."id" )
LEFT OUTER JOIN "odds_odds" T3
ON ( "odds_offer"."id" = T3."offer_id" )
GROUP BY
"odds_odds"."id",
"odds_odds"."offer_id",
"odds_odds"."time",
"odds_odds"."o1",
"odds_odds"."o2",
"odds_odds"."o3",
"odds_odds"."o4"
HAVING "odds_odds"."time" =  MAX(T3."time");


Table aliases

A table named "odds_odds" seems odd to me, but you could save some typing by aliasing as odds at least. Also not sure why you are using quotation marks everywhere, but maybe it's a requirement for your system. I have a Postgres database and never needed those, so it just feels like noise.

Simplifying

You said:

For each offer I want the latest Odds (ORDER BY time).

What I see your code doing is what seems like unnecessary computation to find MAX(), where you likely could entirely remove your aggregation and write something along those lines:

SELECT
"odds_odds"."id",
"odds_odds"."offer_id",
"odds_odds"."time",
"odds_odds"."o1",
"odds_odds"."o2",
"odds_odds"."o3",
"odds_odds"."o4",
T3."time" AS "max_time"
FROM "odds_odds"
LEFT OUTER JOIN "odds_offer"
ON ( "odds_odds"."offer_id" = "odds_offer"."id" )
LEFT OUTER JOIN "odds_odds" T3
ON ( "odds_offer"."id" = T3."offer_id" )
ORDER BY "odds_odds"."time" DESC
LIMIT 1; -- or however many you need

• Interesting solution. I wish I could see the differences in query plans. Oct 9, 2014 at 0:07
• Me too. I can't imagine that sorting the result set (instead of computing the source set) wouldn't be faster, but I will try to test it out. Oct 9, 2014 at 0:27
• Thanks. However, this gives me only the Odds with the highest "time" field - which is not what I want. For each Offer in the database - I want the Odds from that Offer with the "highest" time - that's why I'm using the Max. Oct 9, 2014 at 13:50
• @Martol1ni It would have helped if you mentioned that in your question. You said "latest time" which to most means the most recent time. If your "time" column a cumulative time rather than an actual time/date? Oct 9, 2014 at 14:21
• I'm sorry - that's what the current query is doing, I've not edited my question. Oct 10, 2014 at 7:36