pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow performance
7+ messages / 4 participants
[nested] [flat]
* Slow performance
@ 2011-08-31 13:04 Kai Otto <kotto@medis.nl>
0 siblings, 2 replies; 7+ messages in thread
From: Kai Otto @ 2011-08-31 13:04 UTC (permalink / raw)
To: 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"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow performance
@ 2011-08-31 18:26 Andy Colson <andy@squeakycode.net>
parent: Kai Otto <kotto@medis.nl>
1 sibling, 1 reply; 7+ messages in thread
From: Andy Colson @ 2011-08-31 18:26 UTC (permalink / raw)
To: Kai Otto <kotto@medis.nl>; +Cc: 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"
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow performance
@ 2011-08-31 18:51 Alan Hodgson <ahodgson@simkin.ca>
parent: Andy Colson <andy@squeakycode.net>
0 siblings, 1 reply; 7+ messages in thread
From: Alan Hodgson @ 2011-08-31 18:51 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow performance
@ 2011-08-31 18:56 Andy Colson <andy@squeakycode.net>
parent: Alan Hodgson <ahodgson@simkin.ca>
0 siblings, 1 reply; 7+ messages in thread
From: Andy Colson @ 2011-08-31 18:56 UTC (permalink / raw)
To: Alan Hodgson <ahodgson@simkin.ca>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow performance
@ 2011-08-31 18:58 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Kai Otto <kotto@medis.nl>
1 sibling, 1 reply; 7+ messages in thread
From: Kevin Grittner @ 2011-08-31 18:58 UTC (permalink / raw)
To: Kai Otto <kotto@medis.nl>; pgsql-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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow performance
@ 2011-08-31 20:05 Alan Hodgson <ahodgson@simkin.ca>
parent: Andy Colson <andy@squeakycode.net>
0 siblings, 0 replies; 7+ messages in thread
From: Alan Hodgson @ 2011-08-31 20:05 UTC (permalink / raw)
To: pgsql-performance; +Cc: Andy Colson <andy@squeakycode.net>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow performance
@ 2011-09-05 09:21 Kai Otto <kotto@medis.nl>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 7+ messages in thread
From: Kai Otto @ 2011-09-05 09:21 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-09-05 09:21 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-31 13:04 Slow performance Kai Otto <kotto@medis.nl>
2011-08-31 18:26 ` Andy Colson <andy@squeakycode.net>
2011-08-31 18:51 ` Alan Hodgson <ahodgson@simkin.ca>
2011-08-31 18:56 ` Andy Colson <andy@squeakycode.net>
2011-08-31 20:05 ` Alan Hodgson <ahodgson@simkin.ca>
2011-08-31 18:58 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-09-05 09:21 ` Kai Otto <kotto@medis.nl>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox