PostgreSQL Expert needed for large batch update

I have a test table called names with about 1 billion records of fake names of people (first_name and last_name).

I created a table called last_name_distinct in which I have each last name with the number of records it has in the names table stored in the field last_name_distict.count. To populate last_name_distict from the names table took about 30 minutes and resulted in about 20 million unique last names.

In the names table I also have a field called page. This is a page number which will be used to display the names. I want to display the names for a specific last_name over multiple pages, with a maximum of 100 records per page. So if the last name "Doe" has 150 records, it will display over 2 pages, 100 on the first page and 50 on the second page.

So to do this I asked ChatGPT to make an sql script (see below) to loop on the last_name_distinct table and then loop on the names table for the current last_name and update the page field with a page number for each record in names.

But the process for doing this takes way too long, about 1 month to run, and I am using a very fast multi-core dedicated server.

I tried using both the sql script provided by ChatGPT and writing my own program in php to do the same job, but both run too long.

I am guessing that the most efficient way of doing this is to run an sql script from within psql (\i [login to view URL]), which is what I did with the scipt ChatGPT made for me.

But I am wondering if there is a faster way of doing this? Maybe there is someone out there who is a PostgeSQL expert and nows a trick or two to make this run faster? If you have the solution please let me know.

DO $$

DECLARE

name_row record;

counter integer := 0;

record_count integer := 0;

record_row names%ROWTYPE; -- Declare a record variable to hold the values from the names table

BEGIN

-- Loop through distinct last names where count > 100

FOR name_row IN (SELECT last_name, count FROM last_name_distinct WHERE count > 100) LOOP

-- Reset the counter for each distinct last name

counter := 0;

-- Retrieve records from names table matching the current last name, sorted by last name and first name

FOR record_row IN (SELECT * FROM names WHERE last_name = name_row.last_name ORDER BY last_name, first_name) LOOP

-- Increment the counter for each record

counter := counter + 1;

-- Calculate the page number

[login to view URL] := (counter - 1) / 100 + 1;

-- Update the page field in the names table using the name_id

UPDATE names SET page = [login to view URL] WHERE name_id = record_row.name_id;

-- Print the last_name_distinct.last_name field for every 100,000th record processed

record_count := record_count + 1;

IF record_count % 100000 = 0 THEN

RAISE NOTICE 'Processing last name: %', name_row.last_name;

END IF;

END LOOP;

END LOOP;

END $$;

PostgreSQL PostgreSQL Administration PostgreSQL Programming

Nº del proyecto: #36898796

Sobre el proyecto