logo标签列表

Postgres查询速度慢，使用LIMIT限制结果数量

postgresqldatabase-performancesql-limit
7
我遇到了类似于PostgreSQL查询limit 1非常缓慢和带有ORDER和LIMIT子句的极其缓慢的PostgreSQL查询的问题，尽管在我的情况下，无论LIMIT是1、5还是500都没有关系。
基本上，当我运行一个没有限制的Django ORM生成的查询时，查询需要半秒钟，但是加上限制（用于分页）后，需要7秒钟。
需要7秒钟的查询为：
SELECT "buildout_itemdescription"."product_code_id",
         MIN("buildout_lineitem"."unit_price") AS "min_price"
FROM "buildout_lineitem"
INNER JOIN "buildout_itemdescription"
    ON ("buildout_lineitem"."item_description_id" = "buildout_itemdescription"."id")
WHERE (("buildout_lineitem"."report_file_id" IN (154, 172, 155, 181, 174, 156, 157, 182, 175, 176, 183, 158, 177, 159, 179, 178, 164, 180, 367, 165, 173, 166, 167, 168, 368, 422, 370, 169, 1335, 1323, 161, 160, 162, 170, 171, 676, 151, 163, 980, 152, 369, 153, 963, 1718, 881, 617, 1759, 1780, 636, 1199, 1243, 947, 1163, 1422, 1009, 1407, 1035, 1241, 1077, 1271, 1111, 1130, 1489, 1507, 1555, 1600, 1619, 1663)
        AND "buildout_lineitem"."unit_price" > 0
        AND NOT ("buildout_itemdescription"."product_code_id" IS NULL)
        AND "buildout_lineitem"."date" >= '2014-04-20'::date
        AND "buildout_lineitem"."date" <= '2019-03-25'::date))
GROUP BY  "buildout_itemdescription"."product_code_id"
ORDER BY  "buildout_itemdescription"."product_code_id" LIMIT 5

另一个查询与LIMIT 5相同，但没有限制。

Postgres对这两个查询使用非常不同的计划（HASH JOIN vs NESTED LOOP）。

