explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2OCm

Settings
# exclusive inclusive rows x rows loops node
1. 10.132 1,364.786 ↑ 6.4 971 1

GroupAggregate (cost=3,148,289.23..3,148,862.17 rows=6,194 width=456) (actual time=1,354.280..1,364.786 rows=971 loops=1)

2.          

CTE pvresults

3. 3.075 1,308.050 ↑ 4.0 1,561 1

Sort (cost=3,128,973.48..3,128,988.96 rows=6,194 width=643) (actual time=1,307.762..1,308.050 rows=1,561 loops=1)

  • Sort Key: pv.productvariantid
  • Sort Method: quicksort Memory: 732kB
4. 2.463 1,304.975 ↑ 4.0 1,561 1

Nested Loop (cost=1.58..3,128,583.36 rows=6,194 width=643) (actual time=4.356..1,304.975 rows=1,561 loops=1)

5. 2.009 1,274.414 ↑ 4.0 1,561 1

Nested Loop (cost=1.14..3,076,954.87 rows=6,194 width=635) (actual time=3.853..1,274.414 rows=1,561 loops=1)

6. 1,157.827 1,157.827 ↑ 3.0 971 1

Index Scan using producttable_pkey on producttable (cost=0.57..13,464.26 rows=2,940 width=595) (actual time=2.592..1,157.827 rows=971 loops=1)

  • Index Cond: ((productid >= 6065895) AND (productid <= 6070895))
  • Filter: (productstatus = 'visible'::text)
  • Rows Removed by Filter: 3253
7. 114.578 114.578 ↑ 231.0 2 971

Index Scan using idx_productid_productvarianttable on productvarianttable pv (cost=0.57..1,037.38 rows=462 width=48) (actual time=0.078..0.118 rows=2 loops=971)

  • Index Cond: (productid = producttable.productid)
  • Filter: (productvariantstatus = 'visible'::text)
  • Rows Removed by Filter: 0
8. 28.098 28.098 ↑ 1.0 1 1,561

Index Scan using idx_productvariantid_productvariantinfotable on productvariantinfotable pvi (cost=0.43..8.32 rows=1 width=16) (actual time=0.017..0.018 rows=1 loops=1,561)

  • Index Cond: (productvariantid = COALESCE(pv.parentvariantid, pv.productvariantid))
9. 42.296 1,354.654 ↑ 4.0 1,561 1

Sort (cost=19,300.27..19,315.75 rows=6,194 width=456) (actual time=1,354.219..1,354.654 rows=1,561 loops=1)

  • Sort Key: pvresults.productid, pvresults.storeid, pvresults.productname, pvresults.productdescription, pvresults.productimageids, pvresults.imageids, pvresults.productcategoryid, pvresults.gstsubcategoryid, pvresults.parentproductid, pvresults.productcode, pvresults.seostatus, mrpproducttable.productid
  • Sort Method: quicksort Memory: 733kB
10. 1.705 1,312.358 ↑ 4.0 1,561 1

Nested Loop Left Join (cost=0.43..18,910.15 rows=6,194 width=456) (actual time=1,307.781..1,312.358 rows=1,561 loops=1)

11. 1,309.092 1,309.092 ↑ 4.0 1,561 1

CTE Scan on pvresults (cost=0.00..123.88 rows=6,194 width=448) (actual time=1,307.767..1,309.092 rows=1,561 loops=1)

12. 1.561 1.561 ↓ 0.0 0 1,561

Index Only Scan using mrpproducttable_pkey on mrpproducttable (cost=0.43..3.02 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,561)

  • Index Cond: (productid = pvresults.productid)
  • Heap Fetches: 0