Do datatypes of columns affect query performance?

by

The question:

Let’s suppose I have this table:

Table "public.orders"
         Column      |     Type      | Collation | Nullable | Default 
    -----------------+---------------+-----------+----------+---------
     o_orderkey      | integer       |           | not null | 
     o_custkey       | integer       |           |          | 
     o_orderstatus   | character(1)  |           |          | 
     o_totalprice    | numeric(12,2) |           |          | 
     o_orderdate     | date          |           |          | 
     o_orderpriority | character(15) |           |          | 
     o_clerk         | character(15) |           |          | 
     o_shippriority  | integer       |           |          | 
     o_comment       | character(79) |           |          |

If I have queries involving o_orderstatus, o_orderpriority, o_clerk or o_comment columns, can I change char(n) datatype to text in order to improve them?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Yes. And probably a lot.

You never gain performance using character(n)(alias char(n)). You don’t gain anything at all because that type is outdated, mostly useless, and discouraged. Related:

But you saw that bit in the quote from the manual where it says:

apart from increased storage space when using the blank-padded type

char(n) is the blank-padded type. And column names like comment indicate mostly blank-padded waste. text or varchar remove the bloat and everything around your table becomes substantially faster, as your avg. row size probably shrinks to less than half. More tuples per data page means fewer pages to read and process per query, and that is the most important factor for performance there is.

While being at it, order columns favorably to make it more efficient, yet:

   Column      |     Type      | Collation | Nullable | Default 
---------------+---------------+-----------+----------+---------
 orderkey      | integer       |           | not null | 
 custkey       | integer       |           |          | 
 orderdate     | date          |           |          | 
 shippriority  | integer       |           |          | 
 totalprice    | numeric(12,2) |           |          | 
 orderstatus   | varchar(1)    |           |          | 
 orderpriority | varchar(15)   |           |          | 
 clerk         | varchar(15)   |           |          | 
 comment       | varchar(79)   |           |          |

Why?

I kept the length restrictions with varchar(n), but if those are just arbitrary, use text instead. A tiny bit faster, yet. (And less corner-case hassle.) See:


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment