Is querying the number of columns faster for a small table than a large table?

Question

I am creating a database that will include a high-ranking list and require quick SQL queries for the list.

There will be 1-to-1 between the entry in the list of high scores and users, but I do not need all the user information for the list itself.

So I'm wondering if the design will query the database faster.

Setting 1:

2 tables with a ratio of 1 to 1.

user table: ID (PK) - name - email address - entryID (FK)

records table: ID (PK) - rating - message - userID (FK)

Query:

SELECT score, message
FROM entries
ORDER BY score desc

Setting 2:

1 table

user table: ID - name - email address - rating - message

Query:

SELECT score, message
FROM users
ORDER BY score desc

I am wondering: Does the number of columns in a table affect the query speed when you select a subset of columns?

Answer 1

. - -/.

, , IO, . ( 2 ) , .

, , , - IO, .

JosMac , .

: .

1 , , :

create table users (
    ID int not null primary key,
    name varchar(50) not null,
    email varchar(50) not null,
    sex int,
    age int,
    country varchar(50),
    occupation varchar(50),
    handedness int);


create table entries (
    ID int not null primary key,
    score int not null,
    message varchar(140) not null,
    userID int not null references users(ID));

create index entry_scores
on entries(score);

insert into users
select  generate_series,
    'name',
    '[e-mail]',
    CASE generate_series % 3
        WHEN 0 THEN
            null
        WHEN 1 THEN
            1
        WHEN 2 THEN
            2
    END,
    generate_series,
    'Some country',
    'some occupation',
    CASE generate_series % 3
        WHEN 0 THEN
            null
        WHEN 1 THEN
            1
        WHEN 2 THEN
            2
    END from generate_series(1, 1000000, 1);

insert into entries
select generate_series, generate_series, 'some message', generate_series from generate_series(1, 1000000, 1);

explain analyze select score, message from entries;

:

Seq Scan on entries  (cost=0.00..9117.72 rows=176472 width=302) (actual time=0.019..393.597 rows=1000000 loops=1)

2 , , :

create table users (
    ID int not null primary key,
    name varchar(30) not null,
    email varchar(30) not null,
    sex int,
    age int,
    country varchar(50),
    occupation varchar(50),
    handedness int,
    score int not null,
    message varchar(140) not null);

create index user_scores
on users(score);

insert into users
select  generate_series,
    'name',
    '[e-mail]',
    CASE generate_series % 3
        WHEN 0 THEN
            null
        WHEN 1 THEN
            1
        WHEN 2 THEN
            2
    END,
    generate_series,
    'Some country',
    'some occupation',
    CASE generate_series % 3
        WHEN 0 THEN
            null
        WHEN 1 THEN
            1
        WHEN 2 THEN
            2
    END,
    generate_series,
    'some message' from generate_series(1, 1000000, 1);

explain analyze select score, message from users;

:

Seq Scan on users  (cost=0.00..15857.46 rows=157146 width=302) (actual time=0.012..485.094 rows=1000000 loops=1)

, 1 , -, , IO .

2: .

Answer 2

, , , - . - , , .

, . , 9.6 "" ...

, , TOASTED.

:

t=# create table s09 (i int, a text);
CREATE TABLE
t=# insert into s09 select generate_series, 'text' from generate_series(1,9999999,1);
INSERT 0 9999999
t=# analyze s09;
ANALYZE
t=# explain analyze select * from s09;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on s09  (cost=0.00..154053.58 rows=9999858 width=9) (actual time=0.010..1712.339 rows=9999999 loops=1)
 Planning time: 0.046 ms
 Execution time: 2825.514 ms
(3 rows)

t=# explain analyze select i from s09;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on s09  (cost=0.00..154053.58 rows=9999858 width=4) (actual time=0.010..1828.329 rows=9999999 loops=1)
 Planning time: 0.028 ms
 Execution time: 2937.231 ms
(3 rows)

Answer 3

, , Q1 , Q2 , , Q2 , Q1, :

• Q2
• Q2 ,
• Q1 Q2 , , , . , , , ,
• ,
• , ,

, . , . , , , .

, , . , , , . , . NF - , .