Slow performance - Mailing list pgsql-performance

Hi all,

I am running a simple query:

SELECT * FROM public.“Frame”

Time taken:

35.833 ms (i.e. roughly 35 seconds)

Number of rows:

121830

Number of columns:

38

This is extremely slow for a database server.

Can anyone help me in finding the problem?

Thanks,

KOtto

Client: pgAdmin III

Information:

Table definition for “Frame”:

CREATE TABLE "Frame"

(

  "ID" bigint NOT NULL,

  "Series.ID" bigint NOT NULL,

  filename text NOT NULL,

  "Frame UID" text NOT NULL,

  "Instance Number" integer,

  "Image Type" text,

  "Scanning Sequence" text,

  "Sequence Variant" text,

  "Scan Options" text,

  "MR Acquisition Type" text,

  "Sequence Name" text,

  "Angio Flag" text,

  "Repetition Time" double precision,

  "Echo Time" double precision,

  "Inversion Time" double precision,

  "Number of Averages" double precision,

  "Imaging Frequency" double precision,

  "Imaged Nucleus" text,

  "Echo Number" text,

  "Magnetic Field Strength" double precision,

  "Spacing Between Slices" double precision,

  "Number of Phase Encoding Steps" integer,

  "Echo Train Length" integer,

  "Protocol Name" text,

  "Trigger Time" double precision,

  "Nominal Interval" integer,

  "Cardiac Number of Images" integer,

  "SAR" double precision,

  "Image Position Patient" text,

  "Image Orientation Patient" text,

  "Slice Location" double precision,

  "Rows" integer,

  "Columns" integer,

  "Pixel Spacing" text,

  "Transfer Syntax UID" text,

  "SOP Instance UID" text,

  "Temporal Position Identifier" integer,

  "Number Of Temporal Positions" integer,

  CONSTRAINT "Frame_pkey" PRIMARY KEY ("ID"),

  CONSTRAINT "Frame_ID_key" UNIQUE ("ID")

)

WITH (

  OIDS=FALSE

);

ALTER TABLE "Frame" OWNER TO "MDDBClient";

GRANT ALL ON TABLE "Frame" TO "MDDBClient";

GRANT ALL ON TABLE "Frame" TO public;

PostGreSQL : 9.0

History: Query has always been slow

Hardware: Win 7 enterprise 64bit with SP1, 3.0GB RAM, Intel Xeon 3050 @ 2.13Ghz dual, 500GB HD (WD5000AAKS).

Explain:

"Seq Scan on "Frame"  (cost=0.00..9537.30 rows=121830 width=541) (actual time=0.047..93.318 rows=121830 loops=1)"

"Total runtime: 100.686 ms"

Auto Vacuum: Vacuum just performed.

GUC:

"version";"PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit"

"bytea_output";"escape"

"client_encoding";"UNICODE"

"effective_cache_size";"2GB"

"lc_collate";"English_United States.1252"

"lc_ctype";"English_United States.1252"

"listen_addresses";"*"

"log_destination";"stderr"

"log_line_prefix";"%t "

"logging_collector";"on"

"max_connections";"100"

"max_stack_depth";"2MB"

"port";"5432"

"server_encoding";"UTF8"

"shared_buffers";"32MB"

"TimeZone";"CET"

"work_mem";"16MB"