feat: PostgreSQL destination delete stale requires sequential scan

[kd7lxl]

Which problem is this feature request solving?

The postgresql destination plugin deletes stale records like this:

cloudquery/plugins/destination/postgresql/client/delete.go

Lines 18 to 25 in f649bed

	sb.WriteString("delete from ")
	sb.WriteString(pgx.Identifier{msg.TableName}.Sanitize())
	sb.WriteString(" where ")
	sb.WriteString(schema.CqSourceNameColumn.Name)
	sb.WriteString(" = $1 and ")
	sb.WriteString(schema.CqSyncTimeColumn.Name)
	sb.WriteString(" < $2")
	batch.Queue(sb.String(), msg.SourceName, msg.SyncTime)

The filter on _cq_source_name and _cq_sync_time requires a sequential scan of the table because these fields aren't indexed:

explain delete from "gcp_compute_instances" where _cq_source_name = 'foo' and _cq_sync_time < now();
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Delete on gcp_compute_instances  (cost=0.00..20059.78 rows=0 width=0)
   ->  Seq Scan on gcp_compute_instances  (cost=0.00..20059.78 rows=2 width=6)
         Filter: ((_cq_source_name = 'foo'::text) AND (_cq_sync_time < now()))
(3 rows)

On tables with several sources, this could be slow. It seems like it could benefit from an index.

Describe the solution you'd like

Create indexes on _cq_source_name and _cq_sync_time.

Pull request (optional)

• I can submit a pull request