Know your limits

Whenever you write a query against the Illuminate database make sure every query has a LIMIT.

According to the Postgres documentation

If a limit count is given, no more than that many rows will be returned (but possibly less, if the query itself yields less rows).

This means that Postgres will not return more rows than specified by your LIMIT clause. Even if you are expecting 100,000 rows to be returned, include a LIMIT.

If you write a bad query having a LIMIT will save you time waiting for the results. For example, take the following query:

SELECT
  students.student_id,
  students.last_name,
  students.first_name
FROM students

This query returns me 2200 students. So far so good.

Now lets say I want to update this query to get me each students grade level. I might update the query to look like this,

SELECT
  students.student_id,
  students.last_name,
  students.first_name,
  ss_cube.grade_level_id
FROM students
JOIN matviews.ss_cube USING (student_id)

This is taking a while to run … and oh, finally it does and it gives me 40,000 records!! I obviously messed something up (this is actually returning me every enrollment/roster record for every student instead of just one per student), so how can I save myself from a mistake like this in the future?

Well in this case I know my district has 2200 students, and I know my query should only be returning me 1 row for each student, so if I add a LIMIT 2200 this will ensure I get all the students expected but also save me in the event of a bad query.

To take this a step further I might even make this LIMIT 2500, just in case we add a lot more students to our district next year.

Here is the updated query (still getting the bad data, but with a LIMIT):

-- Don't forget our application name and timeout
SET application_name TO 'zrankin via pgAdmin, experimenting with queries';
SET statement_timeout TO '1000';

SELECT
  students.student_id,
  students.last_name,
  students.first_name,
  ss_cube.grade_level_id
FROM students
JOIN matviews.ss_cube USING (student_id)
LIMIT 2500