What are the latest Odds?

Question

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).

Answer 1

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.
Detailed explanation and links in this related answer on SO:

• Select first row in each GROUP BY group?

Answer 2

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