pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect * is very slow
9+ messages / 7 participants
[nested] [flat]
* Select * is very slow
@ 2010-11-08 06:16 shaiju.ck <shaiju.ck@gmail.com>
0 siblings, 6 replies; 9+ messages in thread
From: shaiju.ck @ 2010-11-08 06:16 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a table employee with 33 columns.
The table have 200 records now.
Select * from employee takes 15 seconds to fetch the data!!!
Which seems to be very slow.
But when I say select id,name from empoyee it executes in 30ms.
Same pefromance if I say select count(*) from emloyee.
Why the query is slow if I included all the columns in the table.
As per my understanding , number of columns should not be having a major
impact on the query performance.
I have increased the shared_buffres to 1024MB, but no improvement.
I have noticed that the query "show shared_buffers" always show 8MB.Why is
this? Does it mean that changing the shared_buffers in config file have no
impact?
Can anybody help?
Shaiju
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Select-is-very-slow-tp3254568p3254568.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select * is very slow
@ 2010-11-08 15:23 Pavel Stehule <pavel.stehule@gmail.com>
parent: shaiju.ck <shaiju.ck@gmail.com>
5 siblings, 0 replies; 9+ messages in thread
From: Pavel Stehule @ 2010-11-08 15:23 UTC (permalink / raw)
To: shaiju.ck <shaiju.ck@gmail.com>; +Cc: pgsql-performance
Hello
do you use a VACUUM statement?
Regards
Pavel Stehule
2010/11/8 shaiju.ck <shaiju.ck@gmail.com>:
> Hi, I have a table employee with 33 columns. The table have 200 records now.
> Select * from employee takes 15 seconds to fetch the data!!! Which seems to
> be very slow. But when I say select id,name from empoyee it executes in
> 30ms. Same pefromance if I say select count(*) from emloyee. Why the query
> is slow if I included all the columns in the table. As per my understanding
> , number of columns should not be having a major impact on the query
> performance. I have increased the shared_buffres to 1024MB, but no
> improvement. I have noticed that the query "show shared_buffers" always show
> 8MB.Why is this? Does it mean that changing the shared_buffers in config
> file have no impact? Can anybody help? Shaiju
> ________________________________
> View this message in context: Select * is very slow
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select * is very slow
@ 2010-11-08 15:30 Thom Brown <thom@linux.com>
parent: shaiju.ck <shaiju.ck@gmail.com>
5 siblings, 0 replies; 9+ messages in thread
From: Thom Brown @ 2010-11-08 15:30 UTC (permalink / raw)
To: shaiju.ck <shaiju.ck@gmail.com>; +Cc: pgsql-performance
On 8 November 2010 06:16, shaiju.ck <shaiju.ck@gmail.com> wrote:
> Hi, I have a table employee with 33 columns. The table have 200 records
> now. Select * from employee takes 15 seconds to fetch the data!!! Which
> seems to be very slow. But when I say select id,name from empoyee it
> executes in 30ms. Same pefromance if I say select count(*) from emloyee. Why
> the query is slow if I included all the columns in the table. As per my
> understanding , number of columns should not be having a major impact on the
> query performance. I have increased the shared_buffres to 1024MB, but no
> improvement. I have noticed that the query "show shared_buffers" always show
> 8MB.Why is this? Does it mean that changing the shared_buffers in config
> file have no impact? Can anybody help? Shaiju
>
Could you run an EXPLAIN ANALYZE on the query? And what do the columns
contain? For instance, if you have 10 columns each returning massive XML
documents, each hundreds of megs, the bottleneck would be I/O bandwidth.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select * is very slow
@ 2010-11-08 15:37 Justin Pitts <justinpitts@gmail.com>
parent: shaiju.ck <shaiju.ck@gmail.com>
5 siblings, 0 replies; 9+ messages in thread
From: Justin Pitts @ 2010-11-08 15:37 UTC (permalink / raw)
To: shaiju.ck <shaiju.ck@gmail.com>; +Cc: pgsql-performance
On Mon, Nov 8, 2010 at 1:16 AM, shaiju.ck <shaiju.ck@gmail.com> wrote:
> [....] I have increased the shared_buffres to 1024MB, but no
> improvement. I have noticed that the query "show shared_buffers" always show
> 8MB.Why is this? Does it mean that changing the shared_buffers in config
> file have no impact? Can anybody help? Shaiju
Have you restarted PostgreSQL? Changing that setting requires a
complete restart for it to take effect.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select * is very slow
@ 2010-11-08 17:01 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: shaiju.ck <shaiju.ck@gmail.com>
5 siblings, 1 reply; 9+ messages in thread
From: Kevin Grittner @ 2010-11-08 17:01 UTC (permalink / raw)
To: shaiju.ck <shaiju.ck@gmail.com>; pgsql-performance
"shaiju.ck" <shaiju.ck@gmail.com> wrote:
> The table have 200 records now.
> Select * from employee takes 15 seconds to fetch the data!!!
> Which seems to be very slow.
> But when I say select id,name from empoyee it executes in 30ms.
> Same pefromance if I say select count(*) from emloyee.
You haven't given nearly enough information for anyone to diagnose
the issues with any certainty. Earlier responses have asked for
some particularly important information, and I would add a request
to see the output from `VACUUM VERBOSE employee;`. Beyond that, you
might want to review this page for checks you can make yourself, and
information which you could provide to allow people to give more
informed advice:
http://wiki.postgresql.org/wiki/SlowQueryQuestions
-Kevin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select * is very slow
@ 2010-11-08 17:08 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: shaiju.ck <shaiju.ck@gmail.com>
5 siblings, 0 replies; 9+ messages in thread
From: Kevin Grittner @ 2010-11-08 17:08 UTC (permalink / raw)
To: shaiju.ck <shaiju.ck@gmail.com>; pgsql-performance
"shaiju.ck" <shaiju.ck@gmail.com> wrote:
> I have increased the shared_buffres to 1024MB, but no improvement.
> I have noticed that the query "show shared_buffers" always show
> 8MB.Why is this? Does it mean that changing the shared_buffers in
> config file have no impact?
Did you signal PostgreSQL to "reload" its configuration after making
the change?
Oh, and please show us the result of running `select version();` and
tell us about the hardware and OS.
-Kevin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select * is very slow
@ 2010-11-08 17:09 Thomas Kellerer <spam_eater@gmx.net>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 9+ messages in thread
From: Thomas Kellerer @ 2010-11-08 17:09 UTC (permalink / raw)
To: pgsql-performance
Kevin Grittner, 08.11.2010 18:01:
> "shaiju.ck"<shaiju.ck@gmail.com> wrote:
>
>> The table have 200 records now.
>> Select * from employee takes 15 seconds to fetch the data!!!
>> Which seems to be very slow.
>> But when I say select id,name from empoyee it executes in 30ms.
>> Same pefromance if I say select count(*) from emloyee.
>
> You haven't given nearly enough information for anyone to diagnose
> the issues with any certainty. Earlier responses have asked for
> some particularly important information, and I would add a request
> to see the output from `VACUUM VERBOSE employee;`. Beyond that, you
> might want to review this page for checks you can make yourself, and
> information which you could provide to allow people to give more
> informed advice:
Do you really think that VACCUM is the problem? If the OP only selects two columns it is apparently fast.
If he selects all columns it's slow, so I wouldn't suspect dead tuples here.
My bet is that there are some really large text columns in there...
He has asked the same question here:
http://forums.devshed.com/postgresql-help-21/select-is-very-slow-761130.html
but has also failed to answer the question about the table details...
Regards
Thomas
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select * is very slow
@ 2010-11-08 17:15 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 0 replies; 9+ messages in thread
From: Kevin Grittner @ 2010-11-08 17:15 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; pgsql-performance
Thomas Kellerer <spam_eater@gmx.net> wrote:
> Kevin Grittner, 08.11.2010 18:01:
>> I would add a request to see the output from `VACUUM VERBOSE
>> employee;`.
> Do you really think that VACCUM is the problem? If the OP only
> selects two columns it is apparently fast.
> If he selects all columns it's slow, so I wouldn't suspect dead
> tuples here.
>
> My bet is that there are some really large text columns in
> there...
That's something we can infer pretty well from the verbose output.
-Kevin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Select * is very slow
@ 2010-11-08 18:41 Pierre C <lists@peufeu.com>
parent: shaiju.ck <shaiju.ck@gmail.com>
5 siblings, 0 replies; 9+ messages in thread
From: Pierre C @ 2010-11-08 18:41 UTC (permalink / raw)
To: pgsql-performance@postgresql.org, "shaiju.ck" <shaiju.ck@gmail.com>
> The table have 200 records now.
> Select * from employee takes 15 seconds to fetch the data!!!
> Which seems to be very slow.
> But when I say select id,name from empoyee it executes in 30ms.
30 ms is also amazingly slow for so few records and so little data.
- please provide results of "EXPLAIN ANALYZE SELECT id FROM table"
- huge bloat (table never vacuumed ?) => VACUUM VERBOSE
- bad network cable, network interface reverting to 10 Mbps, badly
configured network, etc ? (test it and test ping to server, throughput,
etc)
- server overloaded (swapping, etc) ? (vmstat, iostat, top, etc)
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-11-08 18:41 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-08 06:16 Select * is very slow shaiju.ck <shaiju.ck@gmail.com>
2010-11-08 15:23 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-11-08 15:30 ` Thom Brown <thom@linux.com>
2010-11-08 15:37 ` Justin Pitts <justinpitts@gmail.com>
2010-11-08 17:01 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-11-08 17:09 ` Thomas Kellerer <spam_eater@gmx.net>
2010-11-08 17:15 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-11-08 17:08 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-11-08 18:41 ` Pierre C <lists@peufeu.com>
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