sql - 选择查询需要更长的时间才能返回结果

Question

我的表格定义

CREATE TABLE public.state
(
  id integer NOT NULL,
  state character varying(50),
  district character varying(50),
  block character varying(50),
  panchayat character varying(50),
  village character varying(50),
  habitation character varying(50),
  CONSTRAINT state_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.state
  OWNER TO postgres;


CREATE INDEX state_allx
  ON public.state
  USING btree
  (id, state COLLATE pg_catalog."default", district COLLATE pg_catalog."default", block COLLATE pg_catalog."default", panchayat COLLATE pg_catalog."default", village COLLATE pg_catalog."default", habitation COLLATE pg_catalog."default");


CREATE INDEX state_habitationx
  ON public.state
  USING btree
  (id, habitation COLLATE pg_catalog."default");


CREATE INDEX state_idx
  ON public.state
  USING btree
  (id);

表中有 80359 行。

  select * from state;

上面的 select 语句用了 5.8 秒返回结果。

Total query runtime: 5.8 secs
80359 rows retrieved.

解释返回以下结果。

EXPLAIN select * from state;

"Seq Scan on state  (cost=0.00..1872.59 rows=80359 width=75)"

详细解释

EXPLAIN  (analyze, buffers, format text) select * from state ;

"Seq Scan on state  (cost=0.00..1872.59 rows=80359 width=712) (actual time=0.022..13.700 rows=80359 loops=1)"
"  Buffers: shared hit=1069"
"Planning Time: 0.727 ms"
"Execution Time: 19.810 ms"

如何提高上述查询的性能。

系统

macBook - i7 processor, 16GB RAM.

Answer 1

查询需要 19 毫秒，考虑到数据已缓存，这是一个合理的时间。

5.8秒有几种解释，估计是在客户端测的:

• 原始运行时没有缓存数据(但这不能完全解释如此长的运行时间)。

• 客户端和服务器之间的网络真的很差。

• 客户端(pgAdmin？)需要很长时间来呈现结果集。