Hacker News Reader

Comment by azurelake | Hacker News Reader

> You can't combine two indexes to filter on two cols; you need one composite index

Can you expand on how you perceive traditional RDMSs to be different for this case?

    CREATE TABLE foo (id bigserial, bar int, baz int);
    CREATE INDEX foobar ON foo(bar);
    CREATE INDEX foobaz ON foo(baz);
    SELECT \* FROM foo WHERE bar = 2 AND baz = 4;

Postgres (and I think MySQL) will use both indexes in the above query*. Spanner can only use one index, which will be slow if there are many non-matching bar=2 or baz=4 rows.

So Spanner needs CREATE INDEX foobarbaz ON foo(bar, baz); Which Postgres could also use, and it'd be a bit faster, but the index-combining is decently fast too and much nicer when you consider a table with like 10 cols and many ways to filter/join.

* https://www.postgresql.org/docs/current/indexes-bitmap-scans...

Bitmap Scan relies on shared buffers. Distributed SQL cannot share the buffers between nodes. BitmapAnd is nice but expensive. I prefer a compound index on foo(bar, baz) but PostgreSQL needs an additional index if there are query with condition on baz only. YugabyteDB, thanks to hybrid scan, can use this single index also for SELECT * FROM foo WHERE baz = 4;

I used your example to show how the 2 indexes solution is better on PostgreSQL, but the one composite index is the best one for YugabyteDB (PostgreSQL on a Spanner-like architecture): https://dev.to/yugabyte/one-fat-index-or-two-indexes-on-each...

Yeah, I rarely use composite indexes in Postgres. Only if I want to squeeze a little more performance out of a frequent combo query.

Reply on news.ycombinator.com