ST_DWithin exponentially slow. Cannot find what I am doing wrong

14 000 000 lines is not small. Also, If the geog that you have are uniformly distributed, the number of points concerned is around x100 when you multiply your radius x10 (the area of the circle depends of r²), so it's normal that your time augmentation seems squared. Here it seems to be more than that, but the more data you manipulate the more operations you will potentially needs because of all the cache gestion and disk call (not true for small data or big cache).

Here the explain seems ok, it uses the index so it's not the problem. You should juste be sure to VACUUM ANALYSE your tables but it shouldn't change much.

The main thing you can do if you didn't is tweak your postgresql. By default, the parameters are really conservative, if you have a big server you need to modify the parameters to use it properly. These parameters can be handle in this file on linux: /etc/postgresql/12/main/postgresql.conf then you need to restart postgres (you can easily find doc on internet if you have questions on that). Typically, what I modify are the following (adapted for around 120Go and 48 CPU of ram) :

• shared_buffers = 30GB
• effective_cache_size = 80GB
• work_mem = 256MB
• maintenance_work_mem = 5GB
• autovacuum_work_mem = 5GB
• effective_io_concurrency = 200 (for SSD, or 2 for disk)
• max_worker_processes = 48
• max_parallel_workers = 48
• max_parallel_workers_per_gather = 12
• wal_buffers = 16MB
• min_wal_size = 1GB
• max_wal_size = 2GB

Those are probably not perfect, and defined partly because of documentation that I found and partly from try and fail on big request. But if you didn't configure your postgresql at all (you said that you started) it should make a big difference in performance for big request (yours is not that big, but it should have an impact). The geometry data is usually big, so it should need more space than typical use of postgresql. Also, if you can, be sure to put your data on SSD, it can have a big impact too.

EDIT

I just reread your request, and I don't really understand why you need all the points whithin X meters if after you only keep one line by numacc. Either you didn't put the whole query, or you really only need one point. So I just rewrite it in case what you really wanted was to get the closest point. I used MATERIALIZED CTE, which create temporary table for each step, sometimes it can really improve the performance, so in case you wanted to get all the points and not just the closest neighboor, you can try to run it as is with removing the ORDER BY and the LIMIT in the INNER JOIN LATERAL at the end. And of course here I limit the search with ST_DWithin but if you want a true nearest neighboor you can remove this WHERE :

WITH usg AS MATERIALIZED
(
    SELECT
            DISTINCT(u.num_acc) AS unumacc
            , c.*
        FROM
            usagers_2018 u
        WHERE
            u.grav = '2'
        INNER JOIN caracteristiques_2018 c ON
            u.num_acc = c.num_acc
        ORDER BY
            c.id
), sub AS MATERIALIZED
(
    SELECT
            DISTINCT(usg.unumacc)
            , usg.*
            , v.*
        FROM
            usg
        INNER JOIN vehicules_2018 v ON
            usg.num_acc = v.num_acc
)
SELECT
        sub.*
        , vf.*
    FROM sub
    INNER JOIN LATERAL 
        (
            SELECT
                    vf.*
                FROM
                    valeurs_foncieres vf
                WHERE
                    ST_DWithin(
                        vf.geopoint
                        ,sub.geog
                        , 1000
                        ,FALSE
                    )
                ORDER BY vf.geopoint <-> sub.geog
                LIMIT 1
        )   
    ON TRUE;