I am surprised by the slow response time of a type of query and am hoping to get some advice.

Let's imagine a table person with the columns id (integer and primary key) and age (integer). I would like to compute the average age of a list of persons. I tried so far two queries:

Query 1:

select avg(p.age)
from persons p
where p.id = any(...);

Query 2:

select avg(p.age)
  ) as i (id)
  join person p on i.id = p.id;

For a large number of IDs, e.g., 2 million, the queries are both surprisingly slow (in my setup it takes >10s) with query 1 being slower than query 2. If the IDs were already in a database table and we were doing a normal join of two existing tables, it is fast.

I don't understand why it is so slow and how/whether it can be improved. As id is the primary key, there is obviously an index on it.

  • 2
    Please edit your question and add both execution plans generated using explain (analyze, buffers, format text) (not just a "simple" explain) as formatted text and make sure you preserve the indention of the plan. Paste the text, then put ``` on the line before the plan and on a line after the plan. Please also include complete create index statements for all indexes as well. Jul 16 at 22:28
  • Also, please specify the database version..
    – jjanes
    Jul 17 at 2:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.