Optimizing a query by adding an index Here are my tables:

accounts_company:
 - id
 ...

accounts_companyview:
 - account_id: FK to company
 - viewer_id: FK to company

shipments_address:
 - companyview_id: FK to companyview
 - is_shipper: boolean (not null)

accounts_similarcompanyview
 - original_id: FK to companyview
 - similar_id: FK to companyview 


This is my query (generated by Django)

SELECT COUNT(*) AS "__count"
  FROM "accounts_similarcompanyview"
 INNER JOIN "accounts_companyview"
    ON ("accounts_similarcompanyview"."original_id" = "accounts_companyview"."id")
 INNER JOIN "shipments_address"
    ON ("accounts_companyview"."id" = "shipments_address"."company_view_id")
 INNER JOIN "accounts_companyview" T5
    ON ("accounts_similarcompanyview"."similar_id" = T5."id")
 INNER JOIN "shipments_address" T6
    ON (T5."id" = T6."company_view_id")
 WHERE ("accounts_similarcompanyview"."are_similar" = true AND "shipments_address"."is_shipper" = true AND "accounts_companyview"."viewer_id" = 51729 AND T6."is_shipper" = true AND T5."viewer_id" = 51729);


Output of EXPLAIN ANALYZE: https://explain.depesz.com/s/slJK

I can see that there is an Index Scan taking most of the time, so I tried to add a few indexes but no change whatsoever, I still get a full index scan.

CREATE INDEX "shipments_a_is_ship_d78ee4_idx" ON "shipments_address" ("is_shipper");


CREATE INDEX "shipments_a_is_ship_d78ee4_idx" ON "shipments_address" ("is_shipper", "company_view_id");


What index could I add to make this query faster?

EDIT: forgot to add the size of the tables 

select count(*) from shipments_address;
 2998765

select count(*) from accounts_company;
 168224

select count(*) from accounts_companyview;
 371560

select count(*) from accounts_similarcompanyview;
 83434
The main problem are the bad estimates of the join cardinalities that lead PostgreSQL to use a nested loop join when a hash join would perform better.

There is one simple thing you can do to reduce the impact of the outermost nested loop join:

CREATE INDEX ON shipments_address (company_view_id) WHERE is_shipper;


That should cut the execution time roughly in half.

Other than that, I can think of the ugly method of temporarily disabling nested loop joins:

BEGIN;

SET LOCAL enable_nestloop = off;

/* your query */

COMMIT;