[
  {
    "Plan": {
      "Node Type": "Limit",
      "Parallel Aware": false,
      "Startup Cost": 0.86,
      "Total Cost": 1362.36,
      "Plan Rows": 5,
      "Plan Width": 36,
      "Actual Startup Time": 7035.543,
      "Actual Total Time": 7063.808,
      "Actual Rows": 5,
      "Actual Loops": 1,
      "Output": ["buildout_itemdescription.product_code_id", "(min(buildout_lineitem.unit_price))"],
      "Shared Hit Blocks": 1995053,
      "Shared Read Blocks": 1158777,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0,
      "Plans": [
        {
          "Node Type": "Aggregate",
          "Strategy": "Sorted",
          "Partial Mode": "Simple",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Startup Cost": 0.86,
          "Total Cost": 35365525.74,
          "Plan Rows": 129877,
          "Plan Width": 36,
          "Actual Startup Time": 7035.541,
          "Actual Total Time": 7063.804,
          "Actual Rows": 5,
          "Actual Loops": 1,
          "Output": ["buildout_itemdescription.product_code_id", "min(buildout_lineitem.unit_price)"],
          "Group Key": ["buildout_itemdescription.product_code_id"],
          "Shared Hit Blocks": 1995053,
          "Shared Read Blocks": 1158777,
          "Shared Dirtied Blocks": 0,
          "Shared Written Blocks": 0,
          "Local Hit Blocks": 0,
          "Local Read Blocks": 0,
          "Local Dirtied Blocks": 0,
          "Local Written Blocks": 0,
          "Temp Read Blocks": 0,
          "Temp Written Blocks": 0,
          "Plans": [
            {
              "Node Type": "Nested Loop",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Join Type": "Inner",
              "Startup Cost": 0.86,
              "Total Cost": 35362951.28,
              "Plan Rows": 255139,
              "Plan Width": 10,
              "Actual Startup Time": 7035.220,
              "Actual Total Time": 7062.420,
              "Actual Rows": 10660,
              "Actual Loops": 1,
              "Output": ["buildout_itemdescription.product_code_id", "buildout_lineitem.unit_price"],
              "Inner Unique": false,
              "Shared Hit Blocks": 1995053,
              "Shared Read Blocks": 1158777,
              "Shared Dirtied Blocks": 0,
              "Shared Written Blocks": 0,
              "Local Hit Blocks": 0,
              "Local Read Blocks": 0,
              "Local Dirtied Blocks": 0,
              "Local Written Blocks": 0,
              "Temp Read Blocks": 0,
              "Temp Written Blocks": 0,
              "Plans": [
                {
                  "Node Type": "Index Scan",
                  "Parent Relationship": "Outer",
                  "Parallel Aware": false,
                  "Scan Direction": "Forward",
                  "Index Name": "buildout_itemdescription_product_code_id_084c51de",
                  "Relation Name": "buildout_itemdescription",
                  "Schema": "public",
                  "Alias": "buildout_itemdescription",
                  "Startup Cost": 0.43,
                  "Total Cost": 143970.67,
                  "Plan Rows": 1003733,
                  "Plan Width": 8,
                  "Actual Startup Time": 0.074,
                  "Actual Total Time": 627.419,
                  "Actual Rows": 351127,
                  "Actual Loops": 1,
                  "Output": ["buildout_itemdescription.id", "buildout_itemdescription.deleted", "buildout_itemdescription.created_on", "buildout_itemdescription.modified_on", "buildout_itemdescription.description", "buildout_itemdescription.category_1", "buildout_itemdescription.category_2", "buildout_itemdescription.category_3", "buildout_itemdescription.manufacturer_sku", "buildout_itemdescription.manufacturer_name", "buildout_itemdescription.distributor_sku", "buildout_itemdescription.supplier_id", "buildout_itemdescription.unit_of_measure", "buildout_itemdescription.quantity_in_unit_of_measure", "buildout_itemdescription.created_by_id", "buildout_itemdescription.modified_by_id", "buildout_itemdescription.product_code_id", "buildout_itemdescription.region_id"],
                  "Index Cond": "(buildout_itemdescription.product_code_id IS NOT NULL)",
                  "Rows Removed by Index Recheck": 0,
                  "Shared Hit Blocks": 195375,
                  "Shared Read Blocks": 144994,
                  "Shared Dirtied Blocks": 0,
                  "Shared Written Blocks": 0,
                  "Local Hit Blocks": 0,
                  "Local Read Blocks": 0,
                  "Local Dirtied Blocks": 0,
                  "Local Written Blocks": 0,
                  "Temp Read Blocks": 0,
                  "Temp Written Blocks": 0
                },
                {
                  "Node Type": "Index Scan",
                  "Parent Relationship": "Inner",
                  "Parallel Aware": false,
                  "Scan Direction": "Forward",
                  "Index Name": "buildout_lineitem_item_description_id_88254e09",
                  "Relation Name": "buildout_lineitem",
                  "Schema": "public",
                  "Alias": "buildout_lineitem",
                  "Startup Cost": 0.43,
                  "Total Cost": 35.00,
                  "Plan Rows": 9,
                  "Plan Width": 10,
                  "Actual Startup Time": 0.018,
                  "Actual Total Time": 0.018,
                  "Actual Rows": 0,
                  "Actual Loops": 351127,
                  "Output": ["buildout_lineitem.id", "buildout_lineitem.deleted", "buildout_lineitem.created_on", "buildout_lineitem.modified_on", "buildout_lineitem.date", "buildout_lineitem.month", "buildout_lineitem.quantity", "buildout_lineitem.unit_price", "buildout_lineitem.extended_sell", "buildout_lineitem.list_price", "buildout_lineitem.list_price_ext", "buildout_lineitem.agreed_price", "buildout_lineitem.agreed_price_ext", "buildout_lineitem.baseline_price", "buildout_lineitem.baseline_price_ext", "buildout_lineitem.item_class", "buildout_lineitem.user_created_by", "buildout_lineitem.sub_region", "buildout_lineitem.business_unit", "buildout_lineitem.created_by_id", "buildout_lineitem.item_description_id", "buildout_lineitem.modified_by_id", "buildout_lineitem.report_file_id"],
                  "Index Cond": "(buildout_lineitem.item_description_id = buildout_itemdescription.id)",
                  "Rows Removed by Index Recheck": 0,
                  "Filter": "((buildout_lineitem.unit_price > '0'::numeric) AND (buildout_lineitem.date >= '2014-04-20'::date) AND (buildout_lineitem.date <= '2019-03-25'::date) AND (buildout_lineitem.report_file_id = ANY ('{154,172,155,181,174,156,157,182,175,176,183,158,177,159,179,178,164,180,367,165,173,166,167,168,368,422,370,169,1335,1323,161,160,162,170,171,676,151,163,980,152,369,153,963,1718,881,617,1759,1780,636,1199,1243,947,1163,1422,1009,1407,1035,1241,1077,1271,1111,1130,1489,1507,1555,1600,1619,1663}'::integer[])))",
                  "Rows Removed by Filter": 10,
                  "Shared Hit Blocks": 1799678,
                  "Shared Read Blocks": 1013783,
                  "Shared Dirtied Blocks": 0,
                  "Shared Written Blocks": 0,
                  "Local Hit Blocks": 0,
                  "Local Read Blocks": 0,
                  "Local Dirtied Blocks": 0,
                  "Local Written Blocks": 0,
                  "Temp Read Blocks": 0,
                  "Temp Written Blocks": 0
                }
              ]
            }
          ]
        }
      ]
    },
    "Planning Time": 2.796,
    "Triggers": [
    ],
    "Execution Time": 7063.932
  }
]

