分表与带标签单表的查询性能差异：仅遍历指定字段场景

Great question! Let's break down whether there's a meaningful performance gap between these two approaches when you're only fetching time, value1, and value2 without any joins.

Key Scenarios to Consider #

1. When querying all data (no filtering on ty in Scheme 2) #

• Scheme 1: If you need to fetch all data from both tables, you'll run two separate queries like:

  SELECT time, value1, value2 FROM foo_baz;
  SELECT time, value1, value2 FROM foo_bar;
  

  The total data scanned is the sum of the two tables' sizes. Since each table only contains the three columns you care about, each row is smaller—meaning more rows fit per storage page, which boosts IO efficiency.
• Scheme 2: A single query scans the entire combined table:

  SELECT time, value1, value2 FROM foo;
  

  This table includes the extra ty column, making it slightly larger than the sum of Scheme 1's two tables. You'll scan a tiny bit more data, leading to marginally slower performance (though the difference is negligible unless you're dealing with massive datasets).

2. When querying a specific subset (e.g., data equivalent to foo_baz in Scheme 2) #

• Without indexes: Scheme 2 requires a full table scan plus filtering rows where ty matches your target value:

  SELECT time, value1, value2 FROM foo WHERE ty = 1; -- assuming 1 maps to foo_baz
  

  This is slower than Scheme 1's direct query on foo_baz, because you're scanning extra rows (the foo_bar equivalent data) and adding an extra filter step.
• With indexes on ty: If you add an index on the ty column in Scheme 2, the database can quickly locate the rows you need, bringing performance close to Scheme 1. That said, Scheme 1 still has a tiny edge—smaller rows mean more fit in memory and storage pages, reducing IO overhead.
• With partitioning: If you partition the foo table by ty, querying a specific partition is almost identical to querying Scheme 1's single table, eliminating most performance gaps.

3. Cache Efficiency #

• Scheme 1's smaller tables are more likely to be fully cached in memory if you frequently access one of them. Scheme 2's larger table may not fit in cache as easily, leading to more disk reads for repeated queries.

Bottom Line #

• If you almost always query only one of the datasets (either foo_baz or foo_bar), Scheme 1 will give you slightly better read performance, especially without indexes on Scheme 2's ty column.
• If you regularly need to query all combined data, Scheme 2's single query is more convenient, with only a minor performance hit compared to running two queries in Scheme 1.
• You can narrow the gap in Scheme 2 by adding an index on ty or using table partitioning, but Scheme 1 remains optimal for isolated dataset queries.

内容的提问来源于stack exchange，提问作者Maik Klein