Skip site navigation (1) Skip section navigation (2)

Re: slow result

From: Ron <rjpeace(at)earthlink(dot)net>
To: Laurent Manchon <lmanchon(at)univ-montp2(dot)fr>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow result
Date: 2007-01-23 14:43:35
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
At 07:34 AM 1/23/2007, Laurent Manchon wrote:
>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

In response to

  • slow result at 2007-01-23 12:34:19 from Laurent Manchon

pgsql-performance by date

Next:From: Merlin MoncureDate: 2007-01-23 16:05:51
Subject: Re: extract(field from timestamp) vs date dimension
Previous:From: Bill MoranDate: 2007-01-23 13:53:25
Subject: Re: slow result

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group