Add index on dynpoi_status.uuid

[frodrigo]

explain         UPDATE
                  dynpoi_status
                SET
                  subtitle = marker.subtitle
                FROM
                  marker
                WHERE
                  marker.source = 36623 AND
                  dynpoi_status.uuid = marker.uuid
;

 Update on dynpoi_status  (cost=32930.03..79437.94 rows=64 width=438)
   ->  Hash Join  (cost=32930.03..79437.94 rows=64 width=438)
         Hash Cond: (dynpoi_status.uuid = marker.uuid)
         ->  Seq Scan on dynpoi_status  (cost=0.00..44883.65 rows=432965 width=164)
         ->  Hash  (cost=32827.35..32827.35 rows=8215 width=278)
               ->  Bitmap Heap Scan on marker  (cost=676.23..32827.35 rows=8215 width=278)
                     Recheck Cond: (source = 36623)
                     ->  Bitmap Index Scan on idx_marker_source_class  (cost=0.00..674.18 rows=8215 width=0)
                           Index Cond: (source = 36623)
(9 lignes)

[frodrigo]

@jocelynj The dynpoi_status (like marker) table already have an index on uuid as primary key (and a second for 0.2 API retro compact)

    "dynpoi_status_pkey" PRIMARY KEY, btree (uuid)
    "idx_dynpoi_status_id" btree (uuid_to_bigint(uuid))

The issue os not about index on dynpoi_status.

[frodrigo]

osmose_frontend=> UPDATE
                  dynpoi_status
                SET
                  subtitle = marker.subtitle
                FROM
                  marker
                WHERE
                  marker.source = 36623 AND
                  dynpoi_status.uuid = marker.uuid
;
UPDATE 0
Temps : 542,017 ms

[anayrat]

That's fun, I studied this query. If I remember correctly we had a bad estimation on marker.source cardinality. Maybe we can try by increasing statistic size on this column.

[jocelynj]

@anayrat : is it something that we can easily try?

[anayrat]

I started to work on it but not finished yet. I hope I will be available to spend some time on this issue this week.

[frodrigo]

Note, since long time, I plan to merge marker and dyna_status.