个人中心
文章发布
退出
作者热门文章
- html - 出于某种原因，IE8 对我的 Sass 文件中继承的 html5 CSS 不友好？
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景？
25
4
我遇到了类似于 PostgreSQL query very slow with limit 1 的问题和 Extremely slow PostgreSQL query with ORDER and LIMIT clauses尽管在我的例子中，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 与 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
相关模型如下:
订单项
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 中的表上运行 analyze，但没有帮助。在我链接到的问题中，答案是添加一个索引，但我不确定我要在此处添加什么索引。
最佳答案
PostgreSQL 误认为可以快速扫描product_code_id顺序的行，直到找到第一个满足所有条件的行，却不知道第一个匹配的行不接近开始。因此它最终执行了比预期更多的嵌套循环连接。
删除该索引会加快查询速度，但如果那不是一个选项，请尝试使用
...
ORDER BY buildout_itemdescription.product_code_id + 0
LIMIT 5
那么 PostgreSQL 就不能使用索引了。
关于postgresql - 使用 LIMIT 的慢 Postgres 查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/55344598/
25
4
0
我在网上搜索但没有找到任何合适的文章解释如何使用 javascript 使用 WCF 服务，尤其是 WebScriptEndpoint。 任何人都可以对此给出任何指导吗？ 谢谢 最佳答案 这是一篇关于
我正在编写一个将运行 Linux 命令的 C 程序，例如: cat/etc/passwd | grep 列表 |剪切-c 1-5 我没有任何结果 *这里 parent 等待第一个 child (chi
所以我正在尝试处理文件上传，然后将该文件作为二进制文件存储到数据库中。在我存储它之后，我尝试在给定的 URL 上提供文件。我似乎找不到适合这里的方法。我需要使用数据库，因为我使用 Google 应用引
我正在尝试制作一个宏，将下面的公式添加到单元格中，然后将其拖到整个列中并在 H 列中复制相同的公式 我想在 F 和 H 列中输入公式的数据 Range("F1").formula = "=IF(ISE
问题类似于this one ，但我想使用 OperatorPrecedenceParser 解析带有函数应用程序的表达式在 FParsec . 这是我的 AST: type Expression =
我想通过使用 sequelize 和 node.js 将这个查询更改为代码取决于在哪里 select COUNT(gender) as genderCount from customers where
我正在使用GNU bash，版本5.0.3(1)-发行版(x86_64-pc-linux-gnu)，我想知道为什么简单的赋值语句会出现语法错误: #/bin/bash var1=/tmp
这里，为什么我的代码在 IE 中不起作用。我的代码适用于所有浏览器。没有问题。但是当我在 IE 上运行我的项目时，它发现错误。 而且我的 jquery 类和 insertadjacentHTMl 也不
我正在尝试更改标签的innerHTML。我无权访问该表单，因此无法编辑 HTML。标签具有的唯一标识符是“for”属性。 这是输入和标签的结构:
我有一个页面，我可以在其中返回用户帖子，可以使用一些 jquery 代码对这些帖子进行即时评论，在发布新评论后，我在帖子下插入新评论以及删除 按钮。问题是 Delete 按钮在新插入的元素上不起作用，
我有一个大约有 20 列的“管道分隔”文件。我只想使用 sha1sum 散列第一列，它是一个数字，如帐号，并按原样返回其余列。 使用 awk 或 sed 执行此操作的最佳方法是什么？ Accounti
我需要将以下内容插入到我的表中...我的用户表有五列 id、用户名、密码、名称、条目。 (我还没有提交任何东西到条目中，我稍后会使用 php 来做)但由于某种原因我不断收到这个错误:#1054 - U
所以我试图有一个输入字段，我可以在其中输入任何字符，但然后将输入的值小写，删除任何非字母数字字符，留下“。”而不是空格。 例如，如果我输入: 地球的 70% 是水，-!*#$^^ & 30% 土地 输
我正在尝试做一些我认为非常简单的事情，但出于某种原因我没有得到想要的结果？我是 javascript 的新手，但对 java 有经验，所以我相信我没有使用某种正确的规则。 这是一个获取输入值、检查选择
我想使用 angularjs 从 mysql 数据库加载数据。 这就是应用程序的工作原理；用户登录，他们的用户名存储在 cookie 中。该用户名显示在主页上 我想获取这个值并通过 angularjs
我正在使用 autoLayout，我想在 UITableViewCell 上放置一个 UIlabel，它应该始终位于单元格的右侧和右侧的中心。 这就是我想要实现的目标 所以在这里你可以看到我正在谈论的
我需要与 MySql 等效的 elasticsearch 查询。我的 sql 查询: SELECT DISTINCT t.product_id AS id FROM tbl_sup_price t
我正在实现代码以使用 JSON。 func setup() { if let flickrURL = NSURL(string: "https://api.flickr.com/
我尝试使用for循环声明变量，然后测试cols和rols是否相同。如果是，它将运行递归函数。但是，我在 javascript 中执行 do 时遇到问题。有人可以帮忙吗？ 现在，在比较 col.1 和
我举了一个我正在处理的问题的简短示例。 HTML代码: 1 2 3 CSS 代码: .BB a:hover{ color: #000; } .BB > li:after {
我是一名优秀的程序员,十分优秀！