I'm working on a Magento 2.3.0 store with 790k SKUs and 7k Categories. Category pages and Single product page open load time is more than 10s. I enabled db query log and I could see below query that takes more than 3 seconds.
## 2019-01-19 19:32:07
## 9478 ## QUERY
SQL: SELECT `inventory_stock_1`.`quantity`, `inventory_stock_1`.`is_salable` FROM `inventory_stock_1` WHERE (sku = 'P00AA40')
AFF: 1
TIME: 3.0955
TRACE: #1 Magento\Framework\DB\Logger\File[Magento\Framework\DB\Logger\LoggerAbstract]#000000006e030af800000000689c3fe1#->getStats('query', 'SELECT `inventor...', array(), &Magento\Framework\DB\Statement\Pdo\Mysql#000000006e03021000000000689c3fe1#) called at [vendor/magento/framework/DB/Logger/File.php:67]
#2 Magento\Framework\DB\Logger\File#000000006e030af800000000689c3fe1#->logStats('query', 'SELECT `inventor...', array(), &Magento\Framework\DB\Statement\Pdo\Mysql#000000006e03021000000000689c3fe1#) called at [vendor/magento/framework/DB/Logger/LoggerProxy.php:152]
#3 Magento\Framework\DB\Logger\LoggerProxy#000000006e030aef00000000689c3fe1#->logStats('query', 'SELECT `inventor...', array(), &Magento\Framework\DB\Statement\Pdo\Mysql#000000006e03021000000000689c3fe1#) called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:542]
#4 Magento\Framework\DB\Adapter\Pdo\Mysql#000000006e030ae800000000689c3fe1#->_query('SELECT `inventor...', array()) called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:615]
#5 Magento\Framework\DB\Adapter\Pdo\Mysql#000000006e030ae800000000689c3fe1#->query(&Magento\Framework\DB\Select#000000006e03020b00000000689c3fe1#, array()) called at [vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php:756]
#6 Magento\Framework\DB\Adapter\Pdo\Mysql[Zend_Db_Adapter_Abstract]#000000006e030ae800000000689c3fe1#->fetchRow(&Magento\Framework\DB\Select#000000006e03020b00000000689c3fe1#) called at [vendor/magento/module-inventory-indexer/Model/ResourceModel/GetStockItemData.php:63]
#7 Magento\InventoryIndexer\Model\ResourceModel\GetStockItemData#000000006e030f3a00000000689c3fe1#->execute('P00AA40', 1) called at [vendor/magento/module-inventory-sales/Model/GetProductSalableQty.php:75]
#8 Magento\InventorySales\Model\GetProductSalableQty#000000006e03021800000000689c3fe1#->execute('P00AA40', 1) called at [vendor/magento/module-inventory-sales-frontend-ui/Plugin/Block/Stockqty/AbstractStockqtyPlugin.php:80]
#9 Magento\InventorySalesFrontendUi\Plugin\Block\Stockqty\AbstractStockqtyPlugin#000000006e03020a00000000689c3fe1#->aroundIsMsgVisible(&Magento\CatalogInventory\Block\Stockqty\DefaultStockqty\Interceptor#000000006e030ce500000000689c3fe1#, &Closure#000000006e03021e00000000689c3fe1#) called at [vendor/magento/framework/Interception/Interceptor.php:135]
#10 Magento\CatalogInventory\Block\Stockqty\DefaultStockqty\Interceptor#000000006e030ce500000000689c3fe1#->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#11 Magento\CatalogInventory\Block\Stockqty\DefaultStockqty\Interceptor#000000006e030ce500000000689c3fe1#->___callPlugins('isMsgVisible', array(), NULL) called at [generated/code/Magento/CatalogInventory/Block/Stockqty/DefaultStockqty/Interceptor.php:26]
#12 Magento\CatalogInventory\Block\Stockqty\DefaultStockqty\Interceptor#000000006e030ce500000000689c3fe1#->isMsgVisible() called at [vendor/magento/module-catalog-inventory/Block/Stockqty/DefaultStockqty.php:28]
#13 Magento\CatalogInventory\Block\Stockqty\DefaultStockqty\Interceptor[Magento\CatalogInventory\Block\Stockqty\DefaultStockqty]#000000006e030ce500000000689c3fe1#->_toHtml() called at [vendor/magento/framework/View/Element/AbstractBlock.php:668]
#14 Magento\CatalogInventory\Block\Stockqty\DefaultStockqty\Interceptor[Magento\Framework\View\Element\AbstractBlock]#000000006e030ce500000000689c3fe1#->toHtml() called at [vendor/magento/framework/View/Layout.php:557]
#15 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderBlock('product.info.sim...') called at [vendor/magento/framework/View/Layout.php:533]
#16 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('product.info.sim...') called at [vendor/magento/framework/View/Layout.php:488]
#17 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('product.info.sim...', false) called at [vendor/magento/framework/View/Layout.php:585]
#18 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('product.info.sim...', false) called at [vendor/magento/framework/View/Layout.php:535]
#19 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('product.info.sim...') called at [vendor/magento/framework/View/Layout.php:488]
#20 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('product.info.sim...', false) called at [vendor/magento/framework/View/Layout.php:585]
#21 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('product.info.typ...', false) called at [vendor/magento/framework/View/Layout.php:535]
#22 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('product.info.typ...') called at [vendor/magento/framework/View/Layout.php:488]
#23 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('product.info.typ...', false) called at [vendor/magento/framework/View/Layout.php:585]
#24 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('product.info.sto...', false) called at [vendor/magento/framework/View/Layout.php:535]
#25 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('product.info.sto...') called at [vendor/magento/framework/View/Layout.php:488]
#26 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('product.info.sto...', false) called at [vendor/magento/framework/View/Layout.php:585]
#27 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('product.info.pri...', false) called at [vendor/magento/framework/View/Layout.php:535]
#28 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('product.info.pri...') called at [vendor/magento/framework/View/Layout.php:488]
#29 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('product.info.pri...', false) called at [vendor/magento/framework/View/Layout.php:585]
#30 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('product.info.mai...', false) called at [vendor/magento/framework/View/Layout.php:535]
#31 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('product.info.mai...') called at [vendor/magento/framework/View/Layout.php:488]
#32 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('product.info.mai...', false) called at [vendor/magento/framework/View/Layout.php:585]
#33 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('content', false) called at [vendor/magento/framework/View/Layout.php:535]
#34 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('content') called at [vendor/magento/framework/View/Layout.php:488]
#35 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('content', false) called at [vendor/magento/framework/View/Layout.php:585]
#36 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('main', false) called at [vendor/magento/framework/View/Layout.php:535]
#37 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('main') called at [vendor/magento/framework/View/Layout.php:488]
#38 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('main', false) called at [vendor/magento/framework/View/Layout.php:585]
#39 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('columns', false) called at [vendor/magento/framework/View/Layout.php:535]
#40 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('columns') called at [vendor/magento/framework/View/Layout.php:488]
#41 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('columns', false) called at [vendor/magento/framework/View/Layout.php:585]
#42 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('main.content', false) called at [vendor/magento/framework/View/Layout.php:535]
#43 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('main.content') called at [vendor/magento/framework/View/Layout.php:488]
#44 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('main.content', false) called at [vendor/magento/framework/View/Layout.php:585]
#45 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('page.wrapper', false) called at [vendor/magento/framework/View/Layout.php:535]
#46 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('page.wrapper') called at [vendor/magento/framework/View/Layout.php:488]
#47 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('page.wrapper', false) called at [vendor/magento/framework/View/Layout.php:585]
#48 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->_renderContainer('root', false) called at [vendor/magento/framework/View/Layout.php:535]
#49 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderNonCachedElement('root') called at [vendor/magento/framework/View/Layout.php:488]
#50 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->renderElement('root') called at [vendor/magento/framework/View/Layout.php:954]
#51 Magento\Framework\View\Layout\Interceptor[Magento\Framework\View\Layout]#000000006e030bda00000000689c3fe1#->getOutput() called at [vendor/magento/framework/Interception/Interceptor.php:58]
#52 Magento\Framework\View\Layout\Interceptor#000000006e030bda00000000689c3fe1#->___callParent('getOutput', array()) called at [vendor/magento/framework/Interception/Interceptor.php:138]
#53 Magento\Framework\View\Layout\Interceptor#000000006e030bda00000000689c3fe1#->Magento\Framework\Interception\{closure}() called at [vendor/magento/framework/Interception/Interceptor.php:153]
#54 Magento\Framework\View\Layout\Interceptor#000000006e030bda00000000689c3fe1#->___callPlugins('getOutput', array(), array(array('layout-model-cac...'))) called at [generated/code/Magento/Framework/View/Layout/Interceptor.php:39]
#55 Magento\Framework\View\Layout\Interceptor#000000006e030bda00000000689c3fe1#->getOutput() called at [vendor/magento/framework/View/Result/Page.php:258]
#56 Magento\Framework\View\Result\Page\Interceptor[Magento\Framework\View\Result\Page]#000000006e03098000000000689c3fe1#->render(&Magento\Framework\App\Response\Http\Interceptor#000000006e030aa000000000689c3fe1#) called at [vendor/magento/framework/View/Result/Layout.php:171]
#57 Magento\Framework\View\Result\Page\Interceptor[Magento\Framework\View\Result\Layout]#000000006e03098000000000689c3fe1#->renderResult(&Magento\Framework\App\Response\Http\Interceptor#000000006e030aa000000000689c3fe1#) called at [vendor/magento/framework/Interception/Interceptor.php:58]
#58 Magento\Framework\View\Result\Page\Interceptor#000000006e03098000000000689c3fe1#->___callParent('renderResult', array(&Magento\Framework\App\Response\Http\Interceptor#000000006e030aa000000000689c3fe1#)) called at [vendor/magento/framework/Interception/Interceptor.php:138]
#59 Magento\Framework\View\Result\Page\Interceptor#000000006e03098000000000689c3fe1#->Magento\Framework\Interception\{closure}(&Magento\Framework\App\Response\Http\Interceptor#000000006e030aa000000000689c3fe1#) called at [vendor/magento/framework/Interception/Interceptor.php:153]
#60 Magento\Framework\View\Result\Page\Interceptor#000000006e03098000000000689c3fe1#->___callPlugins('renderResult', array(&Magento\Framework\App\Response\Http\Interceptor#000000006e030aa000000000689c3fe1#), array(array('result-messages', 'result-builtin-c...', 'result-varnish-c...'))) called at [generated/code/Magento/Framework/View/Result/Page/Interceptor.php:26]
#61 Magento\Framework\View\Result\Page\Interceptor#000000006e03098000000000689c3fe1#->renderResult(&Magento\Framework\App\Response\Http\Interceptor#000000006e030aa000000000689c3fe1#) called at [vendor/magento/framework/App/Http.php:139]
#62 Magento\Framework\App\Http\Interceptor[Magento\Framework\App\Http]#000000006e030aa200000000689c3fe1#->launch() called at [vendor/magento/framework/App/Bootstrap.php:258]
#63 Magento\Framework\App\Bootstrap#000000006e030a0d00000000689c3fe1#->run(&Magento\Framework\App\Http\Interceptor#000000006e030aa200000000689c3fe1#) called at [index.php:39]
What could be the reason for this? I'm using Luma theme and disabled all 3rd part modules.