PostgreSQL Performance - Slow performance on Windows .NET and OleDb

Author

Slow performance on Windows .NET and OleDb

Greg Quinn

2006-03-28, 7:31 am

Hello,

I have just installed PostGreSql 8.1 on my Windows XP PC. I created a simple
table called users with 4 varchar fields.

I am using the OleDb connection driver. In my .NET application, I populate
3000 records into the table to test PostGreSql's speed. It takes about 3-4
seconds.

Even worse is displaying the 3000 records in a ListView control. It takes
about 7 seconds. In MySQL, the exact same table and application displays the
same 3000 records in under 1/2 second!!!

Why is PostGreSql so slow compared to MySQL? What do you recommend I do to
speed up? It is such a simple query and small database.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Merlin Moncure

2006-03-28, 9:31 am

On 3/28/06, Greg Quinn <greg@officium.co.za> wrote:
> I am using the OleDb connection driver. In my .NET application, I populate
> 3000 records into the table to test PostGreSql's speed. It takes about 3-4
> seconds.

have you tried:
1. npgsql .net data provider
2. odbc ado.net bridge

merlin

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Jim C. Nasby

2006-03-28, 1:32 pm

On Tue, Mar 28, 2006 at 02:14:00PM +0200, Greg Quinn wrote:
> Hello,
>
> I have just installed PostGreSql 8.1 on my Windows XP PC. I created a
> simple table called users with 4 varchar fields.
>
> I am using the OleDb connection driver. In my .NET application, I populate
> 3000 records into the table to test PostGreSql's speed. It takes about 3-4
> seconds.
>
> Even worse is displaying the 3000 records in a ListView control. It takes
> about 7 seconds. In MySQL, the exact same table and application displays
> the same 3000 records in under 1/2 second!!!

Have you vacuumed recently? This smells like it might be a table bloat
problem.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Joshua D. Drake

2006-03-28, 1:32 pm

Jim C. Nasby wrote:
> On Tue, Mar 28, 2006 at 02:14:00PM +0200, Greg Quinn wrote:
>
> Have you vacuumed recently? This smells like it might be a table bloat
> problem.

This could be a lot of things...

He is probably running the default postgresql.conf which is going to
perform horribly.

What is your work_mem? shared_buffers?

Are you passing a where clause? If so is there an index on the field
that is subject to the clause?

When you do the population, is it via inserts or copy?

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Greg Quinn

2006-03-29, 3:31 am

The query is,

select * from users

which returns 4 varchar fields, there is no where clause

Yes, I am running the default postgres config. Basically I have been a MySQL
user and thought I would like to check out PostGreSql. So I did a quick
performance test. The performance was so different that I thought PostGreSQL
was nothing compared to MySQL, but now it seems its just a few configuration
options. Strange how the defult config would be so slow...

I have begun reading the documentation but am not too sure what options I
can quickly tweak to get good performance, could somebody give me some tips?

Thanks

----- Original Message -----
From: "Joshua D. Drake" <jd@commandprompt.com>
To: "Jim C. Nasby" <jnasby@pervasive.com>
Cc: "Greg Quinn" <greg@officium.co.za>; <pgsql- performance@postgres
ql.org>
Sent: Tuesday, March 28, 2006 7:52 PM
Subject: Re: [PERFORM] Slow performance on Windows .NET and OleDb

> Jim C. Nasby wrote:
>
>
> This could be a lot of things...
>
> He is probably running the default postgresql.conf which is going to
> perform horribly.
>
> What is your work_mem? shared_buffers?
>
> Are you passing a where clause? If so is there an index on the field that
> is subject to the clause?
>
> When you do the population, is it via inserts or copy?
>
> Joshua D. Drake
>
>
> --
>
> === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
> Providing the most comprehensive PostgreSQL solutions since 1997
> http://www.commandprompt.com/
>
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Greg Quinn

2006-03-29, 3:31 am

Via insert

>
> When you do the population, is it via inserts or copy?
>
> Joshua D. Drake
>
>
> --
>
> === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
> Providing the most comprehensive PostgreSQL solutions since 1997
> http://www.commandprompt.com/
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Ruben Rubio Rey

2006-03-29, 3:31 am

Greg Quinn wrote:

> The query is,
>
> select * from users
>
> which returns 4 varchar fields, there is no where clause
>
> Yes, I am running the default postgres config. Basically I have been a
> MySQL user and thought I would like to check out PostGreSql. So I did
> a quick performance test. The performance was so different that I
> thought PostGreSQL was nothing compared to MySQL, but now it seems its
> just a few configuration options. Strange how the defult config would
> be so slow...

My english is poor but im gonna try to explain it:

Default configuration in postgres its not for good performance, its just
design to make it working in any computer. Thats why u have to try to
custom default config file.

Anyway, people says that mysql is faster (and lighter) than postgres (at
least with mysql 3.x vs postgres 7.4), but postgres is more advanced and
its much harder to get data corrupted.

But there is something that you should known about postgres. Postgres
creates statistics of usage, and when you "vacumm", it optimizes each
table depending of usage.

So:
- You should custom config file.
- You should vacumm it, as someone recomended before.
- Do u have any indexes? Remove it. To get all rows you do not need it

Note that I just have use it under Linux, i have no idea about how
should it work on Windows.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Chris

2006-03-29, 3:31 am

Ruben Rubio Rey wrote:
> Greg Quinn wrote:
>
[color=darkred]
> - Do u have any indexes? Remove it. To get all rows you do not need it

I wouldn't do that. Postgres needs indexing just like any other database.

It might affect this query but it's not going to help other queries.

--
Postgresql & php tutorials
http://www.designmagick.com/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Greg Quinn

2006-03-29, 7:33 am

> how many rows does it return ? a few, or a lot ?

3000 Rows - 7 seconds - very slow

Which client library may have a problem? I am using OleDb, though haven't
tried the .NET connector yet.

Network configuration?? I am running it off my home PC with no network. It
is P4 2.4 with 1 Gig Ram. Windows XP

----- Original Message -----
From: "PFC" <lists@peufeu.com>
To: "Greg Quinn" <greg@officium.co.za>
Sent: Wednesday, March 29, 2006 11:02 AM
Subject: Re: [PERFORM] Slow performance on Windows .NET and OleDb

>
>
> how many rows does it return ? a few, or a lot ?
>
>
> Good idea...
>
> From the tests I made, on simple queries like yours, with no joins, speed
> pf pg 8.x is about the same as mysql 5.x ; that is to say very fast. If
> you have a performance problem on something so basic, and moreover on
> windows, it smells like a problem in the client library, or in the TCP
> transport between client and server.
> I remember messages saying postgres on windows was slow some time ago
> here, and it turned out to be a problem in the network configuration of
> the machine.
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Markus Schaber

2006-03-29, 7:33 am

Hi, Greg,

Greg Quinn wrote:
> Via insert

Are those inserts encapsulated into a single transaction? If not, that's
the reason why it's so slow, every transaction sync()s through to the disk.

Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

PFC

2006-03-29, 7:33 am

> 3000 Rows - 7 seconds - very slow

On my PC (athlon 64 3000+ running Linux), selecting 3000 rows with 4
columns out of a 29 column table takes about 105 ms, including time to
transfer the results and convert them to native Python objects. It takes
about 85 ms on a test table with only those 4 columns.

There is definitely a problem somewhere on your system.

I'd suggest running this query in an infinite loop. Logically, it should
use 100% processor, with postgres using some percentage (30% here) and
your client using some other percentage (70% here). Is your processor used
to the max ?

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Merlin Moncure

2006-03-29, 9:32 am

On 3/29/06, Greg Quinn <greg@officium.co.za> wrote:
>
> 3000 Rows - 7 seconds - very slow
>
> Which client library may have a problem? I am using OleDb, though haven't
> tried the .NET connector yet.

esilo=# create temp table use_npgsql as select v, 12345 as a, 'abcdef'
as b, 'abcdef' as c, 4 as d from generate_series(1,10
0000) v;

SELECT
Time: 203.000 ms
esilo=# explain analyze select * from use_npgsql;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on use_npgsql (cost=0.00..1451.16 rows=61716 width=76)
(actual time=0.007..176.106 rows=100000 loops=1)
Total runtime: 336.809 ms
(2 rows)

I just pulled out 100k rows in about 1/3 second. The problem is not
your postgresql configuration. Your problem is possibly in the oledb
driver. The last time I looked at it, it was not production ready.

http://pgfoundry.org/frs/?group_id=...&release_id=407

Merlin

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Christopher Kings-Lynne

2006-03-29, 8:28 pm

You should run the select query from the psql utility to determine if
it's PostgreSQL, or your OleDb driver that's being slow. It takes like
185ms on one of my tables to get 7000 rows.

Greg Quinn wrote:
>
> 3000 Rows - 7 seconds - very slow
>
> Which client library may have a problem? I am using OleDb, though
> haven't tried the .NET connector yet.
>
> Network configuration?? I am running it off my home PC with no network.
> It is P4 2.4 with 1 Gig Ram. Windows XP
>
> ----- Original Message ----- From: "PFC" <lists@peufeu.com>
> To: "Greg Quinn" <greg@officium.co.za>
> Sent: Wednesday, March 29, 2006 11:02 AM
> Subject: Re: [PERFORM] Slow performance on Windows .NET and OleDb
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

--
Christopher Kings-Lynne

Technical Manager
CalorieKing
Tel: +618.9389.8777
Fax: +618.9389.8444
chris.kings-lynne@calorieking.com
www.calorieking.com

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links