Postgres invalid memory alloc request size 1073741824

Question

When I run the long lasting query [1] for updating geometry column based on the result from subquery I get the following error "invalid memory alloc request size 1073741824".

Does anybody an idea how to fix this error? Or how to modify the update query to generate geometry better way?

[1]

UPDATE hp 
SET the_geom = lines.the_geom 
FROM (
    SELECT hp_id, st_makeline(the_geom) the_geom
    FROM ( 
        SELECT hp_id, st_collect(the_geom) the_geom
        FROM (
            SELECT sbp.hp_id, point_sequence, sobr.the_geom
            FROM sbp 
            JOIN sobr ON sbp.bp_id=sobr.id
            ORDER BY hp_id, point_sequence
        ) t
        GROUP BY hp_id
    ) tt
    GROUP BY hp_id) lines 
WHERE lines.hp_id = hp.id AND hp.the_geom IS NULL;

Here is the query plan:

Update on hp  (cost=133655367.27..139530992.38 rows=1 width=268)
  ->  Nested Loop  (cost=133655367.27..139530992.38 rows=1 width=268)
        ->  Subquery Scan on lines  (cost=133655366.69..139529271.88 rows=200 width=104)
              ->  GroupAggregate  (cost=133655366.69..139529269.88 rows=200 width=40)
                    Group Key: sbp.hp_id
                    ->  GroupAggregate  (cost=133655366.69..139510248.44 rows=1267929 width=40)
                          Group Key: sbp.hp_id
                          ->  Sort  (cost=133655366.69..134489514.21 rows=333659008 width=48)
                                Sort Key: sbp.hp_id, sbp.poradove_cislo_bodu
                                ->  Hash Join  (cost=7282892.98..76155670.90 rows=333659008 width=48)
                                      Hash Cond: (sbp.bp_id = sobr.id)
                                      ->  Seq Scan on sbp  (cost=0.00..8104984.08 rows=333659008 width=24)
                                      ->  Hash  (cost=4761304.88..4761304.88 rows=124139688 width=40)
                                            ->  Seq Scan on sobr  (cost=0.00..4761304.88 rows=124139688 width=40)
        ->  Index Scan using hp_id_key on hp  (cost=0.57..8.59 rows=1 width=172)
              Index Cond: (id = lines.hp_id)
              Filter: (the_geom IS NULL)

Thanks for all the advice!

Answer 1

I think you are taking unnecessary steps, I would try:

SET the_geom = lines.the_geom 
FROM (
    SELECT sbp.hp_id,
    st_makeline(sobr.the_geom ORDER BY hp_id, point_sequence) as the_geom
    FROM sbp 
    JOIN sobr 
    ON sbp.bp_id=sobr.id
    GROUP BY hp_id
    ) lines
WHERE lines.hp_id = hp.id AND hp.the_geom IS NULL;

But without sample data I can't be 100% certain it will work