Menu
I'm experiencing an issue similar to PostgreSQL query very slow with limit 1 and Extremely slow PostgreSQL query with ORDER and LIMIT clauses although in my case it doesn't matter if the LIMIT is 1, 5 or 500.
Basically, when I run a query generated by Django's ORM without a limit, the query takes half a second but with the limit (added for pagination) it takes 7 seconds.
The query that takes 7 seconds is:
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
And the other query is the same without the LIMIT 5.
Postgres is using very different plans for the two queries (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
}
]
vs
[
{
"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
}
]
In Django, my code is:
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
)
The indexes on these tables are: 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
Here are the relevant models:
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)
ItemDescription
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'
]
]
I've tried running analyze on the tables in Postgres, but it didn't help. In the question I linked to, the answer was to add an index but I'm not sure what index I'd add here.
209
PostgreSQL mistakenly thinks that it can be fast by scanning the rows in product_code_id order until it has found the first one satisfying all conditions, but it is not aware that the first matching row is not close to the beginning. So it ends up executing more of the nested loop join than it bargained for.
Removing that index would speed up the query, but if that is not an option, try using
...
ORDER BY buildout_itemdescription.product_code_id + 0
LIMIT 5
Then PostgreSQL cannot use the index.
113
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
