1

I have a query like this:

SELECT "main_table".*
FROM "main_table"
INNER JOIN "other_table" ON "other_table"."deleted_at" IS NULL
AND "other_table"."id" = "main_table"."appointment_id"
WHERE "main_table"."user_id" = 1
  AND (other_table.date BETWEEN '2020-02-26 23:29:42.678693' AND '2020-02-27 01:29:42.678739')

You probably know everything you need to about the schema from the query, but let me know any questions.

What are the optimal indexes to make for this?

main_table is easy (right?)

CREATE INDEX ON main_table (user_id);

For other_table, I'm tempted to think "first postgres will 'do the join', then it will 'filter by date'". Which suggests this index:

CREATE INDEX ON other_table (id, date) where deleted_at IS NULL;

But, without creating the indexes and running EXPLAIN, is it unknowable how postgres will go about doing the query, and it might think that it's better to filter by date first, then 'do the join'?

  • this select will typically match 1–10 rows
  • for a given date range, there are thousands of rows in other_table
  • for a given user_id there are thousands of rows in main_table
  • the results of this query will never have two main_table rows correlating with one other_table row, or vice versa. It's always 1-1.
  • other_table has about 2/3 of its rows with deleted_at not null
0

Given your information about the distribution of the data, the following execution plan will be the best:

  • Use an index scan to get the 1 to 10 rows from other table.

  • Perform a nested loop join with main_table as inner table, which will be fast if the join condition is indexed.

So the ideal index on other_table would be

CREATE INDEX ON other_table (date) WHERE deleted_at IS NULL;

If deleted_at is usually NULL, you can omit the WHERE clause without losing much.

If you want to get an index only scan on other_table (which probably isn't necessary, since you only fetch very few rows), you could instead

CREATE INDEX ON other_table (date) INCLUDING (id) WHERE deleted_at IS NULL;

The ideal index on main_table would be

CREATE INDEX ON main_table (user_id, appointment_id);

The order of the columns doesn't matter in this case, because you will scan for both columns with the = operator.


In the real world, you will try to pick indexes that can be useful for as many queries as possible, because having too many indexes hurts performance and wastes space.

3
  • But I think the 1-10 rows on other_table is predicated on knowing id, without knowing id (which you can't if you are the outer member of the nested loop) there "might also typically be thousands". I think a hash join between two index scans might be the best plan here.
    – jjanes
    Feb 27 '20 at 13:31
  • Quite possible; the question is ambiguous about that. I'll wait for feedback, ideally in the form of a couple of EXPLAIN (ANALYZE, BUFFERS) outputs. Feb 27 '20 at 13:51
  • @LaurenzAlbe sorry I was unclear about the 1-10 rows - i meant, the result of the whole select is typically 1-10 rows. there are thousands of rows matched on each table, with just each's table's criteria. i updated this info in the bulletpoints in the question. let me know if it's still not clear! Feb 27 '20 at 15:23
0

there is a 1-1 relationship with main_table.

Do you mean that main_table.appointment_id and other_table.id are both primary keys of their respective tables? That is rarely a good design, and it would be easier to get efficient queries if the tables were merged to one table with more columns.

But, is it unknowable how postgres will go about doing the query, and it might think that it's better to filter by date first, then 'do the join'?

It is not unknowable, if you use EXPLAIN or EXPLAIN (ANALYZE).

If this is just testing, you could create a bunch of indexes and see which ones actually get used, and whether it seems like a good use of them, and keep the good ones in production.

Your existing indexes look pretty good for a nested loop from main_table to other_table. But it might like to instead pull out the thousands with the user_id, and the thousands with the right date, and then hash join them together. In which case, you would want

CREATE INDEX ON other_table (date,id) where deleted_at IS NULL;

Or it might want to do a nested loop with other_table driving, in which case you would want the above index plus:

CREATE INDEX ON main_table (appointment_id, user_id);

(although the other order of columns should also work)

If deleted_at is usually NULL, then specify "where deleted_at IS NULL;" might not be worthwhile, as it makes the index less flexible for other queries without making it that much faster (although if is enable index-only-scan, it might be quite a bit faster).

2
  • "there is a 1-1 relationship with main_table" - sorry I meant, the results of this query will never have two main_table rows correlating with one other_table row, or vice versa. Feb 27 '20 at 14:59
  • I've updated my question with info about the 1-1, and also about deleted_at (majority of the table is not null) Feb 27 '20 at 15:27

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.