1

I have a table that contains both metadata about the row as well as some numeric information. The metadata is much bigger (URLs and free text versus just a few numbers for the other part).

Most of my queries ignore the metadata, e.g. they are just adding up some subset of the numbers.

If I split metadata into a different table, would that make these queries meaningfully faster? My table has about 30 million rows.

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
Xodarap
  • 11,581
  • 11
  • 56
  • 94
  • 1
    I am not expert enough on the topic to submit this as an answer, but as I understand it the extra metadata columns cause your table to occupy more pages. More pages undermines your cache. If you are on PG 11 or higher, have you considered using the `include` option to store your numeric measures in your indexes and see if your queries can run index-only? Without your benchmarking different options it is impossible to say whether any improvement would be meaningful to you. – Mike Organek Jul 29 '20 at 23:31
  • 1
    An example of the query using ```EXPLAIN ANALYZE``` query would go a long way to providing meaningful information. Also take a look at: https://wiki.postgresql.org/wiki/Slow_Query_Questions – Adrian Klaver Jul 29 '20 at 23:47
  • Maybe. Are your queries limited by IO? Can you include all the numeric columns in an index and keep your table vacuumed well enough to get efficient index-only-scans? Is the metadata column(s) defined in the table after all of the other columns? Is your metadata so large that it gets stored in a TOAST table rather than the main table? (Alas, PostgreSQL doesn't give you much control over this last one) – jjanes Jul 30 '20 at 13:50

1 Answers1

0

Selecting all the columns of the table will reduce the query performance.

SELECT * FROM TABLE;

Selecting only the required column will not affect the query performance

SELECT column1, column2 FROM TABLE1;

Disadvantages of having too many columns in PostgreSQL explained here

Thirumal
  • 8,280
  • 11
  • 53
  • 103