undefined | Better HN

0 pointssgarland2y ago0 comments

> CLUSTER

And here we see the benefit of clustered indices, á la MySQL. Assuming, of course, your PK is k-sortable.

0 comments

It may use the same name, but reading https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.h... this doesn't really look like the same thing and wouldn't help here. InnoDB's clustered index (usually on the primary key) is used for fast row lookups by that primary key. It only has an advantage if the primary key is the order you want, but otherwise would have the exact same problem postgres's query planner was protecting me against, but without any way to fix the problem.

sgarlandOP2y ago

From the same page:

> In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.

I think that unless you're only doing table scans, or your rows are inserted in no discernible order, you should see a speedup. If the latter though, then yes of course, page jumps are page jumps.

Happy to test this to find out.

Izkata2y ago

If it does an index scan on the secondary index, unless it's an index-only scan it'll still have to jump around to different pages on the clustered index, so it can't really take advantage of the disk cache unless the whole thing is cached. Or if there's a high correlation between the secondary index's order and clustered index's order, which is what the postgres CLUSTER command does.

j / k navigate · click thread line to collapse

0 comments

Izkata2y ago

sgarlandOP2y ago

From the same page:

> In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.

I think that unless you're only doing table scans, or your rows are inserted in no discernible order, you should see a speedup. If the latter though, then yes of course, page jumps are page jumps.

Happy to test this to find out.

Izkata2y ago

j / k navigate · click thread line to collapse