Can many table names having the same prefix slow down a database?

Question

I understand (maybe wrongly) that SQL databases often use associative arrays based on B-trees and that looking up entries in these arrays entails binary search that lexicographically compares a key with the keys in the array.

I wonder whether, in a database with many tables, if many of those tables' names have similar "namespace" prefixes, such as inventory_... or accounting_... or cached_resources_for_..., queries will be slowed in any significant way if the table names need to be looked up in a B-tree and, in the binary search, when comparing keys that all look like some_long_prefix_..., the string comparison routine needs to look farther into the names to get past the common prefix.

I wonder whether giving table names unique prefixes, such as serial numbers, like 123_cached_resources_for_foo versus 124_cached_resources_for_bar, would improve performance in any significant way.

This question is not about Hungarian notation like prefixing every table's name with tbl_.

If it matters, I am interested primarily in PostgreSQL and SQLite.

Answer 1

In the grand scheme of things, certainly not.

Just think of all the other work that needs to be done. Syntax checking, query planning, security, disk I/O, per-row CPU use, collation, sorting, logging, diagnostics, replication, sharding, network...

Finding a table or other object by name would barely register in the top thousand things a database needs to do to execute even the simplest query.

Answer 2

Table names and column names don't impact on performance in any significant way.

And even if they impacted, what would matter is actually the length of such names, because long column and table names means longer sql statements and longer names on result sets, which have to be stored/copied in memory and/or transmitted on the network.

Adding other useless prefixes would make things worse, not better.

But, as I said, it wouldn't matter anyway.