explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BO7C

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 3,508.428 ↑ 4.5 62,449 1

Finalize GroupAggregate (cost=12,113,370.47..12,157,430.38 rows=279,857 width=232) (actual time=3,399.260..3,508.428 rows=62,449 loops=1)

  • Group Key: (date((cartitemtable.cartitemtimestamp + '05:30:00'::interval))), productvarianttable.parentvariantid
2. 124.734 3,545.537 ↑ 3.7 63,697 1

Gather Merge (cost=12,113,370.47..12,146,119.50 rows=233,214 width=168) (actual time=3,399.246..3,545.537 rows=63,697 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 23.342 3,420.803 ↑ 5.5 21,232 3 / 3

Partial GroupAggregate (cost=12,112,370.45..12,118,200.80 rows=116,607 width=168) (actual time=3,394.008..3,420.803 rows=21,232 loops=3)

  • Group Key: (date((cartitemtable.cartitemtimestamp + '05:30:00'::interval))), productvarianttable.parentvariantid
4. 22.564 3,397.461 ↑ 4.2 27,467 3 / 3

Sort (cost=12,112,370.45..12,112,661.96 rows=116,607 width=99) (actual time=3,393.991..3,397.461 rows=27,467 loops=3)

  • Sort Key: (date((cartitemtable.cartitemtimestamp + '05:30:00'::interval))), productvarianttable.parentvariantid
  • Sort Method: quicksort Memory: 7,947kB
  • Worker 0: Sort Method: quicksort Memory: 6,974kB
  • Worker 1: Sort Method: quicksort Memory: 7,842kB
5. 13.330 3,374.897 ↑ 4.2 27,467 3 / 3

Parallel Hash Join (cost=299,604.31..12,102,557.21 rows=116,607 width=99) (actual time=3,169.557..3,374.897 rows=27,467 loops=3)

  • Hash Cond: (wholesalecatalogueproducttable.catalogueid = wholesalecataloguetable.catalogueid)
6. 21.308 3,271.825 ↑ 5.4 27,496 3 / 3

Parallel Hash Join (cost=266,456.34..12,068,433.65 rows=149,547 width=95) (actual time=3,079.022..3,271.825 rows=27,496 loops=3)

  • Hash Cond: (producttable.parentproductid = wholesalecatalogueproducttable.productid)
7. 19.836 3,078.778 ↑ 5.6 27,498 3 / 3

Nested Loop (cost=243,560.70..12,045,136.32 rows=153,021 width=87) (actual time=2,903.668..3,078.778 rows=27,498 loops=3)

8. 136.771 2,973.214 ↑ 8.2 28,576 3 / 3

Parallel Hash Join (cost=243,560.12..10,225,548.04 rows=232,920 width=51) (actual time=2,903.651..2,973.214 rows=28,576 loops=3)

  • Hash Cond: (productvarianttable.parentvariantid = pvit.productvariantid)
9. 142.369 1,389.545 ↑ 4.9 257,237 3 / 3

Nested Loop (cost=0.57..9,977,155.27 rows=1,252,290 width=36) (actual time=0.155..1,389.545 rows=257,237 loops=3)

10. 469.041 469.041 ↑ 4.9 259,378 3 / 3

Parallel Seq Scan on cartitemtable (cost=0.00..205,582.31 rows=1,262,558 width=32) (actual time=0.134..469.041 rows=259,378 loops=3)

  • Filter: (date((cartitemtimestamp + '05:30:00'::interval)) >= '2020-07-02'::date)
  • Rows Removed by Filter: 2,785,372
11. 778.135 778.135 ↑ 1.0 1 778,135 / 3

Index Scan using productvarianttable_pkey on productvarianttable (cost=0.57..7.74 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=778,135)

  • Index Cond: (productvariantid = cartitemtable.cartvariantid)
  • Filter: (productvariantstatus <> 'deleted'::text)
  • Rows Removed by Filter: 0
12. 249.667 1,446.898 ↓ 2.9 524,237 3 / 3

Parallel Hash (cost=241,263.45..241,263.45 rows=183,688 width=31) (actual time=1,446.897..1,446.898 rows=524,237 loops=3)

  • Buckets: 524,288 (originally 524288) Batches: 8 (originally 1) Memory Usage: 16,960kB
13. 554.875 1,197.231 ↓ 2.9 524,237 3 / 3

Parallel Hash Join (cost=44,679.99..241,263.45 rows=183,688 width=31) (actual time=833.237..1,197.231 rows=524,237 loops=3)

  • Hash Cond: (pvit.productvariantid = productskudetails.productvariantid)
14. 396.057 396.057 ↑ 1.2 1,082,369 3 / 3

Parallel Seq Scan on productvariantinfotable pvit (cost=0.00..177,175.32 rows=1,328,053 width=8) (actual time=0.010..396.057 rows=1,082,369 loops=3)

  • Filter: (productvariantquantity = 0)
  • Rows Removed by Filter: 4,405,161
15. 160.602 246.299 ↑ 1.3 753,902 3 / 3

Parallel Hash (cost=27,304.55..27,304.55 rows=946,355 width=23) (actual time=246.299..246.299 rows=753,902 loops=3)

  • Buckets: 524,288 Batches: 8 Memory Usage: 20,416kB
16. 85.697 85.697 ↑ 1.3 753,902 3 / 3

Parallel Seq Scan on productskudetails (cost=0.00..27,304.55 rows=946,355 width=23) (actual time=0.006..85.697 rows=753,902 loops=3)

17. 85.728 85.728 ↑ 1.0 1 85,728 / 3

Index Scan using producttable_pkey on producttable (cost=0.57..7.81 rows=1 width=52) (actual time=0.003..0.003 rows=1 loops=85,728)

  • Index Cond: (productid = cartitemtable.cartproductid)
  • Filter: (productstatus <> 'deleted'::text)
  • Rows Removed by Filter: 0
18. 124.438 171.739 ↑ 1.3 439,377 3 / 3

Parallel Hash (cost=15,959.73..15,959.73 rows=554,873 width=16) (actual time=171.739..171.739 rows=439,377 loops=3)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 78,304kB
19. 47.301 47.301 ↑ 1.3 439,377 3 / 3

Parallel Seq Scan on wholesalecatalogueproducttable (cost=0.00..15,959.73 rows=554,873 width=16) (actual time=0.018..47.301 rows=439,377 loops=3)

20. 17.139 89.742 ↑ 1.6 67,100 3 / 3

Parallel Hash (cost=31,828.37..31,828.37 rows=105,568 width=16) (actual time=89.742..89.742 rows=67,100 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 11,552kB
21. 72.603 72.603 ↑ 1.6 67,100 3 / 3

Parallel Seq Scan on wholesalecataloguetable (cost=0.00..31,828.37 rows=105,568 width=16) (actual time=0.024..72.603 rows=67,100 loops=3)

  • Filter: (cataloguestatus <> 'deleted'::text)
  • Rows Removed by Filter: 19,029
Planning time : 1.616 ms
Execution time : 3,616.206 ms