5 ms·

What was your schema? What did you index? I don't have a copy of the HiBP archive at hand, but the web site says it contains 551 million entries. If we assum

What was your schema? What did you index? I don't have a copy of the HiBP archive at hand, but the web site says it contains 551 million entries. If we assume the 11 GB archive expands to 30 GB, that's certainly not a small table, but it's not what I'd call "big data".

deleted 7y ago
[deleted]
lcnmrn 7y ago
'hash' column (varchar). Index created in advance slowed down the import so much it wasn't usable. I couldn't create an index after the import, too slow. I just want to find if one or multiple hashes are present in their data set. I used Clickhouse previously with large data like this and worked much better. Obliviously, I compare oranges with apples, but PostgreSQL could support a columnar data engine or some kind of index?
- sterwill 7y ago
  If you want an index on a column (and you probably do if you're going to query over 500 million rows) you have to create it at some point. Creating it after will be more efficient in your case. So what do you mean "too slow?" How long did it take? I don't have any experience with Clickhouse, and not much with columnar databases in general, but if we're talking about a simple table with one index over one text column, I'm not sure whether it makes any difference if you store the tuples row-wise or column-wise on disk. It's an index that covers 100% of the data either way. I don't know anything about your server but it sounds like PostgreSQL just wasn't able to get much IO throughput from your storage system. Things like storage hardware, filesystem type, and kernel parameters are the big factors here.
- 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 ago
    Was the index build with or without parallelism?
    - anarazel 7y ago
      With parallelism, default settings (i.e. 2 workers).
  - lcnmrn 7y ago
    How 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 ago
      A 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 ago
        I manage to replicate your performance, but I used a hash type index on the hash column.

deleted 7y ago

[deleted]

lcnmrn 7y ago

'hash' column (varchar). Index created in advance slowed down the import so much it wasn't usable. I couldn't create an index after the import, too slow. I just want to find if one or multiple hashes are present in their data set. I used Clickhouse previously with large data like this and worked much better. Obliviously, I compare oranges with apples, but PostgreSQL could support a columnar data engine or some kind of index?

sterwill 7y ago
If you want an index on a column (and you probably do if you're going to query over 500 million rows) you have to create it at some point. Creating it after will be more efficient in your case. So what do you mean "too slow?" How long did it take? I don't have any experience with Clickhouse, and not much with columnar databases in general, but if we're talking about a simple table with one index over one text column, I'm not sure whether it makes any difference if you store the tuples row-wise or column-wise on disk. It's an index that covers 100% of the data either way. I don't know anything about your server but it sounds like PostgreSQL just wasn't able to get much IO throughput from your storage system. Things like storage hardware, filesystem type, and kernel parameters are the big factors here.
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 ago
  Was the index build with or without parallelism?
  - anarazel 7y ago
    With parallelism, default settings (i.e. 2 workers).
- lcnmrn 7y ago
  How 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 ago
    A 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 ago
      I manage to replicate your performance, but I used a hash type index on the hash column.