6
\$\begingroup\$

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

\$\endgroup\$
4
\$\begingroup\$

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:

\$\endgroup\$
4
\$\begingroup\$

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
\$\endgroup\$
  • 1
    \$\begingroup\$ Interesting solution. I wish I could see the differences in query plans. \$\endgroup\$ – RubberDuck Oct 9 '14 at 0:07
  • \$\begingroup\$ 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. \$\endgroup\$ – Phrancis Oct 9 '14 at 0:27
  • \$\begingroup\$ 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. \$\endgroup\$ – Martol1ni Oct 9 '14 at 13:50
  • \$\begingroup\$ @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? \$\endgroup\$ – Phrancis Oct 9 '14 at 14:21
  • \$\begingroup\$ I'm sorry - that's what the current query is doing, I've not edited my question. \$\endgroup\$ – Martol1ni Oct 10 '14 at 7:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.