1
\$\begingroup\$

The following query returns the latest Odds for each Offer based on the timestamp on the Odds. However, the query takes an average of 1497ms - and I'm sincerely asking for help to optimize it.

SELECT DISTINCT ON ("odds_odds"."offer_id")
    "odds_odds"."id", "odds_odds"."o1", "odds_odds"."o2", "odds_odds"."o3", "odds_offer"."odds_type_id", "odds_offer"."match_id", "odds_offer"."bookmaker_id" FROM "odds_odds"
INNER JOIN "odds_offer" ON ( "odds_odds"."offer_id" = "odds_offer"."id" )
INNER JOIN "odds_match" ON ( "odds_offer"."match_id" = "odds_match"."id" ) WHERE ("odds_match"."start_time" >= ? AND "odds_offer"."match_id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND ("odds_offer"."flags" = ? OR ("odds_offer"."flags" = ?
AND "odds_offer"."last_verified" >= ?)) AND NOT ((("odds_odds"."o1" = ? AND "odds_odds"."o1" IS NOT NULL) OR ("odds_odds"."o2" = ? AND "odds_odds"."o2" IS NOT NULL))))
ORDER BY "odds_odds"."offer_id" ASC, "odds_odds"."time" DESC

These are the stats from Heroku's log:

1497ms Avg. time
0/min Throughput
29ms I/O time

Here is the output from EXPLAIN (ANALYZE, VERBOSE, BUFFERS):

