0

I have a table, Company, and I am trying to query that table with many associations,

Company has one plan_info with foreign_key: :company_id, has many customers with foreign_key: :provider_company_id, has many referrals with foreign_key: :from_company_id.

I do have a company uuid, on which I am querying to the table, but when I start adding joins, it slows down the query a lot, and sometimes it timed out.

SELECT c0.id, c0.name, c0.other_names, c0.agreement_text, c0.personal_guarantee_text, c0.description, c0.established, c0.phone_number, c0."type", c0.website, c0.last_fraud_risk_checked, c0."address", c0.inserted_at, c0.updated_at, c2.id, c2.currency_code, c2.customer_since, c2.onboard_complete, c2.onboard_step, c2.pdf_source, c2.agreement_text_pdf, c2.agreement_signature_pdf, c2.personal_guarantee_text_pdf, c2.personal_guarantee_signature_pdf, c2.pdf_generated, c2.is_manual_customer, c2.provider_company_id, c2.seeker_company_id, c2.deleted_at, c2.inserted_at, c2.updated_at, p1.id, p1.stripe_customer_id, p1.stripe_plan_id, p1.plan_name, p1.billing_date, p1.credit_application_limit, p1.credit_application_limit_type, p1.field_limit, p1.user_limit, p1.vendor_limit, p1.vendors_required, p1.vendors_total, p1.company_id, p1.inserted_at, p1.updated_at, r3.id, r3.to_email, r3.to_name, r3.to_company_name, r3.to_position, r3.to_phone_number, r3.auto_bav, r3.source, r3.enable_personal_guarantee, r3.require_personal_guarantee, r3.require_bank_info, r3.is_admin, r3.pre_verified, r3.credit_limit, r3.role_id, r3."type", r3.custom_message, r3.sent, r3.invitation_sent_at, r3.complete, r3.opt_out, r3.block_send, r3.reward_report_id, r3.opt_out_at, r3.from_id, r3.from_company_id, r3.to_company_id, r3.for_company_id, r3.deleted_at, r3.inserted_at, r3.updated_at 
FROM companies AS c0 
LEFT OUTER JOIN plan_information AS p1 
   ON c0.id = p1.company_id 
INNER JOIN customers AS c2 
   ON c2.provider_company_id = $1 
LEFT OUTER JOIN referrals AS r3 
   ON r3.from_company_id = $2 
WHERE c0.id = $3 
AND r3."type" = 'credit_seeker' 
AND r3.complete = FALSE 
AND r3."source" = 0 

["047a25a7-14ca-4604-9793-fe7bf47e64ca", "047a25a7-14ca-4604-9793-fe7bf47e64ca", "047a25a7-14ca-4604-9793-fe7bf47e64ca"]

This is the above query, which I am doing with the given company uuid, I am on PG version 13.13.

can you suggest me some better joins?

I want to get the single company, with all its customers, and referrals and a plan info.a Company can have more than 1K customers, and the customers table has provider_company_id which is company uuid.

Thank you in advance.

1 Answer 1

0

Some of your join constructs are incorrect.

This one is OK:

LEFT OUTER JOIN plan_information AS p1 
   ON c0.id = p1.company_id 

It finds records in the companies table that match records in the plan_information table, based on matching the company id against the plan_information's company_id. All good.

This one, however, is wrong.

INNER JOIN customers AS c2 
   ON c2.provider_company_id = $1 

You are telling the database to join to a "target" table, but not specifying anything in the "source" table to match against.
This will be interpreted as first trying to match every row in the "source" table to every row in the "target" table (a "Cartesian" or "Cross-Product" Join), and then filtering the [huge and incorrect] result using that constant.
That's not what you want and definitely explains the poor performance and timeouts.

Change your JOINS to match "source" field(s) to "target" field(s).
When you need to filter further, add those conditions as additional WHERE clauses, something like this:

INNER JOIN customers AS c2 
   ON c0.id = c2.provider_company_id 
. . . 
WHERE c0.id = $1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.