Обсуждение: Slow 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"
>

On August 31, 2011 11:26:57 AM Andy Colson wrote:
> 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.
>

I think the "." is a thousands separator in some locales, possibly the reason
for confusion.

On 8/31/2011 1:51 PM, Alan Hodgson wrote:
> On August 31, 2011 11:26:57 AM Andy Colson wrote:
>> 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.
>>
>
> I think the "." is a thousands separator in some locales, possibly the reason
> for confusion.
>

D'oh.  I'm zero for two today.  Guess I'll call it a day.

-Andy

"Kai Otto" <kotto@medis.nl> wrote:

> Time taken:
>
> 35.833 ms (i.e. roughly 35 seconds)

Which is it?  35 ms or 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?

> "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"

Assuming 35 seconds for the 121 K rows, it would seem that you're
taking less than 1 ms per row on the database server, which may not
be too bad, depending on how many of them are read from disk.  The
rest of the time would seem to be in the network and the client.
That's where you need to fix something if you want it to be faster.

With only a fraction of 1% of the run time being on the database
server, any attempt to tune things there can't improve performance
by more than that fraction of a percent.

-Kevin

On August 31, 2011 11:56:56 AM Andy Colson wrote:
> On 8/31/2011 1:51 PM, Alan Hodgson wrote:
> > On August 31, 2011 11:26:57 AM Andy Colson wrote:
> >> 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.
> >
> > I think the "." is a thousands separator in some locales, possibly the
> > reason for confusion.
>
> D'oh.  I'm zero for two today.  Guess I'll call it a day.
>

Oh, no, you were right. I think the original poster is European, though and
misread the output.

Hi all,

Thanks for the replies and sorry for the late response, I have been away
for a few days.

Concerning the performance: 1 ms per row seems slow knowing that the
entire database is less then 64MB and therefore should easily fit into
memory and the client (pgAdmin III) runs on the server.

I am going to test another database to check the performance of the
hardware.

-Kai

> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
> Sent: Wednesday, August 31, 2011 8:59 PM
> To: Kai Otto; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Slow performance
>
> "Kai Otto" <kotto@medis.nl> wrote:
>
> > Time taken:
> >
> > 35.833 ms (i.e. roughly 35 seconds)
>
> Which is it?  35 ms or 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?
>
> > "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"
>
> Assuming 35 seconds for the 121 K rows, it would seem that you're
> taking less than 1 ms per row on the database server, which may not
> be too bad, depending on how many of them are read from disk.  The
> rest of the time would seem to be in the network and the client.
> That's where you need to fix something if you want it to be faster.
>
> With only a fraction of 1% of the run time being on the database
> server, any attempt to tune things there can't improve performance
> by more than that fraction of a percent.
>
> -Kevin