pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Saleem Burhani Baloch <peseek@khi.wol.net.pk>
To: pgsql-performance@postgresql.org
Subject: Slow response of PostgreSQL
Date: Tue, 17 Feb 2004 11:24:02 +0500 (PKT)
Message-ID: <200402170624.i1H6O2u16022@ns2.khi.wol.net.pk> (raw)

Hello,

I m checking Postgresql and MS-SQl database server for our new development. On a very first query Postresql is out performed and I think it is very disappointing. My query consists on a single table only on both machines.

Table Structure
                    Table "inv_detail"
  Attribute   |         Type          |      Modifier
--------------+-----------------------+--------------------
 inv_no       | integer               | not null
 unit_id      | character(4)          | not null
 item         | character varying(90) | not null
 qty          | double precision      | not null default 0
 rate         | double precision      | not null default 0
 unit         | character varying(20) | not null
 vl_ex_stax   | double precision      | not null default 0
 stax_prc     | double precision      | not null default 0
 adl_stax_prc | double precision      | not null default 0
 package      | character varying(12) |

Having 440,000 Records.

My Query
--------
select count(*), sum(vl_ex_stax) , sum(qty) , unit from inv_detail group by unit;
on both databases.

PostgreSQL return result in 50 sec every time.
MS-SQL     return result in  2 sec every time.

MS-SQL Machine
**************
Athlon 600Mhz. (Unbranded)
256 MB Ram. ( 133 Mhz)
40 GB Baracude NTFS File System.
Windows 2000 Server Enterprise.
MS-SQL 2000 Enterprise. (Default Settings)

PostgreSQL Machine
******************
P-III 600Mhz (Dell Precision 220)
256 MB Ram (RD Ram)
40 GB Baracuda Ext2 File System.
RedHat 7.2
PostgreSQL 7.1.3-2

My PostgreSQL Conf is
*********************
log_connections = yes
syslog = 2
effective_cache_size = 327680
sort_mem = 10485760
max_connections = 64
shared_buffers = 512
wal_buffers = 1024

NOTICE:  QUERY PLAN:
********************
Aggregate  (cost=inf..inf rows=44000 width=28)
  ->  Group  (cost=inf..inf rows=440000 width=28)
        ->  Sort  (cost=inf..inf rows=440000 width=28)
              ->  Seq Scan on inv_detail  (cost=0.00..11747.00 rows=440000 width=28)
EXPLAIN

Even if I dont compare postgres with any other database server the time taken is alarmingly high. These settings are not good I know, but the Postgres result is very un-acceptable. I m looking forward for comments to change the conf setting for acceptable results.

And I have two more questions :

1- How can I lock a single record so that other users can only read it. ??
2- one user executes a query it will be process and when another user executes the same query having the same result should not again go for processing. The result should be come from the cache. Is this possible in postgres ??


Saleem

pgsql-performance@postgresql.org





view thread (18+ messages)  latest in thread

Message-ID: <200402170624.i1H6O2u16022@ns2.khi.wol.net.pk>
Permalink:  ../200402170624.i1H6O2u16022@ns2.khi.wol.net.pk/
Also on:    postgresql.org/message-id/200402170624.i1H6O2u16022@ns2.khi.wol.net.pk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: peseek@khi.wol.net.pk
  Subject: Re: Slow response of PostgreSQL
  In-Reply-To: <200402170624.i1H6O2u16022@ns2.khi.wol.net.pk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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