对比

[
  {
    "Plan": {
      "Node Type": "Aggregate",
      "Strategy": "Sorted",
      "Partial Mode": "Simple",
      "Parallel Aware": false,
      "Startup Cost": 246569.04,
      "Total Cost": 249781.35,
      "Plan Rows": 129877,
      "Plan Width": 36,
      "Actual Startup Time": 561.755,
      "Actual Total Time": 580.878,
      "Actual Rows": 3771,
      "Actual Loops": 1,
      "Output": ["buildout_itemdescription.product_code_id", "min(buildout_lineitem.unit_price)"],
      "Group Key": ["buildout_itemdescription.product_code_id"],
      "Shared Hit Blocks": 5544,
      "Shared Read Blocks": 27043,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 3729,
      "Temp Written Blocks": 3730,
      "Plans": [
        {
          "Node Type": "Sort",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Startup Cost": 246569.04,
          "Total Cost": 247206.89,
          "Plan Rows": 255139,
          "Plan Width": 10,
          "Actual Startup Time": 561.741,
          "Actual Total Time": 568.372,
          "Actual Rows": 79253,
          "Actual Loops": 1,
          "Output": ["buildout_itemdescription.product_code_id", "buildout_lineitem.unit_price"],
          "Sort Key": ["buildout_itemdescription.product_code_id"],
          "Sort Method": "external merge",
          "Sort Space Used": 1624,
          "Sort Space Type": "Disk",
          "Shared Hit Blocks": 5544,
          "Shared Read Blocks": 27043,
          "Shared Dirtied Blocks": 0,
          "Shared Written Blocks": 0,
          "Local Hit Blocks": 0,
          "Local Read Blocks": 0,
          "Local Dirtied Blocks": 0,
          "Local Written Blocks": 0,
          "Temp Read Blocks": 3729,
          "Temp Written Blocks": 3730,
          "Plans": [
            {
              "Node Type": "Hash Join",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Join Type": "Inner",
              "Startup Cost": 59518.27,
              "Total Cost": 219295.38,
              "Plan Rows": 255139,
              "Plan Width": 10,
              "Actual Startup Time": 429.997,
              "Actual Total Time": 546.546,
              "Actual Rows": 79253,
              "Actual Loops": 1,
              "Output": ["buildout_itemdescription.product_code_id", "buildout_lineitem.unit_price"],
              "Inner Unique": true,
              "Hash Cond": "(buildout_lineitem.item_description_id = buildout_itemdescription.id)",
              "Shared Hit Blocks": 5541,
              "Shared Read Blocks": 27043,
              "Shared Dirtied Blocks": 0,
              "Shared Written Blocks": 0,
              "Local Hit Blocks": 0,
              "Local Read Blocks": 0,
              "Local Dirtied Blocks": 0,
              "Local Written Blocks": 0,
              "Temp Read Blocks": 3526,
              "Temp Written Blocks": 3526,
              "Plans": [
                {
                  "Node Type": "Index Scan",
                  "Parent Relationship": "Outer",
                  "Parallel Aware": false,
                  "Scan Direction": "Forward",
                  "Index Name": "buildout_lineitem_report_file_id_b56906e1",
                  "Relation Name": "buildout_lineitem",
                  "Schema": "public",
                  "Alias": "buildout_lineitem",
                  "Startup Cost": 0.43,
                  "Total Cost": 151740.64,
                  "Plan Rows": 332154,
                  "Plan Width": 10,
                  "Actual Startup Time": 0.063,
                  "Actual Total Time": 30.922,
                  "Actual Rows": 79253,
                  "Actual Loops": 1,
                  "Output": ["buildout_lineitem.unit_price", "buildout_lineitem.item_description_id"],
                  "Index Cond": "(buildout_lineitem.report_file_id = ANY ('{154,172,155,181,174,156,157,182,175,176,183,158,177,159,179,178,164,180,367,165,173,166,167,168,368,422,370,169,1335,1323,161,160,162,170,171,676,151,163,980,152,369,153,963,1718,881,617,1759,1780,636,1199,1243,947,1163,1422,1009,1407,1035,1241,1077,1271,1111,1130,1489,1507,1555,1600,1619,1663}'::integer[]))",
                  "Rows Removed by Index Recheck": 0,
                  "Filter": "((buildout_lineitem.unit_price > '0'::numeric) AND (buildout_lineitem.date >= '2014-04-20'::date) AND (buildout_lineitem.date <= '2019-03-25'::date))",
                  "Rows Removed by Filter": 6,
                  "Shared Hit Blocks": 1610,
                  "Shared Read Blocks": 991,
                  "Shared Dirtied Blocks": 0,
                  "Shared Written Blocks": 0,
                  "Local Hit Blocks": 0,
                  "Local Read Blocks": 0,
                  "Local Dirtied Blocks": 0,
                  "Local Written Blocks": 0,
                  "Temp Read Blocks": 0,
                  "Temp Written Blocks": 0
                },
                {
                  "Node Type": "Hash",
                  "Parent Relationship": "Inner",
                  "Parallel Aware": false,
                  "Startup Cost": 43050.17,
                  "Total Cost": 43050.17,
                  "Plan Rows": 1003733,
                  "Plan Width": 8,
                  "Actual Startup Time": 429.373,
                  "Actual Total Time": 429.373,
                  "Actual Rows": 1005163,
                  "Actual Loops": 1,
                  "Output": ["buildout_itemdescription.product_code_id", "buildout_itemdescription.id"],
                  "Hash Buckets": 131072,
                  "Original Hash Buckets": 131072,
                  "Hash Batches": 16,
                  "Original Hash Batches": 16,
                  "Peak Memory Usage": 3495,
                  "Shared Hit Blocks": 3931,
                  "Shared Read Blocks": 26052,
                  "Shared Dirtied Blocks": 0,
                  "Shared Written Blocks": 0,
                  "Local Hit Blocks": 0,
                  "Local Read Blocks": 0,
                  "Local Dirtied Blocks": 0,
                  "Local Written Blocks": 0,
                  "Temp Read Blocks": 0,
                  "Temp Written Blocks": 3213,
                  "Plans": [
                    {
                      "Node Type": "Seq Scan",
                      "Parent Relationship": "Outer",
                      "Parallel Aware": false,
                      "Relation Name": "buildout_itemdescription",
                      "Schema": "public",
                      "Alias": "buildout_itemdescription",
                      "Startup Cost": 0.00,
                      "Total Cost": 43050.17,
                      "Plan Rows": 1003733,
                      "Plan Width": 8,
                      "Actual Startup Time": 0.021,
                      "Actual Total Time": 287.632,
                      "Actual Rows": 1005163,
                      "Actual Loops": 1,
                      "Output": ["buildout_itemdescription.product_code_id", "buildout_itemdescription.id"],
                      "Filter": "(buildout_itemdescription.product_code_id IS NOT NULL)",
                      "Rows Removed by Filter": 301554,
                      "Shared Hit Blocks": 3931,
                      "Shared Read Blocks": 26052,
                      "Shared Dirtied Blocks": 0,
                      "Shared Written Blocks": 0,
                      "Local Hit Blocks": 0,
                      "Local Read Blocks": 0,
                      "Local Dirtied Blocks": 0,
                      "Local Written Blocks": 0,
                      "Temp Read Blocks": 0,
                      "Temp Written Blocks": 0
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    },
    "Planning Time": 2.965,
    "Triggers": [
    ],
    "Execution Time": 581.324
  }
]

在Django中，我的代码是：
        from rest_framework.pagination import LimitOffsetPagination


        line_items = LineItem.objects.filter(
            report_file__report=report,
            unit_price__gt=0
        ).exclude(
            item_description__product_code__isnull=True
        )


        item_refs_aggregated = line_items.values(
            'item_description__product_code_id'
        ).annotate(
            min_price=Min('unit_price'),
        ).values(
            "item_description__product_code",
            "min_price",
        ).order_by(*ordering)


        paginator = LimitOffsetPagination()
        paginator.page_size = 10
        result_page = paginator.paginate_queryset(
            item_refs_aggregated,
            request
        )

这些表的索引包括： buildout_lineitem: 


Indexes:
    "buildout_lineitem_pkey" PRIMARY KEY, btree (id)
    "buildout_lineitem_created_by_id_a61c52b7" btree (created_by_id)
    "buildout_lineitem_item_description_id_88254e09" btree (item_description_id)
    "buildout_lineitem_modified_by_id_8668530a" btree (modified_by_id)
    "buildout_lineitem_report_file_id_b56906e1" btree (report_file_id)
Foreign-key constraints:
    "buildout_lineitem_created_by_id_a61c52b7_fk_auth_user_id" FOREIGN KEY (created_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "buildout_lineitem_item_description_id_88254e09_fk_buildout_" FOREIGN KEY (item_description_id) REFERENCES buildout_itemdescription(id) DEFERRABLE INITIALLY DEFERRED
    "buildout_lineitem_modified_by_id_8668530a_fk_auth_user_id" FOREIGN KEY (modified_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "buildout_lineitem_report_file_id_b56906e1_fk_buildout_" FOREIGN KEY (report_file_id) REFERENCES buildout_reportfile(id) DEFERRABLE INITIALLY DEFERRED

buildout_itemdescription：

Indexes:
    "buildout_itemdescription_pkey" PRIMARY KEY, btree (id)
    "buildout_itemdescription_created_by_id_32dc4fc9" btree (created_by_id)
    "buildout_itemdescription_description_category_1_c_35f46dc6_idx" btree (description, category_1, category_2, category_3, manufacturer_sku, manufacturer_name, distributo
r_sku, supplier_id, unit_of_measure, quantity_in_unit_of_measure, region_id)
    "buildout_itemdescription_distributor_name_id_ae72fd8a" btree (supplier_id)
    "buildout_itemdescription_modified_by_id_58cf6676" btree (modified_by_id)
    "buildout_itemdescription_product_code_id_084c51de" btree (product_code_id)
    "buildout_itemdescription_region_id_c1682fff" btree (region_id)
Foreign-key constraints:
    "buildout_itemdescrip_modified_by_id_58cf6676_fk_auth_user" FOREIGN KEY (modified_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "buildout_itemdescrip_product_code_id_084c51de_fk_buildout_" FOREIGN KEY (product_code_id) REFERENCES buildout_itemref(id) DEFERRABLE INITIALLY DEFERRED
    "buildout_itemdescrip_region_id_c1682fff_fk_buildout_" FOREIGN KEY (region_id) REFERENCES buildout_region(id) DEFERRABLE INITIALLY DEFERRED
    "buildout_itemdescrip_supplier_id_525b55c4_fk_buildout_" FOREIGN KEY (supplier_id) REFERENCES buildout_supplier(id) DEFERRABLE INITIALLY DEFERRED
    "buildout_itemdescription_created_by_id_32dc4fc9_fk_auth_user_id" FOREIGN KEY (created_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED

以下是相关模型：

LineItem

class LineItem(BaseModel):
    '''
        Field summary: date, month, unit_price, extended_sell,
        list_price, list_price_ext, agreed_price, agreed_price_ext,
        baseline_price, baseline_price_ext, item_class,
        user_created_by, report_file, item_description
    '''
    # make a model regarding the file
    # and include filename = db.Column(db.String())
    # link to it as a foreign key

    class Meta:
        abstract = True

    objects = LineItemManager()

    date = models.DateField(
        _("Date"),
        auto_now=False,
        auto_now_add=False,
        blank=False,
        null=True
    )
    month = models.DateField(
        _("Month"),
        auto_now=False,
        auto_now_add=False,
        blank=False,
        null=True
    )
    quantity = models.IntegerField(
        _("Qty"),
        null=True,
        blank=False,
    )

    unit_price = models.DecimalField(
        _("Unit Price"),
        max_digits=19,
        decimal_places=2
    )
    extended_sell = models.DecimalField(
        _("Ext Sell"),
        null=True,
        blank=True,
        max_digits=19,
        decimal_places=2,
    )
    list_price = models.DecimalField(
        _("List Price"),
        null=True,
        blank=True,
        max_digits=19,
        decimal_places=2,
    )
    list_price_ext = models.DecimalField(
        _("List Price Ext"),
        null=True,
        blank=True,
        max_digits=19,
        decimal_places=2,
    )
    agreed_price = models.DecimalField(
        _("Agreed Price"),
        null=True,
        blank=True,
        max_digits=19,
        decimal_places=2,
    )
    agreed_price_ext = models.DecimalField(
        _("Agreed Price Ext"),
        null=True,
        blank=True,
        max_digits=19,
        decimal_places=2,
    )
    baseline_price = models.DecimalField(
        _("Baseline Price"),
        null=True,
        blank=True,
        max_digits=19,
        decimal_places=2,
    )
    baseline_price_ext = models.DecimalField(
        _("Baseline Price Ext"),
        null=True,
        blank=True,
        max_digits=19,
        decimal_places=2,
    )
    item_class = models.CharField(
        _("Class"),
        max_length=500,
        null=True,
        blank=True,
    )

    user_created_by = models.CharField(
        _("User Created By"),
        max_length=50,
        null=True,
        blank=True,
    )

    report_file = models.ForeignKey(
        ReportFile,
        verbose_name="Report File",
        on_delete=models.CASCADE,
        null=True,
        blank=True,
    )

    item_description = models.ForeignKey(
        "ItemDescription",
        verbose_name=_("Item Desc"),
        on_delete=models.CASCADE
    )

    sub_region = models.CharField(
        _("Sub Region"),
        max_length=500,
        null=True,
        blank=True,
    )

    business_unit = models.CharField(
        _("Sub Region"),
        max_length=500,
        null=True,
        blank=True,
    )

    # TODO: client property is untested
    @property
    def client(self):
        return self.report_file.report.client

    @property
    def date_with_fallback(self):
        if self.date:
            return self.date
        if self.month:
            return self.month
        return None

    @client.setter
    def client(self, value):
        self.report_file.report.client = value

    def __str__(self):
        return f"{self.date} {self.list_price}"

ItemRef

class ItemRef(BaseModel):
    identification_method = models.ForeignKey(
        'IdentificationMethod',
        verbose_name=_("Identification Method"),
        on_delete=models.CASCADE
    )

    identification_values = JSONField()

    def __str__(self):
        return str(self.id)

项目描述

class ItemDescription(BaseModel):
    '''
        Field summary:
            description, category_1, category_2, category_3
            manufacturer_sku, manufacturer_name, distributor_sku,
            supplier, unit_of_measure,
            quantity_in_unit_of_measure, product_code, region
    '''
    description = models.TextField(
        _("desc"),
        blank=False,
        null=True
    )
    category_1 = models.CharField(
        _("Cat 1"),
        max_length=500,
        null=True,
        blank=True,
    )

    category_2 = models.CharField(
        _("Cat 2"),
        max_length=500,
        null=True,
        blank=True,
    )
    category_3 = models.CharField(
        _("Cat 3"),
        max_length=500,
        null=True,
        blank=True,
    )

    manufacturer_sku = models.CharField(
        _("Mfr SKU"),
        max_length=500,
        null=True,
    )

    manufacturer_name = models.CharField(
        _("Mfr Name"),
        max_length=200,
        null=True,
    )
    distributor_sku = models.CharField(
        _("Dist SKU"),
        max_length=500,
        null=True,
    )

    unit_of_measure = models.CharField(
        _("UOM"),
        max_length=50,
        null=True,
    )

    quantity_in_unit_of_measure = models.IntegerField(
        _("Qty In UOM"),
        null=True,
        blank=True,
    )

    product_code = models.ForeignKey(
        ItemRef,
        verbose_name=_("Product Code"),
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
    )

    supplier = models.ForeignKey(
        Supplier,
        verbose_name=_("Dist Name"),
        on_delete=models.CASCADE
    )

    region = models.ForeignKey(
        Region, verbose_name=_("Region"),
        on_delete=models.CASCADE
    )

    def get_fields(self, fields):
        # returns the selected fields as a list and the id
        result = []
        for field in fields:
            value = getattr(self, field)
            result.append(value)
        return tuple(result), self.pk

    def __str__(self):
        return str(self.description)

    class Meta:
        index_together = [
            [
                'description', 'category_1', 'category_2',
                'category_3', 'manufacturer_sku', 'manufacturer_name',
                'distributor_sku', 'supplier', 'unit_of_measure',
                'quantity_in_unit_of_measure', 'region'
            ]
        ]

我尝试在Postgres中对表运行分析，但没有帮助。在我链接的问题中，答案是添加一个索引，但我不确定我要添加什么索引。
你可以展示一下你所使用的Django查询吗？ - Hybrid
我添加了一个简化版本的Django代码。 - Zev
谢谢，我已经为这两个添加了索引。更长的查询现在从7.3降至7.0。任何小优化都有帮助，但主要问题仍然存在。Django反规范化库是否有帮助？ - Zev
如果IN子句相关的话，我会感到惊讶，但是你可以将其替换为与单个值的相等比较，并在数据库 shell 中运行查询以进行检查。 - Endre Both
@EndreBoth 我把 FROM 和 INNER JOIN 对调了，但计时结果完全相同，所以我想它们是等价的。 - Zev
显示剩余8条评论
1个回答
10

PostgreSQL错误地认为按照product_code_id的顺序扫描行直到找到满足所有条件的第一行会更快，但它没有意识到第一个匹配的行不靠近开头。因此，它最终执行了比预期更多的嵌套循环连接。

删除该索引将加速查询，但如果不是选项，请尝试使用

...
ORDER BY buildout_itemdescription.product_code_id + 0
LIMIT 5

那么PostgreSQL无法使用该索引。

你的回答仍然为我带来了最佳性能，但对于 SSD 进行的一些数据库调优也有所帮助 https://amplitude.engineering/how-a-single-postgresql-config-change-improved-slow-query-performance-by-50x-85593b8991b0 - Zev
当然。告诉你的数据库关于你的硬件信息是个好主意。我也会增加 effective_io_concurrency。 - Laurenz Albe
我还发现，如果语句中有多个连接，并且这不足以“指导”规划器，解决方案是将最有效的连接移动到单独的CTE中。这肯定会强制规划器使用连接的索引，并按照您提供的严格顺序执行。 - Luke
PostgreSQL v12 中已经发生了变化。 - Laurenz Albe
互联网上最好的答案，我尝试了很多其他被很多人推荐的解决方案，我认为在这种情况下索引会使查询更快，但事实并非如此，现在我的性能提高了346倍。 - Sushin Pv
显示剩余2条评论
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接