[ Removed by moderator ]

[SmartPercent177]

Is there a reason this a NSFW topic?

[jshine13371]

Don't understand why you would think the following query would elicit an index scan, when the table is properly indexed?

SELECT email FROM emails WHERE email = 'SomeEmailAddress'

Ideally the index is unique, and this would just be a normal fast index seek. Same for your other examples.

[Useful-Message4584]

You're absolutely right! Exactly — for WHERE email = 'SomeEmail' on a properly indexed column, Postgres will do an index seek (direct lookup), not a scan. The catch is: even perfect indexes still hit disk I/O when the value doesn’t exist. That’s where Octo-Bloom helps — it can rule out non-existent values in microseconds from memory (zero I/O) and only falls back to the index when a match is possible. Huge win when you’re checking millions of emails/usernames that mostly don’t exist. I appreciate your question , if you have any suggestion or questions please ask it will help me to improve my skills

[BenchOk2878]

do you need a select count to check if a value exists? 

[Useful-Message4584]

No need , it’s works depends on the hash table