Styjun

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.

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.