0

I have the following table:

CREATE TABLE users(
  id text PRIMARY KEY, -- actually a uuid
  organization_id text NOT NULL,
  lname text NOT NULL,
  fname text NOT NULL
  -- other irrelevant fields
);

CREATE INDEX idx_users_fullname ON users(
  organization_id,
  NULLIF(lname, ''),
  NULLIF(fname, ''),
  id
);

While implementing pagination, I found LIMIT ... OFFSET to be rather slow, so I decided to use a different method. The issue is, I want empty strings to be returned last. ORDER BY lets you choose how you want to sort NULL values and there's NULLIF(), which is handy.

I can make use of that index using the following query:

SELECT u.id                                                                 
FROM users u
WHERE u.organization_id = '00000000-0000-0000-0000-000000000000'
AND (NULLIF(u.lname, ''),
     NULLIF(u.fname, ''),
     u.id) >
  (SELECT NULLIF(lname, ''),
          NULLIF(fname, ''),
          id
  FROM users
  WHERE id = '00000000-0000-0000-0000-000000000001')
ORDER BY NULLIF(u.lname, ''),
         NULLIF(u.fname, ''),
         u.id
LIMIT 15;

Which generates the following plan:

 Limit  (cost=8.72..20.93 rows=15 width=112) (actual time=0.047..0.077 rows=15 loops=1)
   InitPlan 1 (returns $0,$1,$2,$3)
     ->  Index Scan using users_pkey on users  (cost=0.29..8.31 rows=1 width=112) (actual time=0.015..0.015 rows=1 loops=1)
           Index Cond: (id = '00000000-0000-0000-0000-000000000001'::uuid)
   ->  Index Scan using test_index on users u  (cost=0.41..5769.08 rows=7087 width=112) (actual time=0.046..0.073 rows=15 loops=1)
         Index Cond: ((organization_id = '00000000-0000-0000-0000-000000000000'::uuid) AND (ROW(NULLIF((lname)::text, ''::text), NULLIF((fname)::text, ''::text), id) > ROW($0, $1, $2)))
 Planning time: 0.392 ms
 Execution time: 0.124 ms

But the issue is, x > NULL or NULL > x is always NULL. So it yields incorrect results.

Currently I resorted to doing this:

SELECT u.id
FROM users u
WHERE u.organization_id = '00000000-0000-0000-0000-000000000000'
AND (NULLIF(u.lname, '') IS NULL,
     u.lname,
     NULLIF(u.fname, '') IS NULL,
     u.fname,
     u.id) >
  (SELECT NULLIF(lname, '') IS NULL,
          lname,
          NULLIF(fname, '') IS NULL,
          fname,
          id
  FROM users WHERE id = '00000000-0000-0000-0000-000000000001')
ORDER BY NULLIF(u.lname, ''),
         NULLIF(u.fname, ''),
         u.id
LIMIT 15;

CREATE INDEX idx_users_fullname_2 ON users(
  organization_id,
  (NULLIF(lname, '') IS NULL),
  lname,
  (NULLIF(fname, '') IS NULL),
  fname,
  id
);

Which generates the following plan:

 Limit  (cost=17.04..17.06 rows=8 width=80) (actual time=0.032..0.032 rows=0 loops=1)
   InitPlan 1 (returns $0,$1,$2,$3,$4)
     ->  Index Scan using users_pkey on users  (cost=0.29..8.31 rows=1 width=47) (actual time=0.017..0.017 rows=0 loops=1)
           Index Cond: (id = '00000000-0000-0000-0000-000000000001'::uuid)
   ->  Sort  (cost=8.73..8.75 rows=8 width=80) (actual time=0.032..0.032 rows=0 loops=1)
         Sort Key: (NULLIF((u.lname)::text, ''::text)), (NULLIF((u.fname)::text, ''::text)), u.id
         Sort Method: quicksort  Memory: 25kB
         ->  Index Only Scan using idx_users_fullname_2 on users u  (cost=0.41..8.61 rows=8 width=80) (actual time=0.022..0.022 rows=0 loops=1)
               Index Cond: ((organization_id = '00000000-0000-0000-0000-000000000000'::uuid) AND (ROW(((NULLIF((lname)::text, ''::text) IS NULL)), lname, ((NULLIF((fname)::text, ''::text) IS
 NULL)), fname, id) > ROW($0, ($1)::text, $2, ($3)::text, $4)))
               Heap Fetches: 0
 Planning time: 0.474 ms
 Execution time: 0.098 ms

Having 2 indices for the same purpose feels rather suboptimal to me. But this query won't use the first one even if there's no second one.

Is there a way to achieve what I want while using a single index?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.