Using Flat Catalog will help you speed up your product collection instead of managing the catalog data in many tables by the Entity Attribute Value (EAV). When your store applies the Flat Catalog, the new tables on the fly are generated and they will store all necessary data related to your products or categories.
The flat catalog is updated automatically and quickly in every minute or according to your cron job regradless that your catalog contains more than 500,000 SKUs. Apparently, the flat catalog can also speed up the processing of catalog and cart price rules without any difficulties.
In this tutorial, I will guide you how to enable the flat catalog in an effective way.
Stores > Settings > Configurations.
Use Flat Catalog Categoryfield.
Use Flat Catalog Productfield.
Save Configto complete.
Products > Inventory > Catalog.
Namefield, insert the text “_DEMO” to the product name.
To make sure the results are correct, let refresh the page and the change will be displayed with your cron job schedule.
Products > Inventory > Categories.
Store Viewto display, you can set for all store views or a specific store view as you need.
Add Subcategorylink, and do as the following:
Save Categoryto save the settings.
Category Productstab, click
Reset Filterlink to make all products visible.
To make sure the results are correct, refresh the page and the change will be displayed with your cron job schedule.
Ref: Magento 2 User Guide
It comes to the end of tutorial: How to Enable Flat Catalog in Magento 2.