POSTGRES QUERY METRICS

querymetrics.txt

Products Table Query: 
SELECT * FROM products where productid = 879569;
(1 row)
Time: 1.960 ms


Product Table Query 2: 
SELECT * FROM products where productid > 9999000 AND price > 200.00;
(303 rows)
Time: 4.214 ms


Product Table Query 3:
SELECT * FROM products where price > 200 AND themename = 'NINJAGO' 
AND featured = 'Retiring soon' AND chokinghazard = 't' AND rating > 4 AND productlimit > 5;
(1317 rows)
First Query: Time: 7699.409 ms
Second Query: Time : 5395.425 ms
Third Query: Time: 600.796 ms
Subsequent Queries capped at 585 ms to 586 ms


-----------------------------------------------------------------------------------------------------------


Availabilities Table Query 1: 
SELECT * FROM availabilities WHERE productId = 224 AND storeId = 221;
 availabilityid | productid | storeid | inventory 
----------------+-----------+---------+-----------
           2231 |       224 |     221 |         4
(1 row)
Time: 1.199 ms


Availabilities Table Query 2:
SELECT * FROM availabilities WHERE productId > 240 AND productId < 270 AND storeId > 240 AND storeId < 270;
(281 rows)
Time: 3.356 ms


Availabilities Table Query 3:
SELECT * FROM stores INNER JOIN availabilities ON stores.storeId = availabilities.storeId 
WHERE stores.storeId = 34 AND availabilities.productId = 182032;
 storeid |    storename    | storezip | availabilityid | productid | storeid | inventory 
---------+-----------------+----------+----------------+-----------+---------+-----------
      34 | The LEGO Outlet |    52056 |        1820314 |    182032 |      34 |         9
(1 row)
First Query: Time: 2.292 ms
Second Query: Time: 0.413 ms
Subsequent Queries: 0.350 ms to 0.450 ms


Availabilities Table Query 4: 
SELECT * FROM stores INNER JOIN availabilities ON stores.storeId = availabilities.storeId 
WHERE stores.storeId = 34 AND availabilities.productId > 182032 AND availabilities.productId < 182220;
 storeid |    storename    | storezip | availabilityid | productid | storeid | inventory 
---------+-----------------+----------+----------------+-----------+---------+-----------
      34 | The LEGO Outlet |    52056 |        1820324 |    182033 |      34 |         9
      34 | The LEGO Outlet |    52056 |        1820334 |    182034 |      34 |         5
      34 | The LEGO Outlet |    52056 |        1820344 |    182035 |      34 |         7
      34 | The LEGO Outlet |    52056 |        1820354 |    182036 |      34 |         9
      34 | The LEGO Outlet |    52056 |        1820364 |    182037 |      34 |         9
      34 | The LEGO Outlet |    52056 |        1820374 |    182038 |      34 |         5
      34 | The LEGO Outlet |    52056 |        1820384 |    182039 |      34 |         7
      34 | The LEGO Outlet |    52056 |        1820394 |    182040 |      34 |         9
(8 rows)
First Query: Time: 1.733 ms
Second Query: Time: 0.543 ms


Availabilities Table Query 5: 
SELECT COUNT(*) FROM availabilities where inventory > 3;
82500000
(1 row)
Time: 15957.312 ms (00:15.957)


Availability Table Query 6: 
SELECT * FROM stores INNER JOIN availabilities ON stores.storeId = availabilities.storeId INNER JOIN products ON products.productId = availabilities.productId WHERE stores.storeId = 34 AND availabilities.productId > 182032 AND availabilities.productId < 182050;
(8 rows)
First Query: Time: 3.975 ms
Second Query: Time: 0.763 ms