Re: Slow performance - Mailing list pgsql-performance

When you ran it, did it really feel like 30 seconds?  Or did it come
right back real quick?

Because your report says:

 > 35.833 ms

Thats ms, or milliseconds, or 0.035 seconds.

-Andy


On 8/31/2011 8:04 AM, Kai Otto wrote:
> 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"
>