PostgreSQL – Optimize Age Range in WHERE Clause

postgresql

I have 2 columns in the database for events.

agelow (int)
agehigh (int)

How it's structured:

agehigh is being used for "and under" which is represented by 0 in the column
the high age is placed in the agelow column
The "and over" is represented by 99 and is not a problem since it has the agehigh and the agelow in the right columns.
all ages are represented with 99 in both columns.

I am trying to write a WHERE that can pull all event ages if no ages are passed in but also need to be able to pull events just for one age passed in or for an age range passed in. The age range is making this a big headache.

Example

If I pass in agelow 5 and agehigh 15, I would need to grab all events that are

available for anyone from 5 up to 15 which includes the "5 and under", "5 and up", "15 and under" and all ages.

Here is the WHERE part:

WHERE
((events.age_low >= agelow AND events.age_high <= agehigh) OR
 (events.age_low <= agelow AND events.age_high >= agehigh) OR
 (events.age_low <= agelow /* LOW (99 for all) */ AND events.age_high >= agelow /* LOW (99 for all) */) OR
 (events.age_low <= agelow /* LOW (99 for all) */ AND events.age_high = 99 /* DEFAULT 99 */) OR
 (events.age_low >= agelow /* LOW (99 for all) */ AND events.age_high = 0 /* DEFAULT 0 */) OR 
 (events.age_high >= agehigh /* HIGH (0 for all) */ AND events.age_low <= agehigh /* HIGH (0 for all) */) OR 
 (events.age_low >= agehigh /* HIGH (0 for all) */ AND events.age_high = 0 /* DEFAULT 0 */) OR 
 (events.age_low <= agehigh /* HIGH (0 for all) */ AND events.age_high = 99 /* DEFAULT 99 */) OR  
 (events.age_low = 99 AND events.age_high = 99));

Here is a small selection of the age columns:

CREATE TABLE events(age_low,age_high)
AS VALUES
  (  99 ,  99 ),
  (   3 ,  12 ),
  (   1 ,   6 ),
  (   5 ,   0 ),
  (  99 ,  99 ),
  (  99 ,  99 ),
  (  99 ,  99 ),
  (   5 ,  11 ),
  (   5 ,   0 ),   -- this is 5 and below
  (  16 ,  99 ),   -- this is 16 and up
  (   1 ,   4 ),
  (  13 ,  18 ),
  (  99 ,  99 ),   -- this is all ages
  (   5 ,   0 ),
  (  99 ,  99 );

Best Answer

One option is to wrap greatest and least to assist this. We can use this to iron out the oddities

5,0 being five and under. That should really be 0,5 if there was any level of sanity.
That 99,99 is all ages. Here if the lower bound is 99 we set that to -1 just be sure it's all ages.

Code,

CREATE FUNCTION normalize_query_range(start int, finish int)
RETURNS int4range
AS
$$
  SELECT int4range(
    CASE WHEN least(start,finish) = 99 THEN -1 ELSE least(start,finish) END
    , greatest(start,finish)
  );
$$ LANGUAGE sql
IMMUTABLE;

Now, we can easily do this using the range operators for @> contains, and && overlaps.

SELECT age_low,age_high
FROM events
WHERE normalize_query_range(age_low,age_high) && int4range(5,15);

And, even better, creating an index will speed this up.

CREATE INDEX ON events
  USING gist(normalize_query_range(age_low, age_high));

Related Solutions

PostgreSQL – Using CASE in Window Functions to Partition by Age Group

You could use a LATERAL join for this. And no need for joining the users table twice:

FROM 
    competition_users AS cu
  LEFT JOIN users AS u
    ON cu.user_id = u.user_id
  LEFT JOIN LATERAL
    ( SELECT CASE WHEN u.dob > '2000-01-01' AND u.dob < now() 
                 THEN '0-15' 
                 ELSE '15+' 
             END AS ageGroup
    ) AS age
    ON TRUE
  LEFT JOIN current_competition_sessions AS ccs 
    ON cu.competition_user_id = ccs.competition_user_id 
WHERE cu.left_competition = FALSE 
  AND cu.competition_id = :compId

and then the WINDOW would be left (almost) unchanged:

WINDOW
    ageGroupTimes AS (PARTITION BY age.ageGroup ORDER BY cus.time_in_seconds ASC)

Alternatively, a simple CTE or derived table with a normal LEFT JOIN could be used, without the need for the (9.3+ available only) LATERAL. With the following you'll only need to slightly modify your upper-level SELECT and WINDOW (removing table aliases mainly):

FROM
  ( SELECT 
        cu.*, 
        cus.time_in_seconds,
        u.gender,
        CASE WHEN u.dob > '2000-01-01' AND u.dob < now() 
            THEN '0-15' 
            ELSE '15+' 
        END AS ageGroup
    FROM 
        competition_users AS cu
      LEFT JOIN users AS u
        ON cu.user_id = u.user_id
      LEFT JOIN current_competition_sessions AS ccs 
        ON cu.competition_user_id = ccs.competition_user_id 
    WHERE cu.left_competition = FALSE 
      AND cu.competition_id = :compId
  ) AS d

PostgreSQL 9.4: Index Only Used For Lower INET Ranges

Your statistics are way off. The planner thinks it's going to retrieve 948529, 1016801, 5685950 and 5685950 rows, respectively, when infact it retrieves 55, 1, 3 and 3.

In the second example, that is why it's preferring a seqscan (I am guessing it expects to have about 5e+6 values in the table and 1e+6, being 20% of the entire table, is not worth an index scan).

What you need is increasing the statistics target on the two columns (ALTER TABLE ip_range_domains ALTER COLUMN high SET STATISTICS 1000) and run ANALYZE ip_range_domains. This will give the planner a much better idea of the data contained in this table (specifically, the two columns you are filtering on). Try playing with different values (10000 is the maximum).

The decision the planner needs to make is that between a sequential scan of a table, where each individual read is quite cheap (not least because the OS can prefetch that for you, but also because of the mechanics of the storage medium), and a random read (which is believed to be much more expensive because of... well, random, unpredictable reading pattern).

If the planner's conclusion is it will have to read 30% of the index to come up with 20% of the table, which it will all have to retrieve using random fetches, that is simply just too costly, so it will prefer a full sequential scan.

Example

Best Answer

Related Solutions

PostgreSQL – Using CASE in Window Functions to Partition by Age Group

PostgreSQL 9.4: Index Only Used For Lower INET Ranges

Related Question