pastebin schema pasted by wasamasa on Thu Oct 29 13:21:15 2020

create table pastes(hash text, author text, title text, time float, paste text);

pastebin fill script pasted by wasamasa on Thu Oct 29 13:21:33 2020

(import scheme)
(import (chicken base))
(import (chicken random))
(import (chicken process-context))
(import simple-sha1)
(import sql-de-lite)

(define (main db-path)
  (call-with-database db-path
    (lambda (db)
      (with-transaction db
        (lambda ()
          (let loop ((i 0))
            (when (< i 15000)
              (let ((hash (string->sha1sum (number->string i)))
                    (author "wasa")
                    (title "test")
                    (paste "123")
                    (pastes (add1 (pseudo-random-integer 3))))
                (let loop ((j 0))
                  (when (< j pastes)
                    (exec (sql db "INSERT INTO pastes VALUES(?, ?, ?, ?, ?)")
                          hash author title (+ i j) paste)
                    (loop (add1 j))))
                (loop (+ i pastes))))))))))

(apply main (command-line-arguments))

explain (query plan) added by wasamasa on Thu Oct 29 13:26:24 2020

SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> explain select * from pastes p where time = (select min(time) from pastes p2 where p2.hash=p.hash) order by time desc limit 10;
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     57    0                    0   Start at 57  
1     OpenEphemeral  2     7     0     k(1,-B)        0   nColumn=7    
2     Integer        10    1     0                    0   r[1]=10; LIMIT counter
3     OpenRead       0     2     0     5              0   root=2 iDb=0; pastes
4     Rewind         0     48    0                    0                
5       Column         0     3     2                    0   r[2]=pastes.time
6       RealAffinity   2     0     0                    0                
7       Null           0     4     4                    0   r[4..4]=NULL; Init subquery result
8       Integer        1     5     0                    0   r[5]=1; LIMIT counter
9       Null           0     6     7                    0   r[6..7]=NULL 
10      OpenRead       1     2     0     4              0   root=2 iDb=0; pastes
11      Once           0     21    0                    0                
12      OpenAutoindex  3     3     0     k(3,B,,)       0   nColumn=3; for pastes
13      Rewind         1     21    0                    0                
14        Column         1     0     9                    0   r[9]=pastes.hash
15        Column         1     3     10                   0   r[10]=pastes.time
16        Noop           10    0     0                    0                
17        Rowid          1     11    0                    0   r[11]=rowid  
18        MakeRecord     9     3     8                    0   r[8]=mkrec(r[9..11])
19        IdxInsert      3     8     0                    16  key=r[8]     
20      Next           1     14    0                    3                
21      Column         0     0     12                   0   r[12]=pastes.hash
22      IsNull         12    30    0                    0   if r[12]==NULL goto 30
23      SeekGE         3     30    12    1              0   key=r[12]    
24        IdxGT          3     30    12    1              0   key=r[12]    
25        Column         3     1     8                    0   r[8]=pastes.time
26        RealAffinity   8     0     0                    0                
27        CollSeq        0     0     0     BINARY-8       0                
28        AggStep        0     8     6     min(1)         1   accum=r[6] step(r[8])
29      Next           3     24    0                    0                
30      AggFinal       6     1     0     min(1)         0   accum=r[6] N=1
31      Copy           6     4     0                    0   r[4]=r[6]    
32      DecrJumpZero   5     33    0                    0   if (--r[5])==0 goto 33
33      Ne             4     47    2     BINARY-8       85  if r[2]!=r[4] goto 47
34      Column         0     3     13                   0   r[13]=pastes.time
35      RealAffinity   13    0     0                    0                
36      Sequence       2     14    0                    0   r[14]=cursor[2].ctr++
37      IfNotZero      1     41    0                    0   if r[1]!=0 then r[1]--, goto 41
38      Last           2     0     0                    0                
39      IdxLE          2     47    13    1              0   key=r[13]    
40      Delete         2     0     0                    0                
41      Column         0     0     15                   0   r[15]=pastes.hash
42      Column         0     1     16                   0   r[16]=pastes.author
43      Column         0     2     17                   0   r[17]=pastes.title
44      Column         0     4     18                   0   r[18]=pastes.paste
45      MakeRecord     13    6     20                   0   r[20]=mkrec(r[13..18])
46      IdxInsert      2     20    13    6              0   key=r[20]    
47    Next           0     5     0                    1                
48    Sort           2     56    0                    0                
49      Column         2     5     19                   0   r[19]=paste  
50      Column         2     0     18                   0   r[18]=time   
51      Column         2     4     17                   0   r[17]=title  
52      Column         2     3     16                   0   r[16]=author 
53      Column         2     2     15                   0   r[15]=hash   
54      ResultRow      15    5     0                    0   output=r[15..19]
55    Next           2     49    0                    0                
56    Halt           0     0     0                    0                
57    Transaction    0     0     2     0              1   usesStmtJournal=0
58    Goto           0     1     0                    0                
sqlite> explain query plan select * from pastes p where time = (select min(time) from pastes p2 where p2.hash=p.hash) order by time desc limit 10;
QUERY PLAN
|--SCAN TABLE pastes AS p
|--CORRELATED SCALAR SUBQUERY 1
|  `--SEARCH TABLE pastes AS p2 USING AUTOMATIC COVERING INDEX (hash=?)
`--USE TEMP B-TREE FOR ORDER BY
sqlite> explain select hash, author, title, min(time), paste from pastes group by hash order by time desc limit 10; 
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     74    0                    0   Start at 74  
1     OpenEphemeral  1     7     0     k(1,-B)        0   nColumn=7    
2     Integer        10    1     0                    0   r[1]=10; LIMIT counter
3     Integer        20    2     0                    0   r[2]=20      
4     MustBeInt      2     0     0                    0   OFFSET counter
5     OffsetLimit    1     3     2                    0   if r[1]>0 then r[3]=r[1]+max(0,r[2]) else r[3]=(-1); LIMIT+OFFSET
6     SorterOpen     2     5     0     k(1,-B)        0                
7     Integer        0     11    0                    0   r[11]=0; clear abort flag
8     Null           0     14    14                   0   r[14..14]=NULL
9     Gosub          13    61    0                    0                
10    OpenRead       0     2     0     5              0   root=2 iDb=0; pastes
11    Rewind         0     21    0                    0                
12      Column         0     0     16                   0   r[16]=pastes.hash
13      Column         0     1     17                   0   r[17]=pastes.author
14      Column         0     2     18                   0   r[18]=pastes.title
15      Column         0     4     19                   0   r[19]=pastes.paste
16      Column         0     3     20                   0   r[20]=pastes.time
17      RealAffinity   20    0     0                    0                
18      MakeRecord     16    5     21                   0   r[21]=mkrec(r[16..20])
19      SorterInsert   2     21    0                    0   key=r[21]    
20    Next           0     12    0                    1                
21    OpenPseudo     3     21    5                    0   5 columns in r[21]
22    SorterSort     2     64    0                    0   GROUP BY sort
23      SorterData     2     21    3                    0   r[21]=data   
24      Column         3     0     15                   0   r[15]=       
25      Compare        14    15    1     k(1,-B)        0   r[14] <-> r[15]
26      Jump           27    31    27                   0                
27      Move           15    14    1                    0   r[14]=r[15]  
28      Gosub          12    48    0                    0   output one row
29      IfPos          11    64    0                    0   if r[11]>0 then r[11]-=0, goto 64; check abort flag
30      Gosub          13    61    0                    0   reset accumulator
31      Column         3     4     22                   0   r[22]=pastes.time
32      RealAffinity   22    0     0                    0                
33      CollSeq        23    0     0     BINARY-8       0                
34      AggStep        0     22    7     min(1)         1   accum=r[7] step(r[22])
35      If             23    42    0                    0                
36      Column         3     0     4                    0   r[4]=pastes.hash
37      Column         3     1     5                    0   r[5]=pastes.author
38      Column         3     2     6                    0   r[6]=pastes.title
39      Column         3     3     8                    0   r[8]=pastes.paste
40      Column         3     4     9                    0   r[9]=pastes.time
41      RealAffinity   9     0     0                    0                
42      Integer        1     10    0                    0   r[10]=1; indicate data in accumulator
43    SorterNext     2     23    0                    0                
44    Gosub          12    48    0                    0   output final row
45    Goto           0     64    0                    0                
46    Integer        1     11    0                    0   r[11]=1; set abort flag
47    Return         12    0     0                    0                
48    IfPos          10    50    0                    0   if r[10]>0 then r[10]-=0, goto 50; Groupby result generator entry point
49    Return         12    0     0                    0                
50    AggFinal       7     1     0     min(1)         0   accum=r[7] N=1
51    Copy           9     24    0                    0   r[24]=r[9]   
52    Sequence       1     25    0                    0   r[25]=cursor[1].ctr++
53    IfNotZero      3     57    0                    0   if r[3]!=0 then r[3]--, goto 57
54    Last           1     0     0                    0                
55    IdxLE          1     60    24    1              0   key=r[24]    
56    Delete         1     0     0                    0                
57    Copy           4     26    4                    0   r[26..30]=r[4..8]
58    MakeRecord     24    7     31                   0   r[31]=mkrec(r[24..30])
59    IdxInsert      1     31    24    7              0   key=r[31]    
60    Return         12    0     0                    0   end groupby result generator
61    Null           0     4     9                    0   r[4..9]=NULL 
62    Integer        0     10    0                    0   r[10]=0; indicate accumulator empty
63    Return         13    0     0                    0                
64    Sort           1     73    0                    0                
65      IfPos          2     72    1                    0   if r[2]>0 then r[2]-=1, goto 72; OFFSET
66      Column         1     6     30                   0   r[30]=paste  
67      Column         1     5     29                   0   r[29]=min(time)
68      Column         1     4     28                   0   r[28]=title  
69      Column         1     3     27                   0   r[27]=author 
70      Column         1     2     26                   0   r[26]=hash   
71      ResultRow      26    5     0                    0   output=r[26..30]
72    Next           1     65    0                    0                
73    Halt           0     0     0                    0                
74    Transaction    0     0     2     0              1   usesStmtJournal=0
75    Goto           0     1     0                    0                
sqlite> explain query plan select hash, author, title, min(time), paste from pastes group by hash order by time desc limit 10;
QUERY PLAN
|--SCAN TABLE pastes
|--USE TEMP B-TREE FOR GROUP BY
`--USE TEMP B-TREE FOR ORDER BY
sqlite>