Skip site navigation (1) Skip section navigation (2)

Slow performance

From: "Kai Otto" <kotto(at)medis(dot)nl>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Slow performance
Date: 2011-08-31 13:04:53
Message-ID: A2F2DADB84065C41B4948C619B5E482E021A9DE8@md-mail01.medis.local (view raw or flat)
Thread:
Lists: 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"

Responses

pgsql-performance by date

Next:From: Andy ColsonDate: 2011-08-31 13:59:59
Subject: Re: IN or EXISTS
Previous:From: Kevin GrittnerDate: 2011-08-31 12:40:29
Subject: Re: Query performance issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group