How to find rank of order number by same start

Question

Dok table contains order numbers :

create table dok ( doktyyp char(1),
     tasudok char(25) );
CREATE INDEX dok_tasudok_idx ON dok (tasudok);
CREATE UNIQUE INDEX dok_tasudok_unique_idx ON dok (doktyyp,tasudok)
WHERE doktyyp IN ( 'T', 'U') ;

Order numbers contain same start part and different suffices like

91000465663        
91000465663-1
91000465663-2
91000465663-T
91000465663-T-1

How to create query which returns order sequence number by given prefix. For doktyyp column value is always constant 'T'.

For numbers below, results should be

91000465663        returns 1
91000465663-1      returns 2
91000465663-2      returns 3
91000465663-T      returns 4
91000465663-T-1    returns 5

Query

with koik as (
select rank() over (order by tasudok), tasudok
from dok 
where doktyyp='T' and tasudok  like '91000465663%'
)

select rank 
from koik 
where tasudok='91000465663-1'

Seems to work properly but looks too long for this simple task. How to create shorter and better query ?

Explain output:

"Subquery Scan on koik  (cost=685.04..685.07 rows=1 width=8)"
"  Filter: (koik.tasudok = '91000465663-1'::bpchar)"
"  ->  WindowAgg  (cost=685.04..685.06 rows=1 width=34)"
"        ->  Sort  (cost=685.04..685.05 rows=1 width=26)"
"              Sort Key: dok.tasudok"
"              ->  Bitmap Heap Scan on dok  (cost=23.55..685.03 rows=1 width=26)"
"                    Recheck Cond: (doktyyp = 'T'::bpchar)"
"                    Filter: (tasudok ~~ '91000465663%'::text)"
"                    ->  Bitmap Index Scan on dok_tasudok_unique_idx  (cost=0.00..23.55 rows=437 width=0)"
"                          Index Cond: (doktyyp = 'T'::bpchar)"

Using postgres 11

is there an index on column tasudok? What does EXPLAIN say? – Neville Kuyt Sep 24 '21 at 14:33 — Neville Kuyt, Sep 24 '21 at 14:33
I updated question and added indexes and explain output – Andrus Sep 24 '21 at 14:47 — Andrus, Sep 24 '21 at 14:47

score 1 · Accepted Answer · answered Sep 24 '21 at 16:03

If I got your use case correctly, the query will always consider a fixed prefix of the first 11 characters on tasudok, so in order to speed things up you can tell the index to sort tasudok already truncated to the first 11 characters (partial index), so that you don't need to use like.

CREATE INDEX dok_doktyyp_tasudok_idx ON dok (doktyyp, SUBSTRING(tasudok FROM 1 FOR 11));

And the query should look like

WITH koik AS (
  SELECT RANK() OVER (ORDER BY tasudok), tasudok
  FROM dok 
  WHERE doktyyp='T' AND SUBSTRING(tasudok FROM 1 FOR 11) = '91000465663'
)
SELECT tasudok,rank FROM koik 
WHERE tasudok = '91000465663-1';

Of course, this would not make sense if the prefix size is variable.

Demo: db<>fiddle

How to find rank of order number by same start

1 Answers1