Postgresql count+sort performance

Question

I have built a small inventory system using postgresql and psycopg2. Everything works great, except, when I want to create aggregated summaries/reports of the content, I get really bad performance due to count()'ing and sorting. The DB schema is as follows: ``` CREATE TABLE hosts ( id SERIAL PRIMARY KEY, name VARCHAR(255) ); CREATE TABLE items ( id SERIAL PRIMARY KEY, description TEXT ); CREATE TABLE host_item ( id SERIAL PRIMARY KEY, host INTEGER REFERENCES hosts(id) ON DELETE CASCADE ON UPDATE CASCADE, item INTEGER REFERENCES items(id) ON DELETE CASCADE ON UPDATE CASCADE ); ``` There are some other fields as well, but those are not relevant. I want to extract 2 different reports: - List of all hosts with the number of items per, ordered from highest to lowest count - List of all items with the number of hosts per, ordered from highest to lowest count I have used 2 queries for the purpose: Items with host count: ``` SELECT i.id, i.description, COUNT(hi.id) AS count FROM items AS i LE…

Accepted Answer

@Gordon and @willglynn have provided a lot of useful background as to why your query is slow.

A workaround would be to add a counter to the tables `items` and `hosts` and triggers that keep them up to date - for a non-trivial cost to write operations. Or use materialized views like you do. I might opt for that.

For that, you still need to execute these queries on a regular basis and they can be improved. Rewrite your first one to:

```
SELECT id, i.description, hi.ct
FROM   items i
JOIN  (
    SELECT item AS id, count(*) AS ct
    FROM   host_item
    GROUP  BY item
    ORDER  BY ct DESC
    LIMIT  10
    ) hi USING (id);
```

If there is a row in table `items` for most rows in table `host_item`, it is faster to aggregate first and then `JOIN`. Contrary to what @willglynn speculates, this is not optimized automatically in Postgres 9.1.

`count(*)` is faster than `count(col)` on principal - and equivalent while `col` cannot be NULL. (A `LEFT JOIN` might introduce NULL values.)

Simplified `LEFT JOIN` to `JOIN`. It should be safe to assume that there are always at least ten distinct hosts. Doesn't matter much for your original query, but it's a requirement for this one.

Indexes on table `host_item` won't help, and the PK on `items` covers the rest.

Probably still not good enough for your case, but in my tests with Postgres 9.1 this form is more than twice as fast. Should translate to 9.2, but test with `EXPLAIN ANALYZE` to be sure.

Postgresql count+sort performance

Solution

Problem