I'm struggling with optimizing the performance of a single query, which is sometimes taking up to 180s for certain tenants. When I re-run this later in pgadmin on the production database server (using EXPLAIN ANALYZE), these are executing in <0.2s. If not on the first try, then at least on the second or third rerun.

My first conclusion is that a big factor is whether the table (or at least the dataset I'm accessing) is currently held in cache. But I'm wondering if there's other options besides increasing the memory on the database server to improve the performance, i.e. whether there's something structurally bad with the query or the indices.

The query is a fairly simple multi-table join, here's a slightly simplified (projection only), anonymized version of it:

SELECT results.id, result_details.title, seven.info_a, oscar.info_b
     FROM results
LEFT JOIN result_details ON results.details_domain_id = result_details.domain_id
                        AND results.tenant_id = result_details.tenant_id
LEFT JOIN seven          ON result_details.a = seven.domain_id
                        AND result_details.tenant_id = seven.tenant_id
LEFT JOIN oscar          ON result_details.b = oscar.domain_id
                        AND result_details.tenant_id = oscar.tenant_id
WHERE results.tenant_id=123

So the basic structure is that there's four tables involved: I want all results (golf_echo in the explain) for a certain tenant. Each result maps to a single entry in result_details (golf_india in the explain). This then gives me the ID of two other tables seven and oscar, to which there's also a single entry (or rarely none) each.

This is the output of the EXPLAIN ANALYZE command: http://explain.depesz.com/s/u1bT
From what I understand, the main portion of the time (90%) is spent on the Bitmap Heap Scan of oscar. My primary question is why that might be, considering the almost identical join for seven being so much faster, using an Index Only Scan.

It's worth noting that seven and oscar have the same basic schema (artificial primary key, a tenant foreign key, a tenant-specific domain ID, followed by various data columns only used in the projection):

id | tenant_id | domain_id | data_1 | data_2 | data_3 | ...

Both of them have almost the same three indices:

1. btree (tenant_id)
2. breee (tenant_id, domain_id)
3. btree (tenant_id, domain_id, other_data_1, other_data_2)

These are obviously not particularly good indices (most are ORM defaults), but I'd like to understand the problem instead of making random guesses how to change these.

They also have almost same table size: seven has ~1GiB, oscar has ~1.2GiB, but seven has 6.5M rows and oscar has 3.5M rows. So seven (the 'slower one') has slightly less data, but twice as many rows.

In the vast majority of cases, a row in the first table matches to exactly one row in the second, third and fourth table. In very rare (but supported) cases, the second, third or fourth table might not have a matching record. That's why these are LEFT and not INNER JOINs.

The database server is running Postgres 9.4.6, on Heroku (so I don't have direct access to most of the tuning parameters).

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.