pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow result
12+ messages / 9 participants
[nested] [flat]
* slow result
@ 2007-01-23 10:34 Laurent Manchon <lmanchon@univ-montp2.fr>
0 siblings, 5 replies; 12+ messages in thread
From: Laurent Manchon @ 2007-01-23 10:34 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a slow response of my PostgreSQL database 7.4 using this query below
on a table with 800000 rows:
select count(*)from tbl;
PostgreSQL return result in 28 sec every time.
although MS-SQL return result in 0.02 sec every time.
My server is a DELL PowerEdge 2600 with bi-processor Xeon at 3.2 Ghz
with 3GBytes RAM
My PostgreSQL Conf is
*********************
log_connections = yes
syslog = 2
effective_cache_size = 50000
sort_mem = 10000
max_connections = 200
shared_buffers = 3000
vacuum_mem = 32000
wal_buffers = 8
max_fsm_pages = 2000
max_fsm_relations = 100
Can you tell me is there a way to enhence performance ?
Thank you
+-----------------------------------------------------+
| Laurent Manchon |
| Email: lmanchon@univ-montp2.fr |
+-----------------------------------------------------+
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: slow result
@ 2007-01-23 10:43 Heiko W.Rupp <heiko.rupp@redhat.com>
parent: Laurent Manchon <lmanchon@univ-montp2.fr>
4 siblings, 0 replies; 12+ messages in thread
From: Heiko W.Rupp @ 2007-01-23 10:43 UTC (permalink / raw)
To: pgsql-performance
Am 23.01.2007 um 11:34 schrieb Laurent Manchon:
> Hi,
>
> I have a slow response of my PostgreSQL database 7.4 using this
> query below
> on a table with 800000 rows:
>
> select count(*)from tbl;
count(*) is doing a full tablescan over all your 800000 rows. This is
a well known "feature"
of postgres :-/
So enhancing the performance is currently only possible by having
faster disk drives.
--
Heiko W.Rupp
heiko.rupp@redhat.com, http://www.dpunkt.de/buch/
3-89864-429-4.html
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: slow result
@ 2007-01-23 10:53 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Laurent Manchon <lmanchon@univ-montp2.fr>
4 siblings, 0 replies; 12+ messages in thread
From: A. Kretschmer @ 2007-01-23 10:53 UTC (permalink / raw)
To: pgsql-performance
am Tue, dem 23.01.2007, um 11:34:52 +0100 mailte Laurent Manchon folgendes:
> Hi,
>
> I have a slow response of my PostgreSQL database 7.4 using this query below
> on a table with 800000 rows:
>
> select count(*)from tbl;
If i remember correctly, i saw this question yesterday on an other
list...
Answer:
Because PG force a sequencial scan. You can read a lot about this in the
archives. Here some links to explanations:
http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10117&cNode=0T1L6L
http://sql-info.de/postgresql/postgres-gotchas.html#1_7
http://www.varlena.com/GeneralBits/49.php
Hope that helps, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: slow result
@ 2007-01-23 10:55 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Laurent Manchon <lmanchon@univ-montp2.fr>
4 siblings, 1 reply; 12+ messages in thread
From: Steinar H. Gunderson @ 2007-01-23 10:55 UTC (permalink / raw)
To: pgsql-performance; +Cc: Laurent Manchon <lmanchon@univ-montp2.fr>
On Tue, Jan 23, 2007 at 11:34:52AM +0100, Laurent Manchon wrote:
> I have a slow response of my PostgreSQL database 7.4 using this query below
> on a table with 800000 rows:
>
> select count(*)from tbl;
Contrary to your expectations, this is _not_ a query you'd expect to be fast
in Postgres. Try real queries from your application instead -- most likely,
you'll find them to be much master. (If not, come back with the query, the
schema and the EXPLAIN ANALYZE output of your query, and you'll usually get
help nailing down the issues. :-) )
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: slow result
@ 2007-01-23 10:58 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 0 replies; 12+ messages in thread
From: Steinar H. Gunderson @ 2007-01-23 10:58 UTC (permalink / raw)
To: pgsql-performance; +Cc: Laurent Manchon <lmanchon@univ-montp2.fr>
On Tue, Jan 23, 2007 at 11:55:41AM +0100, Steinar H. Gunderson wrote:
> you'll find them to be much master.
s/master/faster/
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 12+ messages in thread
* slow result
@ 2007-01-23 12:34 Laurent Manchon <lmanchon@univ-montp2.fr>
0 siblings, 3 replies; 12+ messages in thread
From: Laurent Manchon @ 2007-01-23 12:34 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a slow response of my PostgreSQL database 7.4 using this query below
on a table with 800000 rows:
select count(*)from tbl;
PostgreSQL return result in 28 sec every time.
although MS-SQL return result in 0.02 sec every time.
My server is a DELL PowerEdge 2600 with bi-processor Xeon at 3.2 Ghz
with 3GBytes RAM
My PostgreSQL Conf is
*********************
log_connections = yes
syslog = 2
effective_cache_size = 50000
sort_mem = 10000
max_connections = 200
shared_buffers = 3000
vacuum_mem = 32000
wal_buffers = 8
max_fsm_pages = 2000
max_fsm_relations = 100
Can you tell me is there a way to enhence performance ?
Thank you
+-----------------------------------------------------+
| Laurent Manchon |
| Email: lmanchon@univ-montp2.fr |
+-----------------------------------------------------+
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: slow result
@ 2007-01-23 12:43 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Laurent Manchon <lmanchon@univ-montp2.fr>
2 siblings, 0 replies; 12+ messages in thread
From: A. Kretschmer @ 2007-01-23 12:43 UTC (permalink / raw)
To: pgsql-performance
am Tue, dem 23.01.2007, um 13:34:19 +0100 mailte Laurent Manchon folgendes:
> Hi,
>
> I have a slow response of my PostgreSQL database 7.4 using this query below
> on a table with 800000 rows:
>
> select count(*)from tbl;
PLEASE READ THE ANSWERS FOR YOUR OTHER MAILS.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: slow result
@ 2007-01-23 13:53 Bill Moran <wmoran@collaborativefusion.com>
parent: Laurent Manchon <lmanchon@univ-montp2.fr>
4 siblings, 0 replies; 12+ messages in thread
From: Bill Moran @ 2007-01-23 13:53 UTC (permalink / raw)
To: Laurent Manchon <lmanchon@univ-montp2.fr>; +Cc: pgsql-performance
In response to Laurent Manchon <lmanchon@univ-montp2.fr>:
>
> I have a slow response of my PostgreSQL database 7.4 using this query below
> on a table with 800000 rows:
>
> select count(*)from tbl;
>
> PostgreSQL return result in 28 sec every time.
> although MS-SQL return result in 0.02 sec every time.
>
> My server is a DELL PowerEdge 2600 with bi-processor Xeon at 3.2 Ghz
> with 3GBytes RAM
While there's truth in everything that's been said by others, the query
should not take _that_ long. I just tried a count(*) on a table with
460,000 rows, and it took less than a second. count(*) in PostgreSQL
is not likely to compare to most other RDBMS for the reasons others have
stated, but counting 800,000 rows shouldn't take 28 seconds.
The standard question applies: have you vacuumed recently?
> My PostgreSQL Conf is
> *********************
> log_connections = yes
> syslog = 2
> effective_cache_size = 50000
> sort_mem = 10000
> max_connections = 200
> shared_buffers = 3000
> vacuum_mem = 32000
> wal_buffers = 8
> max_fsm_pages = 2000
> max_fsm_relations = 100
>
> Can you tell me is there a way to enhence performance ?
On our 4G machines, we use shared_buffers=240000 (which equates to about
2G). The only reason I don't set it higher is that FreeBSD has a limit on
shared memory of 2G.
The caveat here is that I'm running a mix of 8.1 and 8.2. There have been
significant improvements in both the usage of shared memory, and the
optimization of count(*) since 7.4, so the first suggestion I have is to
upgrade your installation.
--
Bill Moran
Collaborative Fusion Inc.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: slow result
@ 2007-01-23 14:43 Ron <rjpeace@earthlink.net>
parent: Laurent Manchon <lmanchon@univ-montp2.fr>
2 siblings, 0 replies; 12+ messages in thread
From: Ron @ 2007-01-23 14:43 UTC (permalink / raw)
To: Laurent Manchon <lmanchon@univ-montp2.fr>; +Cc: pgsql-performance
At 07:34 AM 1/23/2007, Laurent Manchon wrote:
>Hi,
>
>I have a slow response of my PostgreSQL database 7.4 using this query below
>on a table with 800000 rows:
1= Upgrade to the latest stable version of pg. That would be
8.2.x You are very much in the Dark Ages pg version wise.
pg 8.x has significant IO enhancements. Especially compared to 7.4.
>select count(*)from tbl;
>
>PostgreSQL return result in 28 sec every time.
>although MS-SQL return result in 0.02 sec every time.
2= pg actually counts how many rows there are in a table. MS-SQL
looks up a count value from a internal data table... ....which can be
wrong in extraordinarily rare circumstances in a MVCC DBMS (which
MS-SQL is !not!. MS-SQL uses the older hierarchical locking strategy
for data protection.)
Since pg actually scans the table for the count, pg's count will
always be correct. No matter what.
Since MS-SQL does not use MVCC, it does not have to worry about the
corner MVCC cases that pg does.
OTOH, MVCC _greatly_ reduces the number of cases where one
transaction can block another compared to the locking strategy used in MS-SQL.
This means in real day to day operation, pg is very likely to handle
OLTP loads and heavy loads better than MS-SQL will.
In addition, MS-SQL is a traditional Codd & Date table oriented
DBMS. pg is an object oriented DBMS.
Two very different products with very different considerations and
goals (and initially designed at very different times historically.)
Compare them under real loads using real queries if you are going to
compare them. Comparing pg and MS-SQL using "fluff" queries like
count(*) is both misleading and a waste of effort.
>My server is a DELL PowerEdge 2600 with bi-processor Xeon at 3.2 Ghz
>with 3GBytes RAM
>
>
>My PostgreSQL Conf is
>*********************
>log_connections = yes
>syslog = 2
>effective_cache_size = 50000
>sort_mem = 10000
>max_connections = 200
>shared_buffers = 3000
>vacuum_mem = 32000
>wal_buffers = 8
>max_fsm_pages = 2000
>max_fsm_relations = 100
>
>Can you tell me is there a way to enhence performance ?
There are extensive FAQs on what the above values should be for
pg. The lore is very different for pg 8.x vs pg 7.x
>Thank you
You're welcome.
Ron Peacetree
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: slow result
@ 2007-01-23 17:59 Bruno Wolff III <bruno@wolff.to>
parent: Laurent Manchon <lmanchon@univ-montp2.fr>
4 siblings, 1 reply; 12+ messages in thread
From: Bruno Wolff III @ 2007-01-23 17:59 UTC (permalink / raw)
To: Laurent Manchon <lmanchon@univ-montp2.fr>; +Cc: pgsql-performance
On Tue, Jan 23, 2007 at 11:34:52 +0100,
Laurent Manchon <lmanchon@univ-montp2.fr> wrote:
> Hi,
>
> I have a slow response of my PostgreSQL database 7.4 using this query below
> on a table with 800000 rows:
>
> select count(*)from tbl;
>
> PostgreSQL return result in 28 sec every time.
> although MS-SQL return result in 0.02 sec every time.
Besides the other advice mentioned in this thread, check that you don't
have a lot of dead tuples in that table. 28 seconds seems a bit high
for even a sequential scan of 800000 tuples unless they are pretty large.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: slow result
@ 2007-01-24 00:51 Mark Kirkwood <markir@paradise.net.nz>
parent: Laurent Manchon <lmanchon@univ-montp2.fr>
2 siblings, 0 replies; 12+ messages in thread
From: Mark Kirkwood @ 2007-01-24 00:51 UTC (permalink / raw)
To: Laurent Manchon <lmanchon@univ-montp2.fr>; +Cc: pgsql-performance
Laurent Manchon wrote:
> Hi,
>
> I have a slow response of my PostgreSQL database 7.4 using this query below
> on a table with 800000 rows:
>
> select count(*)from tbl;
>
> PostgreSQL return result in 28 sec every time.
Can you post the results of:
analyze verbose tbl;
explain analyze select count(*) from tbl;
The first will give us some info about how many pages tbl has (in 7.4
ISTR it does not state the # of dead rows... but anyway), the second
should help us deduce why it is so slow.
Also as others have pointed out, later versions are quite a bit faster
for sequential scans...
Cheers
Mark
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: slow result
@ 2007-01-24 10:19 Shoaib Mir <shoaibmir@gmail.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 12+ messages in thread
From: Shoaib Mir @ 2007-01-24 10:19 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; Laurent Manchon <lmanchon@univ-montp2.fr>; pgsql-performance
You can also try this one:
ANALYZE tablename;
select reltuples from pg_class where relname = 'tablename';
Will also give almost the same results I guess...
-------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 1/23/07, Bruno Wolff III <bruno@wolff.to> wrote:
>
> On Tue, Jan 23, 2007 at 11:34:52 +0100,
> Laurent Manchon <lmanchon@univ-montp2.fr> wrote:
> > Hi,
> >
> > I have a slow response of my PostgreSQL database 7.4 using this query
> below
> > on a table with 800000 rows:
> >
> > select count(*)from tbl;
> >
> > PostgreSQL return result in 28 sec every time.
> > although MS-SQL return result in 0.02 sec every time.
>
> Besides the other advice mentioned in this thread, check that you don't
> have a lot of dead tuples in that table. 28 seconds seems a bit high
> for even a sequential scan of 800000 tuples unless they are pretty large.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2007-01-24 10:19 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-23 10:34 slow result Laurent Manchon <lmanchon@univ-montp2.fr>
2007-01-23 10:43 ` Heiko W.Rupp <heiko.rupp@redhat.com>
2007-01-23 10:53 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-01-23 10:55 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-01-23 10:58 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-01-23 13:53 ` Bill Moran <wmoran@collaborativefusion.com>
2007-01-23 17:59 ` Bruno Wolff III <bruno@wolff.to>
2007-01-24 10:19 ` Shoaib Mir <shoaibmir@gmail.com>
2007-01-23 12:34 slow result Laurent Manchon <lmanchon@univ-montp2.fr>
2007-01-23 12:43 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-01-23 14:43 ` Ron <rjpeace@earthlink.net>
2007-01-24 00:51 ` Mark Kirkwood <markir@paradise.net.nz>
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