Thread: slow result

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

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

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

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