Improved data display

1 files changed, 0 insertions, 183 deletions

diff --git a/content/posts/postgres-aggregates-correction/queries.sql b/content/posts/postgres-aggregates-correction/queries.sql
deleted file mode 100644
index ce8bb55..0000000
--- a/content/posts/postgres-aggregates-correction/queries.sql
+++ /dev/null
@@ -1,183 +0,0 @@
--- ============================================================
--- postgres-aggregates-correction: benchmark queries
--- Run each block separately and save the EXPLAIN output.
--- ============================================================
-
--- -------------------------------------------------------
--- 1. Subquery — count(id) — no filter  [ORIGINAL, baseline]
--- -------------------------------------------------------
-EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
-SELECT
-  products.id,
-  products.name,
-  (SELECT COUNT(id) FROM reviews WHERE product_id = products.id) AS num_reviews,
-  (SELECT COUNT(id) FROM orders  WHERE product_id = products.id) AS num_orders
-FROM products;
-
-
--- -------------------------------------------------------
--- 2. Subquery — count(*) — no filter   [THE FIX]
--- -------------------------------------------------------
-EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
-SELECT
-  products.id,
-  products.name,
-  (SELECT COUNT(*) FROM reviews WHERE product_id = products.id) AS num_reviews,
-  (SELECT COUNT(*) FROM orders  WHERE product_id = products.id) AS num_orders
-FROM products;
-
-
--- -------------------------------------------------------
--- 3. CTE (merging) — count(id) — no filter
--- -------------------------------------------------------
-EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
-WITH product_reviews AS (
-  SELECT product_id, COUNT(id) AS num_reviews
-  FROM reviews
-  GROUP BY product_id
-),
-product_orders AS (
-  SELECT product_id, COUNT(id) AS num_orders
-  FROM orders
-  GROUP BY product_id
-)
-SELECT
-  products.id,
-  products.name,
-  COALESCE(product_reviews.num_reviews, 0) AS num_reviews,
-  COALESCE(product_orders.num_orders,   0) AS num_orders
-FROM products
-LEFT JOIN product_reviews ON products.id = product_reviews.product_id
-LEFT JOIN product_orders  ON products.id = product_orders.product_id;
-
-
--- -------------------------------------------------------
--- 4. Subquery — count(*) — root filter (price > 900)
--- -------------------------------------------------------
-EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
-SELECT
-  products.id,
-  products.name,
-  (SELECT COUNT(*) FROM reviews WHERE product_id = products.id) AS num_reviews,
-  (SELECT COUNT(*) FROM orders  WHERE product_id = products.id) AS num_orders
-FROM products
-WHERE products.price > 900;
-
-
--- -------------------------------------------------------
--- 5. CTE (merging) — count(id) — root filter (price > 900)
--- -------------------------------------------------------
-EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
-WITH product_reviews AS (
-  SELECT product_id, COUNT(id) AS num_reviews
-  FROM reviews
-  GROUP BY product_id
-),
-product_orders AS (
-  SELECT product_id, COUNT(id) AS num_orders
-  FROM orders
-  GROUP BY product_id
-)
-SELECT
-  products.id,
-  products.name,
-  COALESCE(product_reviews.num_reviews, 0) AS num_reviews,
-  COALESCE(product_orders.num_orders,   0) AS num_orders
-FROM products
-LEFT JOIN product_reviews ON products.id = product_reviews.product_id
-LEFT JOIN product_orders  ON products.id = product_orders.product_id
-WHERE products.price > 900;
-
-
--- -------------------------------------------------------
--- 6. Subquery — count(*) — leaf filter (customer_name ilike '%0')
--- -------------------------------------------------------
-EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
-SELECT
-  products.id,
-  products.name,
-  (SELECT COUNT(*) FROM reviews WHERE product_id = products.id) AS num_reviews,
-  (SELECT COUNT(*) FROM orders  WHERE product_id = products.id AND customer_name ILIKE '%0') AS num_orders
-FROM products;
-
-
--- -------------------------------------------------------
--- 7. CTE (merging) — count(id) — leaf filter (customer_name ilike '%0')
--- -------------------------------------------------------
-EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
-WITH product_reviews AS (
-  SELECT product_id, COUNT(id) AS num_reviews
-  FROM reviews
-  GROUP BY product_id
-),
-product_orders AS (
-  SELECT product_id, COUNT(id) AS num_orders
-  FROM orders
-  WHERE customer_name ILIKE '%0'
-  GROUP BY product_id
-)
-SELECT
-  products.id,
-  products.name,
-  COALESCE(product_reviews.num_reviews, 0) AS num_reviews,
-  COALESCE(product_orders.num_orders,   0) AS num_orders
-FROM products
-LEFT JOIN product_reviews ON products.id = product_reviews.product_id
-LEFT JOIN product_orders  ON products.id = product_orders.product_id;
-
-
--- ============================================================
--- DISK-THROTTLED PASS (run with Docker: shared_buffers=16MB,
--- blkio capped at 125MB/s read — see docker-compose snippet)
--- Run DISCARD ALL between queries to flush caches.
--- ============================================================
-
--- -------------------------------------------------------
--- D1. Subquery — count(id) — no filter  [Bitmap Heap Scan expected]
--- -------------------------------------------------------
-DISCARD ALL;
-EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
-SELECT
-  products.id,
-  products.name,
-  (SELECT COUNT(id) FROM reviews WHERE product_id = products.id) AS num_reviews,
-  (SELECT COUNT(id) FROM orders  WHERE product_id = products.id) AS num_orders
-FROM products;
-
-
--- -------------------------------------------------------
--- D2. Subquery — count(*) — no filter  [Index Only Scan expected]
--- -------------------------------------------------------
-DISCARD ALL;
-EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
-SELECT
-  products.id,
-  products.name,
-  (SELECT COUNT(*) FROM reviews WHERE product_id = products.id) AS num_reviews,
-  (SELECT COUNT(*) FROM orders  WHERE product_id = products.id) AS num_orders
-FROM products;
-
-
--- -------------------------------------------------------
--- D3. CTE (merging) — count(id) — no filter
--- -------------------------------------------------------
-DISCARD ALL;
-EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
-WITH product_reviews AS (
-  SELECT product_id, COUNT(id) AS num_reviews
-  FROM reviews
-  GROUP BY product_id
-),
-product_orders AS (
-  SELECT product_id, COUNT(id) AS num_orders
-  FROM orders
-  GROUP BY product_id
-)
-SELECT
-  products.id,
-  products.name,
-  COALESCE(product_reviews.num_reviews, 0) AS num_reviews,
-  COALESCE(product_orders.num_orders,   0) AS num_orders
-FROM products
-LEFT JOIN product_reviews ON products.id = product_reviews.product_id
-LEFT JOIN product_orders  ON products.id = product_orders.product_id;