The optimization process and recommendations:

1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX my_table_idx_name ON "my_table" ("name");

The optimized query:

SELECT
        DATE_PART('year',
        my_table.created_at::timestamp) AS year,
        DATE_PART('week',
        my_table.created_at::timestamp) AS week_number,
        DATE_TRUNC('week',
        my_table.created_at::timestamp) AS week_starting,
        my_table.name,
        SUM(my_table.seconds) AS total_seconds 
    FROM
        my_table 
    WHERE
        my_table.name = 'jeff' 
        AND my_table.created_at::date >= '2021-01-01' 
        AND my_table.created_at::date <= '2021-10-01' 
    GROUP BY
        year,
        week_number,
        week_starting,
        my_table.name 
    ORDER BY
        my_table.name