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