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
golf_echo in the explain) for a certain tenant. Each
result maps to a single entry in
golf_india in the explain). This then gives me the ID of two other tables
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).