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, Apr 15 '17 at 07: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, Apr 15 '17 at 07: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, Apr 15 '17 at 07:06
@HackerHate That is not what the query does: it doesn't assign team type, it filters on team type. — Mark Rotteveel, Apr 15 '17 at 07:11
She assign team type youth or adult. the query have problem is that if >16 than select on youth. you also can see after where clues she defines only where team.id=1 so that only team=1 player will be display. — Hacker, Apr 15 '17 at 07:16
@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, Apr 15 '17 at 07:30
@KatrineBers If you select >=16 and >=18 which process does are understanding me what i'm saying? — Hacker, Apr 15 '17 at 07:39

score 1 · Accepted Answer · edited May 23 '17 at 11:46

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 timezones altogether in Rails and PostgreSQL

Alternative: function

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

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

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;

That's not the holy grail of relational theory, but it works.

dbfiddle here

Avoid checks for each row, replace query by function?

1 Answers1

Auxiliary table

Alternative: function