What is a better schema for indexing: a combined varchar column or several integer columns?

Question

I want to make my table schema better. This table will insert a record per microsecond.

The table is already too big, so I could not test the table itself.

Current setup (columns id, name, one, two, three):

SELECT * 
FROM table
WHERE name = 'foo' 
  AND one = 1 
  AND two = 2 
  AND three = 3;

Maybe in the future (columns id, name, path):

SELECT * 
FROM table
WHERE
    name = 'foo' 
    AND path = '1/2/3';

If I change three integer columns to one varchar column, will the SQL run faster than now?

Using PostgreSQL
varchar length will 5~12. I think I can use bigint with zerofill (1/2/3 to 1000010200003) which may be faster than varchar.

It's probably not possible to answer this question with what you've given us. You can run various speed tests to answer this question however. Make sure you're indexing the fields you will be searching on. — ColinMac, May 10 '19 at 04:49
Int is faster than varchar u can see the answer here.https://stackoverflow.com/questions/2346920/sql-select-speed-int-vs-varchar but in here there are three int fields and if u change those into one varchar field then the speed will depend the size of varchar when u declaring — umesh99, May 10 '19 at 04:59
@ColinMac actually, I'm not a manager for this table. manager told me using every indexing as he known. — styliss, May 10 '19 at 05:38
@marc_s added content about it. I just wonder one field can faster than many than three fields. — styliss, May 10 '19 at 05:43
The choice between 1 field and 3 fields will depend on the types of query you need to run. If you do only directly lookups using fields `one`, `two`, `three`, then the combined string might be quicker. If you do lookups on either of the trailing columns without also specifying the value in `one` (and `two` if need be), the result will be slower. Selecting ranges would be harder in either case, but you have more work to do with a single column if you don't always specify the leading range. — Jonathan Leffler, May 10 '19 at 06:17

score 1 · Answer 1 · answered May 10 '19 at 07:06

Premature optimization is the root of all evil.

If you have a fixed number of integers, or at least a reasonable upper limit, stick with having an individual column for each.

You would then use a combined index over alk columns, ideally with the not nullable and selective columns first.

If you want to optimize, use smallint which only takes up two bytes.

Gordon Linoff · Accepted Answer · 2019-05-10T11:24:40.237

If I change three integer columns to one varchar column, will the SQL run faster than now?

Not noticeably so. You might produce some small impacts on performance, balancing things such as:

Are the string columns bigger or smaller than the integer keys (resulting in marginal bigger or smaller data pages and indexes)?
Is an index on two variable length strings less efficient than an index on on variable length string and three fixed length keys?
Do the results match what you need or is additional processing needed after you fetch a record?

In either case the available index is going to be used to find the row(s) that match the conditions. This is an index seek, because the comparisons are all equality. Postgres will then go directly to the rows you need. There is a lot of work going on beyond just the index comparisons.

You are describing 1,000,000 inserts per second or 84 millions inserts each day -- that is a lot. Under such circumstances, you are not using an off-the-shelf instance of Postgres running on your laptop. You should have proper DBA support to answer a question like this.

What is a better schema for indexing: a combined varchar column or several integer columns?

2 Answers2