CODEWITHSUNDEEP
postgresqlindexing
Roman Dvoskin
Roman Dvoskin

Reputation: 384

Is primary key column included at the end of a secondary index

Given these tables

Foo
 id (PK)
 name
 updated

Bar
 foo_id (FK)
 name
 updated

And this query:

SELECT * 
FROM Foo as f
JOIN Bar as b
ON f.id=b.foo_id
WHERE b.name = 'Baz' AND f.name = 'Baz'
ORDER BY f.updated ASC, f.id ASC
LIMIT 10
OFFSET 10

Are these appropriate indexes to add - in MySql InnoDB the primary key column is automatically added to the end of a secondary index. What is the case with Postgres?

CREATE INDEX foo_name_id_idx ON foo(name, id)
CREATE INDEX bar_name_id_idx ON bar(name, id)

Upvotes: 4

Views: 1262

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246788

PostgreSQL does not make the distinction between primary and secondary indexes, and the primary key index is no different from other indexes. So the primary key is not added to other indexes, and there is no point in doing that unless you have a special reason for it.

Depending on which of the conditions are selective, there are three possible strategies:

  1. If the condition on bar.name is selective, use bar as the driving site:

    CREATE INDEX ON bar (name);
-- foo.id is already indexed

  2. If the condition on foo.name is selective:

    CREATE INDEX ON foo (name);
CREATE INDEX ON bar(foo_id);  -- for a nested loop join

  3. If none of the conditions are selective:

    /* here the "id" is actually at the end of the index,
   but that is just because it appears in ORDER BY */
CREATE INDEX ON foo (name, updated, id);  -- for the ORDER BY
CREATE INDEX ON bar (foo_id);  -- for a nested loop join

Upvotes: 5

Related Questions