postgresql simple select is slow

Question

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"

Which application are you using to display the result of the select * statement? Which runtime is reported for explain analyze select * from my_table — user330315
– user330315, Commented Jan 26, 2012 at 14:48
select * just example, but select fk_id1, fk_id2, name, currency_name, description - uses ( same 17 sec ), also see question update — cetver
– cetver, Commented Jan 26, 2012 at 14:53
The runtime on the server is only 18 milli seconds (18.649ms). That means if your frontend takes 17 seconds the time is lost sending the rows over the network (or inside the client application) — user330315
– user330315, Commented Jan 26, 2012 at 14:57
If the length of one row with the description field included is about 4000 bytes and there are about 24000 rows, the client has to retrieve 24k times 4k=96 megabytes. Spending 17s on this means that the client/network is processing data at 45 MBits/s (note:bits, not bytes). It's not unreasonable if this is a 100Mbits/s network. — Daniel Vérité
– Daniel Vérité, Commented Jan 26, 2012 at 22:21
@cetver: Of course, general performance tuning always helps. Standard installations come with minimal settings in many distributions. Have a look at the Postgres Wiki. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 27, 2012 at 3:30

Chris Travers · Accepted Answer · 2013-03-24 05:45:22Z

1

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.

answered Mar 24, 2013 at 5:45

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql simple select is slow

upd

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

upd

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related