Обсуждение: Select * is very slow

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.

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

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

"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

"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

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

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

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

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.