Partial و Covering indexes: indexes أصغر وأسرع

-- 10% بس pending، والـ dashboard بيسأل عليهم بس
CREATE INDEX orders_status_full ON orders (status, created_at);
CREATE INDEX orders_pending ON orders (created_at) WHERE status = 'pending';

SELECT indexrelname AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes WHERE relname = 'orders' AND indexrelname <> 'orders_pkey';

EXPLAIN (COSTS OFF) SELECT id FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 10;
-- الـ partial ما ينفعش لسؤال تاني
EXPLAIN (COSTS OFF) SELECT id FROM orders WHERE status = 'paid' ORDER BY created_at LIMIT 10;

$ psql -q -f seed.sql -f queries.sql
       index        |  size   
--------------------+---------
 orders_status_full | 6184 kB
 orders_pending     | 416 kB
(2 rows)

                   QUERY PLAN                    
-------------------------------------------------
 Limit
   ->  Index Scan using orders_pending on orders
(2 rows)

                     QUERY PLAN                      
-----------------------------------------------------
 Limit
   ->  Index Scan using orders_status_full on orders
         Index Cond: (status = 'paid'::text)
(3 rows)

-- unique بشرط: إيميل واحد بين الحسابات اللي ما اتمسحتش بس (soft delete)
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL,
  deleted_at timestamptz
);
CREATE UNIQUE INDEX accounts_email_live ON accounts (lower(email)) WHERE deleted_at IS NULL;

INSERT INTO accounts (email) VALUES ('[email protected]');
INSERT INTO accounts (email) VALUES ('[email protected]');            -- مرفوض
UPDATE accounts SET deleted_at = now() WHERE email = '[email protected]';
INSERT INTO accounts (email) VALUES ('[email protected]');            -- مسموح بعد الحذف
SELECT id, email, deleted_at IS NOT NULL AS deleted FROM accounts ORDER BY id;

$ psql -q -f seed.sql -f queries.sql
psql:queries.sql:11: ERROR:  duplicate key value violates unique constraint "accounts_email_live"
DETAIL:  Key (lower(email))=([email protected]) already exists.
 id |   email    | deleted 
----+------------+---------
  1 | [email protected] | t
  3 | [email protected] | f
(2 rows)

-- covering index: الـ index نفسه فيه كل اللي الـ query محتاجاه
CREATE INDEX orders_user ON orders (user_id);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT user_id, sum(total_cents) FROM orders WHERE user_id BETWEEN 100 AND 110 GROUP BY user_id;

CREATE INDEX orders_user_cover ON orders (user_id) INCLUDE (total_cents);
VACUUM orders; -- بيحدّث الـ visibility map
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT user_id, sum(total_cents) FROM orders WHERE user_id BETWEEN 100 AND 110 GROUP BY user_id;

$ psql -q -f seed.sql -f queries.sql
                               QUERY PLAN                               
------------------------------------------------------------------------
 HashAggregate (actual rows=11 loops=1)
   Group Key: user_id
   Batches: 1  Memory Usage: 37kB
   ->  Bitmap Heap Scan on orders (actual rows=440 loops=1)
         Recheck Cond: ((user_id >= 100) AND (user_id <= 110))
         Heap Blocks: exact=40
         ->  Bitmap Index Scan on orders_user (actual rows=440 loops=1)
               Index Cond: ((user_id >= 100) AND (user_id <= 110))
(8 rows)

                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 GroupAggregate (actual rows=11 loops=1)
   Group Key: user_id
   ->  Index Only Scan using orders_user_cover on orders (actual rows=440 loops=1)
         Index Cond: ((user_id >= 100) AND (user_id <= 110))
         Heap Fetches: 0
(5 rows)

DROP TABLE IF EXISTS jobs;
CREATE TABLE jobs (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  queue      text NOT NULL,
  payload    jsonb NOT NULL,
  run_at     timestamptz NOT NULL,
  done_at    timestamptz,
  dedupe_key text
);
-- 100 ألف job، كلهم خلصوا ما عدا 200
INSERT INTO jobs (queue, payload, run_at, done_at)
SELECT CASE WHEN i % 2 = 0 THEN 'email' ELSE 'video' END, '{}',
       timestamptz '2026-01-01' + i * interval '1 minute',
       CASE WHEN i > 99800 THEN NULL ELSE timestamptz '2026-01-01' + i * interval '1 minute' END
FROM generate_series(1, 100000) AS i;


CREATE INDEX jobs_pending ON jobs (queue, run_at) WHERE done_at IS NULL;
CREATE UNIQUE INDEX jobs_dedupe ON jobs (dedupe_key) WHERE done_at IS NULL;
ANALYZE jobs;

EXPLAIN (COSTS OFF) SELECT id FROM jobs WHERE done_at IS NULL AND queue = 'email' ORDER BY run_at LIMIT 5;

INSERT INTO jobs (queue, payload, run_at, dedupe_key) VALUES ('email', '{}', now(), 'welcome:7');
INSERT INTO jobs (queue, payload, run_at, dedupe_key) VALUES ('email', '{}', now(), 'welcome:7');
UPDATE jobs SET done_at = now() WHERE dedupe_key = 'welcome:7';
INSERT INTO jobs (queue, payload, run_at, dedupe_key) VALUES ('email', '{}', now(), 'welcome:7');

SELECT pg_size_pretty(pg_relation_size('jobs_pending')) AS pending_index,
       pg_size_pretty(pg_relation_size('jobs')) AS table_size;

$ psql -q -f seed.sql -f queries.sql
                 QUERY PLAN                  
---------------------------------------------
 Limit
   ->  Index Scan using jobs_pending on jobs
         Index Cond: (queue = 'email'::text)
(3 rows)

psql:queries.sql:25: ERROR:  duplicate key value violates unique constraint "jobs_dedupe"
DETAIL:  Key (dedupe_key)=(welcome:7) already exists.
 pending_index | table_size 
---------------+------------
 16 kB         | 6672 kB
(1 row)