5 ms·
> I couldn't create an index after the import, too slow. What made that "too slow"? I just tested it, and it's not too bad. This is on my development environm
by anarazel 7y ago
> I couldn't create an index after the import, too slow.
What made that "too slow"?
I just tested it, and it's not too bad. This is on my development environment (~3yo laptop, reasonably powerful), with all kinds of compile-time debugging enabled - but with compiler optimizations turned on.
CREATE TABLE hashes(hash text, count int);
BEGIN;
TRUNCATE hashes;
\copy hashes FROM program '7z e -so ~/tmp/pwned-passwords-sha1-ordered-by-count-v4.7z pwned-passwords-sha1-ordered-by-count-v4.txt' WITH (DELIMITER ':', FREEZE);
Time: 1245024.360 ms (20:45.024)
COMMIT;
The bottleneck is 7z decompressing:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
24403 andres 20 0 31480 22292 4544 R 73.8 0.1 14:54.09 7z
23701 andres 20 0 4380008 11488 9024 S 27.9 0.0 5:42.04 postgres
And with a bit config the PG side could be made consdierably faster.
Creating an index:
CREATE INDEX ON hashes(hash COLLATE "C");
Time: 709595.370 ms (11:49.595)
And yes, without the COLLATE, and FREEZE above, it'd have taken a bit longer. But not that much.
This is all while I also was working on the same loptop, including recompiling etc.
EDIT: Formatting #2
- jeltz 7y agoWas the index build with or without parallelism?
- anarazel 7y agoWith parallelism, default settings (i.e. 2 workers).
- lcnmrn 7y agoHow long does it take to search for a hash? I'm interested in both WHERE cases: hash = 'hash1' and hash in ('hash1', 'hash2').
- anarazel 7y agoA few ms, with cold cache (both OS and postgres). The IN() roughly scales linearly with the number of elements, although it's often a bit better, as the upper tree levels are all in cache after the first few IN() element lookups.
- lcnmrn 7y agoI manage to replicate your performance, but I used a hash type index on the hash column.