ADD COLUMN ts tsvector GENERATED too slow

From: Florents Tselai <florents(dot)tselai(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: ADD COLUMN ts tsvector GENERATED too slow
Date: 2022-07-06 08:42:47
Message-ID: 03C74523-7947-42CA-8392-B7CB8EFC16D2@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a beefy server (40+ worker processes , 40GB+ shared buffers) and a table holding (key text, text text,) of around 50M rows.
These are text fields extracted from 4-5 page pdfs each.

I’m adding the following generated col to keep up with tsvectors

ALTER TABLE docs_text ADD COLUMN ts tsvector GENERATED ALWAYS AS (to_tsvector(’simple', left(text, 1048575))) STORED

I expect this to be slow, but it’s been running for 18hrs already and I certainly hope I’ve done something wrong and there’s a smarter way.

I thought about incremental updates and/or triggers but a generated col is a cleaner solution.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stefan Froehlich 2022-07-06 09:31:20 unable to understand query result
Previous Message Laurenz Albe 2022-07-06 08:24:14 Re: How to upgrade postgres version 8 to 13