-3

For testing purposes, I'm interested in speeding up the following query:

SELECT a.*, b.id 
FROM "table" a JOIN "table" b ON
a.id = b.id

where "table" has an index on id. When passed this, postgres attempts to perform an index scan on the instance b, but needs to sort a. Is it possible to use the index on a.id in this query as well (or is this query going to be fundamentally slow, due to reasons similar to the discussion here).

Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90
wfawwer
  • 103
  • 1

1 Answers1

1

PostgreSQL can perform an index scan in both cases, but it prefers a sequential scan and a sort in the first case, because it thinks that will be faster. In the second case, an index-only scan is possible and faster.

You could of course add all table columns to the index, but that is usually unreasonable and may even exceed the size limit for an index entry.

If you have reason to believe that PostgreSQL is doing the wrong thing, you can try if the query is faster with enable_seqscan set to off. If yes, perhaps you have to adjust random_page_cost to match your hardware.

Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90