Search code examples
performancepostgresqldatabase-designtablespacedatabase-optimization

Optimize PostgreSQL table for seq scans

Suppose there is a table in PostgreSQL database:

\d+ game_user

                           Table "public.game_user"
  Column  |      Type      |                       Modifiers                 | Storage 
----------+----------------+-------------------------------------------------+---------
 id       | bigint         | not null default nextval('gu_id_seq'::regclass) | plain
 created  | timestamptimez | not null default now()                          | plain
 modified | timestamptz    | not null default now()                          | plain
 status   | smallint       | not null default 1                              | plain
 user_id  | bigint         | not null                                        | plain
 game_id  | bigint         | not null                                        | plain
 referrer | varchar(128)   | default NULL::character varying                 | extended
 extra    | json           | default '{}'::json                              | extended
 nickname | varchar(32)    | default NULL::character varying                 | extended

What looks interesting here is Storage column. Is it possible to somehow optimize storing of the table on disk? For example, if I have a lot of seq scans over such table, it sound reasonable to have as much localized layout of the table as possible. Also, having smaller table size could allow effectively use OS page cache and all table readings could happen from the memory. How different storage types (plain, main, extended, etc) affects on such things and how can I tweak my table to optimize it?

Solution

  • To speed up sequential scans, use fast storage and a lot of memory.
    You can use pg_prewarm to load the table into PostgreSQL's shared buffer cache, which will speed up sequential scans considerably.

    That said, and since you ask about TOAST, the only column that might be stored out-of-line is extra, because it is the only one that can grow sufficiently large.
    TOAST can actually speed up sequential scans as long as you do not select the TOASTed column, because the value won't even be read from disk in that case.
    It will not help you with SELECT * FROM game_user.