Avoid checks for each row, replace query by function?

Question

I have teams:

create table team (
    id      integer     primary key,
    type    text
);

Also, I have players:

create table player
(
    id      integer     primary key,
    age     integer,
    team_id integer     references team(id)
);

The type of a team can either be 'YOUTH' or 'ADULT'. In a youth team, only players starting from age 16 are allowed in official games. In an adult team, only players starting from age 18 are allowed in official games.

Given a team identifier, I want to find all allowed players for the upcoming game. I have the following query:

select    player.*
from      player
join      team
on        player.team_id = team.id
where     team.id = 1 and
          (
              (team.type = 'YOUTH' and player.age >= 16) or
              (team.type = 'ADULT' and player.age >= 18)
          );

This works. However, in this query, for every player I am repetitively checking the type of the team. This value will stay the same during the whole query.

Is there a way to improve this query? Should I replace it with a pgplsql function where I first store the team into a local variable, and then discriminate with following flow?

IF team.type = 'YOUTH' THEN <youth query> ELSE <adult query> END IF

For me, that feels like killing a fly with a bazooka, but I don't see an alternative right now.

I created a SQL fiddle: http://rextester.com/TPFA20157

Why do you think this is inefficient, and why do you think replacing with a pgplsql function would be better? Because in all likelihood it isn't and it wouldn't be — Mark Rotteveel
– Mark Rotteveel, Commented Apr 15, 2017 at 7:03
@MarkRotteveel I believe the check team.type = 'XYZ' is checked again and again for every player. Is that not the case? I would be interested on how the planner approaches this! — Katrine Bers
– Katrine Bers, Commented Apr 15, 2017 at 7:05
Main Problem is in your Query because when you select age is >16 than all player is going in youth as well as no any player shown in adult. you must have to give age between 16 to 18 in youth and grater than 18 in adult — Hacker
– Hacker, Commented Apr 15, 2017 at 7:06
@HackerHate That is not what the query does: it doesn't assign team type, it filters on team type. — Mark Rotteveel
– Mark Rotteveel, Commented Apr 15, 2017 at 7:11
@HackerHate Gosh, did you actually read my question!? The conditions >= 16 and >= 18 are correct. But, that has nothing to do with my question. — Katrine Bers
– Katrine Bers, Commented Apr 15, 2017 at 7:30

Erwin Brandstetter · Accepted Answer · 2024-05-26 02:55:48Z

Auxiliary table

In (strict relational) theory, you would have another table storing attributes of team types like the minimum age.

NEVER store "age", though, which is a function of the underlying constant birthday and the current time. Always store the birthday. Could look like this:

CREATE TABLE team_type (
   team_type text PRIMARY KEY
 , min_age   int NOT NULL  -- in years
);

CREATE TABLE team (
   team_id   integer PRIMARY KEY
 , team_type text NOT NULL REFERENCES team_type
);

CREATE TABLE player (
   player_id serial  PRIMARY KEY
 , birthday  date NOT NULL   -- NEVER store "age", it's outdated the next day
 , team_id   integer REFERENCES team
);

Query:

SELECT p.*, age(now(), p.birthday) AS current_age
FROM   player    p
JOIN   team      t  USING (team_id)
JOIN   team_type tt USING (team_type)
WHERE  t.team_id = 1
AND    p.birthday <= now() - interval '1 year' * tt.min_age;

Using the function age() to display current age, which fits the conventional algorithm to determine age.

But using the more efficient expression p.birthday <= now() - interval '1 year' * tt.min_age in the WHERE clause.

Aside: the current date depends on the current time zone, so the result can vary +/- 12 hours, depending on the time zone setting of the session. Details:

Ignoring time zones for Postgres timestamps

Alternative: function

But yes, you could replace the table team_type with the logic encapsulated in a function like this:

CREATE FUNCTION f_bday_for_team_type(text)
  RETURNS date
  LANGUAGE sql STABLE AS
$func$
SELECT (now() - interval '1 year' * CASE $1 WHEN 'YOUTH' THEN 16
                                            WHEN 'ADULT' THEN 18 END)::date
$func$;

Calculating the maximum birthday to fulfill the minimum age for the given team type. The function is STABLE (not VOLATILE) as one might assume. The manual:

Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.

Query:

SELECT p.*, age(now(), p.birthday) AS current_age
FROM   player p
JOIN   team   t USING (team_id)
     , f_bday_for_team_type(t.team_type) AS max_bday  -- implicit CROSS JOIN LATERAL
WHERE  t.team_id = 2
AND    p.birthday <= max_bday;

Not the holy grail of relational theory, but it works.

fiddle

Collectives™ on Stack Overflow

Avoid checks for each row, replace query by function?

1 Answer 1

Auxiliary table

Alternative: function

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Auxiliary table

Alternative: function

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related