2

I have a long-ish query running on a very small database, but the results are nowhere near satisfactory. First the query:

SELECT DISTINCT
  "GAMES_SEASON"."ID",
  "GAMES_SEASON"."WIN_POINTS",
  "GAMES_SEASON"."WIN_EXTRA_TIME_POINTS",
  "GAMES_SEASON"."TIE_POINTS",
  "GAMES_SEASON"."TIE_EXTRA_TIME_POINTS",
  "GAMES_SEASON"."DEFEAT_EXTRA_TIME_POINTS",
  "GAMES_SEASON"."DEFEAT_POINTS",
  "GAMES_SEASON"."LEAGUE_ID",
  "GAMES_SEASON"."START_DATE",
  "GAMES_SEASON"."END_DATE",
  COUNT(DISTINCT CASE
                 WHEN (("GAMES_GOAL"."PASSER_1_ID" = 105
                        OR "GAMES_GOAL"."PASSER_2_ID" = 105)
                       AND T13."SEASON_ID" = ("GAMES_SEASON"."ID"))
                   THEN "GAMES_GOAL"."ID"
                 ELSE NULL
                 END)                         AS "PLAYER_PASSES",
  COUNT(DISTINCT CASE
                 WHEN ("GAMES_GOAL"."SCORER_ID" = 105
                       AND T13."SEASON_ID" = ("GAMES_SEASON"."ID"))
                   THEN "GAMES_GOAL"."ID"
                 ELSE NULL
                 END)                         AS "PLAYER_GOALS",
  (COUNT(DISTINCT CASE
                  WHEN ("GAMES_GOAL"."SCORER_ID" = 105
                        AND T13."SEASON_ID" = ("GAMES_SEASON"."ID"))
                    THEN "GAMES_GOAL"."ID"
                  ELSE NULL
                  END) + COUNT(DISTINCT CASE
                                        WHEN (("GAMES_GOAL"."PASSER_1_ID" = 105
                                               OR "GAMES_GOAL"."PASSER_2_ID" = 105)
                                              AND T13."SEASON_ID" = ("GAMES_SEASON"."ID"))
                                          THEN "GAMES_GOAL"."ID"
                                        ELSE NULL
                                        END)) AS "PLAYER_POINTS",
  "PLAYERS_LEAGUE"."FULL_NAME"
FROM "GAMES_SEASON"
  LEFT OUTER JOIN "GAMES_SEASON_TEAMS" ON ("GAMES_SEASON"."ID" = "GAMES_SEASON_TEAMS"."SEASON_ID")
  LEFT OUTER JOIN "PLAYERS_TEAM" ON ("GAMES_SEASON_TEAMS"."TEAM_ID" = "PLAYERS_TEAM"."ID")
  LEFT OUTER JOIN "GAMES_GAME" ON ("PLAYERS_TEAM"."ID" = "GAMES_GAME"."CHALLENGER_ID")
  LEFT OUTER JOIN "GAMES_GAMEPLAYER" ON ("GAMES_GAME"."ID" = "GAMES_GAMEPLAYER"."GAME_ID")
  LEFT OUTER JOIN "GAMES_GAME" T7 ON ("PLAYERS_TEAM"."ID" = T7."GUEST_PLAYER_ID")
  LEFT OUTER JOIN "GAMES_GAMEPLAYER" T8 ON (T7."ID" = T8."GAME_ID")
  LEFT OUTER JOIN "GAMES_GOAL" ON ("GAMES_GAME"."ID" = "GAMES_GOAL"."GAME_ID")
  LEFT OUTER JOIN "GAMES_GAME" T13 ON ("GAMES_GOAL"."GAME_ID" = T13."ID")
  INNER JOIN "PLAYERS_LEAGUE" ON ("GAMES_SEASON"."LEAGUE_ID" = "PLAYERS_LEAGUE"."ID")
WHERE ("GAMES_GAMEPLAYER"."PLAYER_ID" = 105
       OR T8."PLAYER_ID" = 105)
GROUP BY "GAMES_SEASON"."ID",
  "PLAYERS_LEAGUE"."FULL_NAME"
ORDER BY "GAMES_SEASON"."START_DATE" DESC,
  "PLAYERS_LEAGUE"."FULL_NAME" ASC;

As you can see, there's lots of stuff going on. Since it was slow, I threw it in the execution plan analyzer and saw right away that the sorting spills to disk and is the main culprit of the slowness.