Unique  (cost=342394.61..342394.61 rows=3 width=46) (actual time=46453.678..46703.724 rows=31430 loops=1)
   Output: odds_odds.id, odds_odds.o1, odds_odds.o2, odds_odds.o3, odds_offer.odds_type_id, odds_offer.match_id, odds_offer.bookmaker_id, odds_odds.offer_id, odds_odds."time"
   Buffers: shared hit=482834 read=24654 dirtied=9, temp read=1902 written=1902
   I/O Timings: read=93.940
   ->  Sort  (cost=342394.61..342394.61 rows=3 width=46) (actual time=46453.674..46580.738 rows=250485 loops=1)
         Output: odds_odds.id, odds_odds.o1, odds_odds.o2, odds_odds.o3, odds_offer.odds_type_id, odds_offer.match_id, odds_offer.bookmaker_id, odds_odds.offer_id, odds_odds."time"
         Sort Key: odds_odds.offer_id, odds_odds."time"
         Sort Method: external merge  Disk: 15208kB
         Buffers: shared hit=482834 read=24654 dirtied=9, temp read=1902 written=1902
         I/O Timings: read=93.940
         ->  Nested Loop  (cost=0.11..342394.60 rows=3 width=46) (actual time=8.455..45710.497 rows=250485 loops=1)
               Output: odds_odds.id, odds_odds.o1, odds_odds.o2, odds_odds.o3, odds_offer.odds_type_id, odds_offer.match_id, odds_offer.bookmaker_id, odds_odds.offer_id, odds_odds."time"
               Buffers: shared hit=482827 read=24654 dirtied=9
               I/O Timings: read=93.940
               ->  Nested Loop  (cost=0.00..342383.74 rows=1 width=16) (actual time=8.409..44383.739 rows=33436 loops=1)
                     Output: odds_offer.odds_type_id, odds_offer.match_id, odds_offer.bookmaker_id, odds_offer.id
                     Join Filter: (odds_offer.match_id = odds_match.id)
                     Rows Removed by Join Filter: 66691138
                     Buffers: shared hit=38450 read=24654 dirtied=9
                     I/O Timings: read=93.940
                     ->  Seq Scan on public.odds_offer  (cost=0.00..341815.13 rows=3222 width=16) (actual time=0.135..2791.383 rows=33922 loops=1)
                           Output: odds_offer.odds_type_id, odds_offer.match_id, odds_offer.bookmaker_id, odds_offer.id
                           Filter: ((odds_offer.flags OR ((NOT odds_offer.flags) AND (odds_offer.last_verified >= '2015-04-10 13:43:30.556949+00'::timestamp with time zone))) AND (odds_offer.match_id = ANY ('{2725665,2725667,2725670,2725671,2725674,2725668,2723416,2723423,2723421,2723422,3006845,3006846,3006848,2726643,2726644,2730552,2731247,2731250,2731248,2731249,2733487,2733490,2733740,2733741,2733742,2733743,2734281,2734286,2734288,2736599,2736600,2735768,2735770,2735769,2735773,2735767,2735772,2737269,2738308,2738309,3018437,3018441,3094187,3091835,2740985,2740982,2741303,2741304,2741309,2768481,2768487,2768483,2768482,2768488,2768485,2768484,2742802,3044541,2746058,2746057,2746063,2749068,2753763,2750377,2748517,3065622,2762436,2762437,2762439,2764009,2764320,3016595,2935111,2772316,2772318,2781140,2781144,2780837,2788433,3050601,3094643,3094641,2801042,2801044,2801047,2801048,2801049,2795387,2795390,2795388,2795389,2795395,2795391,2795392,2795394,2821571,2821729,2821730,2821731,2821732,2821733,2821735,2821736,2821738,2821739,2821740,2880288,2829676,2829678,2829679,2829680,2829681,2829682,2829683,2829685,3053895,2839492,2839497,2839501,2850609,2877859,2927855,2927848,2927852,2927854,2927850,3072825,2953872,2953874,3089862,3117521,3007435,3007428,3007427,3007430,3007436,3007444,3007445,3007442,3007446,3007447,3007448,3007429,3007449,3007431,2988273,3047885,3047887,3014213,3018787,3018790,3102572,3119336,3040014,3040020,3043864,3043861,3043862,3043865,3045244,3045245,3045246,3045247,3045248,3045249,3045250,3045251,3045252,3054436,3050931,3063078,3063079,3063080,3063081,3063082,3063083,3057971,3064730,3064731,3064732,3064733,3064734,3064735,3111903,3120490,3120446,3121373}'::integer[])))
                           Rows Removed by Filter: 5523694
                           Buffers: shared hit=38116 read=24654 dirtied=9
                           I/O Timings: read=93.940
                     ->  Materialize  (cost=0.00..517.06 rows=4 width=4) (actual time=0.001..0.511 rows=1967 loops=33922)
                           Output: odds_match.id
                           Buffers: shared hit=334
                           ->  Seq Scan on public.odds_match  (cost=0.00..517.05 rows=4 width=4) (actual time=7.797..8.636 rows=1967 loops=1)
                                 Output: odds_match.id
                                 Filter: (odds_match.start_time >= '2015-04-10 13:53:30.556949+00'::timestamp with time zone)
                                 Rows Removed by Filter: 50333
                                 Buffers: shared hit=334
               ->  Index Scan using odds_odds_offer_id on public.odds_odds  (cost=0.11..10.68 rows=60 width=34) (actual time=0.014..0.033 rows=7 loops=33436)
                     Output: odds_odds.id, odds_odds.o1, odds_odds.o2, odds_odds.o3, odds_odds.offer_id, odds_odds."time"
                     Index Cond: (odds_odds.offer_id = odds_offer.id)
                     Filter: (((odds_odds.o1 <> 0::numeric) OR (odds_odds.o1 IS NULL)) AND ((odds_odds.o2 <> 0::numeric) OR (odds_odds.o2 IS NULL)))
                     Rows Removed by Filter: 2
                     Buffers: shared hit=444377
 Total runtime: 46726.458 ms
