| 11 Jan 2022 |
 @grahamc:nixos.org | cool | 00:58:15 |
| @grahamc:nixos.org | yeah, I think patch adding LIKE was a bit of a mistake, using a btree with a proper comparison op should make it a lot faster | 00:58:35 |
 @ius:nltrix.net | Looks like that's not really compatible with LIKE | 00:58:40 |
| @grahamc:nixos.org | it does work with LIKE, but not with several hundred million builds | 00:58:52 |
| @ius:nltrix.net | Because, if I understand psql docs correctly, it'll result in a seq scan | 00:59:07 |
| @grahamc:nixos.org | exactly | 00:59:16 |
| @grahamc:nixos.org | but again a LIKE is actually fine if it is a suffix only like, ie: LIKE "/nix/store/xxxx%" | 00:59:37 |
| @grahamc:nixos.org | luckily nix store paths are very predictable, so this could be detected | 00:59:55 |
| @ius:nltrix.net | Yeah, but you'd want to support searching for yah9mf7x2ycalb28gg9ximh49g0akkrn as well ideally | 01:00:05 |
| @grahamc:nixos.org | yeah, exactly | 01:00:21 |
| @grahamc:nixos.org | so we could identify it contains a Nix store path's hash and prefix it with /nix/store/ and append a % | 01:00:34 |
| @ius:nltrix.net | So which made me wonder, wouldn't it help to extract the hash using a regex, and match using equals, so you can use the HASH index? | 01:00:47 |
| @grahamc:nixos.org | my experiments on using a btree index and a like can produce good results is ~10-20ms | 01:01:11 |
| @ius:nltrix.net | Then you don't even need a LIKE. Unless there's a great reason for LIKE's on the full path. | 01:01:12 |
| @grahamc:nixos.org | a hash index might be good | 01:01:32 |
| @ius:nltrix.net | Right | 01:03:10 |
| @ius:nltrix.net | Only drawback of scrapping the LIKE is that you can't search the path for a name | 01:03:42 |
| @grahamc:nixos.org | right, though the name is part of the nixname field already | 01:06:07 |
| @ius:nltrix.net | Yeah, was about to suggest that as well | 01:06:35 |
| @grahamc:nixos.org | in general the search could be much faster and better | 01:06:36 |
| @grahamc:nixos.org | and without even a massive amount of work | 01:06:48 |
| @ius:nltrix.net | I wouldn't mind spending a few hours, but oh boy perl. | 01:07:01 |
| @grahamc:nixos.org | :) | 01:07:28 |
| @grahamc:nixos.org | I like to see PRs come with a lot of tests, which typically pushes the Perl from yucky Perl to tolerable | 01:07:59 |
| @grahamc:nixos.org | imho | 01:08:01 |
| @ius:nltrix.net | Are there any samples/excepts of the database anywhere? The builds/buildoutputs table in particular I guess | 01:10:06 |
| @ius:nltrix.net | I wanted to give debugging it a shot (though writing anything but SQL might not be my forte) | 01:11:10 |
| @grahamc:nixos.org | well ... I can try and get you an export, but it is quite large. are you on github? | 01:12:24 |
| @ius:nltrix.net | Sure, same nickname. | 01:12:40 |
| @grahamc:nixos.org | mind if I DM? | 01:13:07 |
