CODEWITHSUNDEEP
postgresql
cetver
cetver

Reputation: 11829

postgresql simple select is slow

i have a table:

CREATE TABLE my_table
(
  id integer NOT NULL DEFAULT nextval('seq_my_table_id'::regclass),
  fk_id1 integer NOT NULL,
  fk_id2 smallint NOT NULL,
  name character varying(255) NOT NULL,
  description text,
  currency_name character varying(3) NOT NULL,
  created timestamp with time zone NOT NULL DEFAULT now(),
  updated timestamp with time zone NOT NULL DEFAULT now(),
  CONSTRAINT "PK_my_table_id" PRIMARY KEY (id ),
  CONSTRAINT "FK_my_table_fk_id1" FOREIGN KEY (fk_id1)
      REFERENCES my_table2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT "FK_my_table_fk_id2" FOREIGN KEY (fk_id2)
      REFERENCES my_table3 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
  OIDS=FALSE,
  autovacuum_enabled=true,
  autovacuum_vacuum_threshold=50,
  autovacuum_vacuum_scale_factor=0.2,
  autovacuum_analyze_threshold=50,
  autovacuum_analyze_scale_factor=0.1,
  autovacuum_vacuum_cost_delay=20,
  autovacuum_vacuum_cost_limit=200,
  autovacuum_freeze_min_age=50000000,
  autovacuum_freeze_max_age=200000000,
  autovacuum_freeze_table_age=150000000
);
ALTER TABLE my_table
  OWNER TO postgres;

CREATE INDEX my_table_fk_id1
  ON my_table
  USING btree
  (fk_id1 );

CREATE INDEX my_table_fk_id2
  ON my_table
  USING btree
  (fk_id2 );

tables records count

select count(id) from my_table; --24061
select count(id) from my_table2; --24061
select count(id) from my_table3; --123

execution time

select * from my_table -- ~17sec

vacuum/analyze - no effect
description - length ~ 4000 chars in each row

postgres.conf - standart settings

Version: 9.1

select all fields except description reduce execution time to ~1,5 sec

How to icrease select speed with description ?

upd

--explain analyze select * from my_table
"Seq Scan on my_table (cost=0.00..3425.79 rows=24079 width=1015) (actual time=0.019..17.238 rows=24079 loops=1)"
"Total runtime: 18.649 ms"

Upvotes: 1

Views: 1516

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

The question is how to make this fast. The issue is not on the server since it takes 18ms there. The simple solution is to select fewer columns so that there is less to transfer over the network. My guess is that you have long descriptions on some. Leave that column off your select and try again.

Upvotes: 1

Related Questions