Postgres查询耗时较长

Question

我执行此SQL查询需要很长时间(~12s)：

select nextval('rec_id_seq_af') as rec_id,ds_id,ds_dt_id,
inv_vcf_id,inv_sample_id,inv_variant_id,ds_dt_line_seq from rs_data_1_af 
group by ds_id,ds_dt_id,inv_vcf_id,inv_sample_id,inv_variant_id,ds_dt_line_seq 
order by ds_id,ds_dt_id,inv_vcf_id,inv_sample_id,inv_variant_id,ds_dt_line_seq;

当我运行时：

EXPLAIN (ANALYZE, BUFFERS)
select nextval('rec_id_seq_af') as rec_id,ds_id,ds_dt_id,
inv_vcf_id,inv_sample_id,inv_variant_id,ds_dt_line_seq from rs_data_1_af 
group by ds_id,ds_dt_id,inv_vcf_id,inv_sample_id,inv_variant_id,ds_dt_line_seq 
order by ds_id,ds_dt_id,inv_vcf_id,inv_sample_id,inv_variant_id,ds_dt_line_seq;

这是输出：

Group  (cost=724728.48..780477.07 rows=314077 width=88) (actual time=10395.641..12546.322 rows=5703 loops=1)
  Group Key: ds_id, ds_dt_id, inv_vcf_id, inv_sample_id, inv_variant_id, ds_dt_line_seq
  Buffers: shared hit=80975, temp read=91041 written=91171
  ->  Sort  (cost=724728.48..732580.39 rows=3140766 width=80) (actual time=10395.619..12019.351 rows=3140766 loops=1)
        Sort Key: ds_id, ds_dt_id, inv_vcf_id, inv_sample_id, inv_variant_id, ds_dt_line_seq
        Sort Method: external merge  Disk: 286312kB
        Buffers: shared hit=75272, temp read=91041 written=91171
        ->  Seq Scan on rs_data_1_af  (cost=0.00..106679.66 rows=3140766 width=80) (actual time=0.009..575.729 rows=3140766 loops=1)
              Buffers: shared hit=75272
Planning Time: 0.478 ms
Execution Time: 12581.964 ms

当rs_data_1_af表中的记录增加时，我将有数百万行，这个查询需要几个小时才能执行。

如何优化？

Answer 1

为什么要运行nextval('rec_id_seq_af')

nextval()推进索引并返回下一个值。对于每一行，它必须这样做，读/写隔离，难怪它很慢。

它的用途是什么，也许你可以使用generate_series()或其他什么？