Unique  (cost=8789.85..8794.39 rows=121 width=358) (actual time=4992.227..4992.240 rows=4 loops=1)
  ->  Sort  (cost=8789.85..8790.15 rows=121 width=358) (actual time=4992.223..4992.225 rows=4 loops=1)
        Sort Key: games_season.start_date, players_league.full_name, games_season.id, games_season.win_points, games_season.win_extra_time_points, games_season.tie_points, games_season.tie_extra_time_points, games_season.defeat_extra_time_points, games_season.defeat_points, games_season.league_id, games_season.end_date, (count(DISTINCT CASE WHEN ((t11.season_id = games_season.id) AND (games_goal.scorer_id = 105)) THEN games_goal.id ELSE NULL::integer END)), (count(DISTINCT CASE WHEN (((games_goal.passer_1_id = 105) OR (games_goal.passer_2_id = 105)) AND (t11.season_id = games_season.id)) THEN games_goal.id ELSE NULL::integer END)), ((count(DISTINCT CASE WHEN ((t11.season_id = games_season.id) AND (games_goal.scorer_id = 105)) THEN games_goal.id ELSE NULL::integer END) + count(DISTINCT CASE WHEN (((games_goal.passer_1_id = 105) OR (games_goal.passer_2_id = 105)) AND (t11.season_id = games_season.id)) THEN games_goal.id ELSE NULL::integer END)))
        Sort Method: quicksort  Memory: 25kB
        ->  GroupAggregate  (cost=8201.14..8785.66 rows=121 width=358) (actual time=3713.585..4992.128 rows=4 loops=1)
              Group Key: games_season.id, players_league.full_name
              ->  Sort  (cost=8201.14..8235.43 rows=13718 width=358) (actual time=3699.079..4604.762 rows=225760 loops=1)
                    Sort Key: games_season.id, players_league.full_name
                    Sort Method: external merge  Disk: 27312kB
                    ->  Hash Join  (cost=249.19..5004.45 rows=13718 width=358) (actual time=90.664..1852.740 rows=225760 loops=1)
                          Hash Cond: (games_season.league_id = players_league.id)
                          ->  Hash Left Join  (cost=247.94..4814.58 rows=13718 width=84) (actual time=90.596..1390.586 rows=225760 loops=1)
                                Hash Cond: (games_goal.game_id = t11.id)
                                ->  Hash Left Join  (cost=238.31..4616.33 rows=13718 width=84) (actual time=90.002..936.210 rows=225760 loops=1)
                                      Hash Cond: (games_game.id = games_goal.game_id)
                                      ->  Hash Right Join  (cost=166.63..4349.62 rows=1493 width=68) (actual time=84.465..629.378 rows=24339 loops=1)
                                            Hash Cond: (games_game.challenger_id = players_team.id)
                                            Filter: ((games_gameplayer.player_id = 105) OR (t8.player_id = 105))
                                            Rows Removed by Filter: 1716156
                                            ->  Hash Right Join  (cost=9.62..225.49 rows=7152 width=12) (actual time=0.609..22.613 rows=7155 loops=1)
                                                  Hash Cond: (games_gameplayer.game_id = games_game.id)
                                                  ->  Seq Scan on games_gameplayer  (cost=0.00..117.52 rows=7152 width=8) (actual time=0.005..6.813 rows=7152 loops=1)
                                                  ->  Hash  (cost=6.50..6.50 rows=250 width=8) (actual time=0.571..0.571 rows=250 loops=1)
                                                        Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                                        ->  Seq Scan on games_game  (cost=0.00..6.50 rows=250 width=8) (actual time=0.007..0.284 rows=250 loops=1)
                                            ->  Hash  (cost=136.62..136.62 rows=1631 width=72) (actual time=40.753..40.753 rows=8311 loops=1)
                                                  Buckets: 1024  Batches: 1  Memory Usage: 877kB
                                                  ->  Nested Loop Left Join  (cost=5.32..136.62 rows=1631 width=72) (actual time=0.595..28.965 rows=8311 loops=1)
                                                        ->  Hash Right Join  (cost=5.04..13.04 rows=57 width=72) (actual time=0.568..1.623 rows=284 loops=1)
                                                              Hash Cond: (t7.guest_player_id = players_team.id)
                                                              ->  Seq Scan on games_game t7  (cost=0.00..6.50 rows=250 width=8) (actual time=0.006..0.325 rows=250 loops=1)
                                                              ->  Hash  (cost=4.90..4.90 rows=11 width=68) (actual time=0.546..0.546 rows=46 loops=1)
                                                                    Buckets: 1024  Batches: 1  Memory Usage: 5kB
                                                                    ->  Hash Right Join  (cost=3.13..4.90 rows=11 width=68) (actual time=0.309..0.482 rows=46 loops=1)
                                                                          Hash Cond: (players_team.id = games_season_teams.team_id)
                                                                          ->  Seq Scan on players_team  (cost=0.00..1.48 rows=48 width=4) (actual time=0.004..0.059 rows=48 loops=1)
                                                                          ->  Hash  (cost=2.99..2.99 rows=11 width=68) (actual time=0.286..0.286 rows=46 loops=1)
                                                                                Buckets: 1024  Batches: 1  Memory Usage: 5kB
                                                                                ->  Hash Right Join  (cost=1.25..2.99 rows=11 width=68) (actual time=0.062..0.225 rows=46 loops=1)
                                                                                      Hash Cond: (games_season_teams.season_id = games_season.id)
                                                                                      ->  Seq Scan on games_season_teams  (cost=0.00..1.46 rows=46 width=8) (actual time=0.003..0.052 rows=46 loops=1)
                                                                                      ->  Hash  (cost=1.11..1.11 rows=11 width=64) (actual time=0.039..0.039 rows=11 loops=1)
                                                                                            Buckets: 1024  Batches: 1  Memory Usage: 2kB
                                                                                            ->  Seq Scan on games_season  (cost=0.00..1.11 rows=11 width=64) (actual time=0.004..0.018 rows=11 loops=1)
                                                        ->  Index Scan using games_gameplayer_6072f8b3 on games_gameplayer t8  (cost=0.28..1.88 rows=29 width=8) (actual time=0.005..0.039 rows=29 loops=284)
                                                              Index Cond: (t7.id = game_id)
                                      ->  Hash  (cost=42.97..42.97 rows=2297 width=20) (actual time=5.508..5.508 rows=2297 loops=1)
                                            Buckets: 1024  Batches: 1  Memory Usage: 106kB
                                            ->  Seq Scan on games_goal  (cost=0.00..42.97 rows=2297 width=20) (actual time=0.007..2.681 rows=2297 loops=1)
                                ->  Hash  (cost=6.50..6.50 rows=250 width=8) (actual time=0.573..0.573 rows=250 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                      ->  Seq Scan on games_game t11  (cost=0.00..6.50 rows=250 width=8) (actual time=0.007..0.286 rows=250 loops=1)
                          ->  Hash  (cost=1.11..1.11 rows=11 width=278) (actual time=0.041..0.041 rows=11 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                ->  Seq Scan on players_league  (cost=0.00..1.11 rows=11 width=278) (actual time=0.007..0.021 rows=11 loops=1)
Planning time: 5.488 ms
Execution time: 5001.204 ms

Alright, simple test - I removed the ordering at all and the query got three times as fast. Here's the catch: as I mentioned the database is really tiny and there are about 15 rows of GAMES_SEASON in there. The given query returns 4 rows. So why on earth takes ordering 4 rows twice as long as doing everything else? Postgres seems to be doing something unintuitive there and I can't understand what's the reason.

Disclaimer: The original query is composed by Django ORM which isn't probably the smartest PSQL planner, so if something is written in a weird way overall, then I do not take full responsibility. Since I have very little experience writing raw SQL, I can't really judge it objectively.

  • 2
    The link to the execution plan does not work (I see an empty page). Please edit your question and add the plan as formatted text please, no screen shots – a_horse_with_no_name Aug 31 '17 at 13:11
  • Also, the DISTINCT (in SELECT DISTINCT) seems redundant. Why is it there and do you get different plan / faster execution if you remove it? – ypercubeᵀᴹ Aug 31 '17 at 13:12
  • @a_horse_with_no_name Fixed the links and added the plain text plan. Thanks! – wanaryytel Aug 31 '17 at 13:22
  • @ypercubeᵀᴹ Tried without the DISTINCT and the execution plan and execution time are basically the same. Logically I think the DISTINCT belongs there rightfully because these parts might yield duplicates (?): LEFT OUTER JOIN "GAMES_GAME" ON ("PLAYERS_TEAM"."ID" = "GAMES_GAME"."CHALLENGER_ID") & ` LEFT OUTER JOIN "GAMES_GAME" T7 ON ("PLAYERS_TEAM"."ID" = T7."GUEST_PLAYER_ID")` (filtering by teams, theoretically a player could be a part of both sides at some point). – wanaryytel Aug 31 '17 at 13:37
  • 2
    Anyway, t's not the DISTINCT or the sort (of 4 rows) that causes the issue. It's the cross joins that produce a lot of rows (225k rows), so before the aggregate phase is done, these 225k rows (27MB) are saved into disk. – ypercubeᵀᴹ Aug 31 '17 at 13:44
1

I might be wrong, but this:

->  Sort  (cost=8201.14..8235.43 rows=13718 width=358) 
          (actual time=3699.079..4604.762 rows=225760 loops=1)

tells me the optimizer expects to sort 13718 rows and allocates sort memory based on that estimate, but in fact has 225760 rows, almost 20 times more, to deal with, so it obviously spills.

Assuming your table statistics are current, you may want to try increasing default_statistics_target for better histogram granularity.

Some reading on the topic.

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.