Thread: Select * is very slow

Select * is very slow

From
"shaiju.ck"
Date:
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.

Re: Select * is very slow

From
Pavel Stehule
Date:
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.
>

Re: Select * is very slow

From
Thom Brown
Date:
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

Re: Select * is very slow

From
"Kevin Grittner"
Date:
"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

Re: Select * is very slow

From
"Kevin Grittner"
Date:
"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

Re: Select * is very slow

From
Thomas Kellerer
Date:
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

Re: Select * is very slow

From
"Kevin Grittner"
Date:
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

Re: Select * is very slow

From
"Pierre C"
Date:
> 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)

Re: Select * is very slow

From
Justin Pitts
Date:
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.