CODEWITHSUNDEEP
sqlpostgresqlindexinggroup-by
DaltonicD
DaltonicD

Reputation: 75

Best indexes for GROUP BY & equality query on joined tables

I have this recurrent SQL query in postgres:

SELECT b.name, COUNT(*)
FROM a JOIN b ON a.x = b.x
WHERE a.value = some_value
GROUP BY b.name;

(Count occurences of a.value for each b.name)

What are the best indexes to aid this query's efficiency? I'm assuming that for a.value a hash index on it helps. But what about the group by? Is there some composite index solution? Or another kind of solution?

Upvotes: 0

Views: 604

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

Never use NATURAL JOIN. It is not clear what your query is actually doing. It is easy to make mistakes. And most importantly, the so-called "natural" join does not use the natural relationships explicitly declared using foreign key relationships.

Let me assume your query looks like this:

SELECT b.name, COUNT(*)
FROM a JOIN
     b 
     ON b.x = a.x
WHERE a.value = some_value
GROUP BY b.name;

In most databases, you would want the following indexes:

  • a(value, x)
  • b(x, name)

Upvotes: 1

Related Questions