Revisions to Optimizing a query by adding an index

added [postgresql-performance] to 571 questions - Shog9 (Id=1924)

Link

edited Jan 7, 2020 at 22:25

Community Bot

1

add table sizes

Source Link

edited Oct 22, 2019 at 7:19

Corentin S.

121
4

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

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?

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

added 138 characters in body

Source Link

edited Oct 22, 2019 at 6:47

Corentin S.

121
4

Here are my tables:

companyaccounts_company:
 - id
 ...

companyviewaccounts_companyview:
 - account_id: FK to company
 - viewer_id: FK to company

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

similarcompanyviewaccounts_similarcompanyview
 - original_id: FK to companyview
 - similar_id: FK to companyview

This is my query (generated by Django)

SELECT COUNT(*) AS "__count"
  FROM "similarcompanyview""accounts_similarcompanyview"
 INNER JOIN "companyview""accounts_companyview"
    ON ("similarcompanyview""accounts_similarcompanyview"."original_id" = "companyview""accounts_companyview"."id")
 INNER JOIN "shipments_address"
    ON ("companyview""accounts_companyview"."id" = "address""shipments_address"."company_view_id")
 INNER JOIN "companyview""accounts_companyview" T5
    ON ("similarcompanyview""accounts_similarcompanyview"."similar_id" = T5."id")
 INNER JOIN "shipments_address" T6
    ON (T5."id" = T6."company_view_id")
 WHERE ("similarcompanyview""accounts_similarcompanyview"."are_similar" = true AND "address""shipments_address"."is_shipper" = true AND "companyview""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?

Here are my tables:

company:
 - id
 ...

companyview:
 - account_id: FK to company
 - viewer_id: FK to company

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

similarcompanyview
 - original_id: FK to companyview
 - similar_id: FK to companyview

This is my query (generated by Django)

SELECT COUNT(*) AS "__count"
  FROM "similarcompanyview"
 INNER JOIN "companyview"
    ON ("similarcompanyview"."original_id" = "companyview"."id")
 INNER JOIN "shipments_address"
    ON ("companyview"."id" = "address"."company_view_id")
 INNER JOIN "companyview" T5
    ON ("similarcompanyview"."similar_id" = T5."id")
 INNER JOIN "shipments_address" T6
    ON (T5."id" = T6."company_view_id")
 WHERE ("similarcompanyview"."are_similar" = true AND "address"."is_shipper" = true AND "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?

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?

Source Link

asked Oct 21, 2019 at 16:07

Corentin S.

121
4

Loading

Stack Exchange Network

Return to Question