\$\endgroup\$
3
  • 3
    \$\begingroup\$ EXPLAIN (ANALYZE, VERBOSE, BUFFERS) please. \$\endgroup\$ Apr 10 '15 at 12:06
  • 1
    \$\begingroup\$ Added EXPLAIN ANALYZE. Thanks for reviewing. \$\endgroup\$
    – Martol1ni
    Apr 10 '15 at 14:00
  • \$\begingroup\$ To give a proper answer I would need to see: Postgres version, table definitions and cardinalities (how many rows) for my answer. In particular: how many distinct offer_id and how many rows per offer_id in each table. Consider instructions in the related tag info on SO: stackoverflow.com/tags/postgresql-performance/info \$\endgroup\$ Apr 19 '15 at 0:54
2
\$\begingroup\$

Step 1: Human-readable, clean format

Especially for "code review". Most of your query string is noise. I also removed redundant parentheses:

SELECT DISTINCT ON (o.offer_id)
       o.id, o.o1, o.o2, o.o3, f.odds_type_id, f.match_id, f.bookmaker_id
FROM   odds_offer f
JOIN   odds_odds  o ON o.offer_id = f.id
JOIN   odds_match m ON m.id = f.match_id
WHERE  NOT (o.o1 = ? AND o.o1 IS NOT NULL OR
            o.o2 = ? AND o.o2 IS NOT NULL)
AND    f.match_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
AND   (f.flags = ? OR
       f.flags = ? AND f.last_verified >= ?)
AND    m.start_time >= ?
ORDER  BY o.offer_id, o."time" DESC;

Step 2: Simplify, fix

The expression o.o1 = ? AND o.o1 IS NOT NULL can be reduced to o.o1 = ?: if that evaluates to true, o.o1 cannot be NULL.

And NOT (o.o1 = ? OR o.o2 = ?) can be replaced with o.o1 <> ? AND o.o2 <> ?

SELECT DISTINCT ON (f.id)
       o.id, o.o1, o.o2, o.o3, f.odds_type_id, f.match_id, f.bookmaker_id
FROM   odds_offer f
JOIN   odds_match m ON m.id = f.match_id
JOIN   odds_odds  o ON o.offer_id = f.id
WHERE  o.o1 <> ?
AND    o.o2 <> ?
AND    m.start_time >= ?
AND   (f.flags = ? OR
       f.flags = ? AND f.last_verified >= ?)
AND    f.match_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ORDER  BY f.id, o."time" DESC NULLS LAST;

Step 3: Rewrite

I would need information as requested in the comment ...

My educated guess is that a LATERAL join would allow to use an index more efficiently.

Also, you have a very long list of matches for match_id. An IN construct is typically inefficient for long lists. Pass an array (or array literal), unnest IDs into a derived table and JOIN to it, that should be substantially faster. Detailed instructions:

SELECT o.id, o.o1, o.o2, o.o3, f.odds_type_id, f.match_id, f.bookmaker_id
FROM   unnest (?) match_id   -- pass array of match_id
JOIN   odds_offer f USING (match_id)
JOIN   odds_match m ON m.id = f.match_id
LEFT   JOIN LATERAL (
    SELECT id, o1, o2, o3
    FROM   odds_odds o
    WHERE  offer_id = f.id
    AND    o1 <> ?
    AND    o2 <> ?
    ORDER  BY o."time" DESC NULLS LAST
    LIMIT  1
    ) o ON true
WHERE (f.flags = ? OR
       f.flags = ? AND f.last_verified >= ?)
-- AND f.match_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)  -- replaced above
AND    m.start_time >= ?
ORDER  BY f.id, o."time" DESC NULLS LAST;

You need at least these two multicolumn indices (maybe another one on odds_offer):

odds_match (id, start_time DESC NULLS LAST)
odds_odds (offer_id, "time" DESC NULLS LAST, o1, o2, o3)

It only makes sense to appended the columns o2, o3 if you get index-only scans out of it. Else don't.

Detailed instructions and explanation in this related answer on SO:

Aside: don't use reserved words like "time" as identifiers.

\$\endgroup\$

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.