[Solved] How to find rank of order number by same start
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

How to find rank of order number by same start

Database type:

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

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX dok_idx_doktyyp_tasudok ON "dok" ("doktyyp","tasudok");
The optimized query:
WITH koik AS (SELECT
        rank() OVER (ORDER 
    BY
        dok.tasudok),
        dok.tasudok 
    FROM
        dok 
    WHERE
        dok.doktyyp = 'T' 
        AND dok.tasudok LIKE '91000465663%') SELECT
        koik.rank 
    FROM
        koik 
    WHERE
        koik.tasudok = '91000465663-1'

Related Articles



* original question posted on StackOverflow here.