From: Bob Henkel <luckyratfoot@gmail.com>
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: getting count for a specific querry
Date: Fri, 8 Apr 2005 14:39:02 -0500
Message-ID: <762e5c05040812392480b796@mail.gmail.com> (raw)
In-Reply-To: <003b01c53c70$6ff184b0$797ba8c0@jfradkin>
References: <1112983840.20921.77.camel@state.g2switchworks.com>
<003b01c53c70$6ff184b0$797ba8c0@jfradkin>
On Apr 8, 2005 2:23 PM, Joel Fradkin <jfradkin@wazagua.com> wrote:
>
> Believe me I just spent two months converting our app, I do not wish to
> give
> up on that work. We do a great deal more then count. Specifically many of
> our queries run much slower on postgres. As mentioned I purchased a 4 proc
> box with 8 gigs of memory for this upgrade (Dell may have been a poor
> choice
> based on comments I have received). Even when I could see a query like
> select * from tblassoc where clientnum = 'WAZ' using indexed joins on
> location and jobtitle it is still taking 22 seconds to run compared to the
> 9
> seconds on MSSQL on a 2 proc 2 gig box. I got one of my querries to run
> faster using a page cost of .2 but then the assoc query was running 50
> seconds, so I adjusted to a cost of 2 (tried 1.2, 2, 3, and 4 and did not
> see hug changes in the assoc except it did not like .2).
>
> I have placed a call to commandprompt.com <http://commandprompt.com; and
> am going to pay for some
> support to see if they have anything meaningful to add.
>
> It could be something with my hardware, my hardware config, my postgres
> config. I am just not sure. I know I have worked diligently to try to
> learn
> all I can and I used to think I was kinda smart.
>
> I set up the data on 4 10k scsi drives in a powervault and my wal on 2 15k
> drives. I am using links to those from the install directory. It starts
> and
> stops ok this way, but maybe it should be different.
>
> I can tell you I am very happy to have this forum as I could not have
> gotten
> to the point I am without the many usefull comments from folks on the
> list.
> I greatly appreciate everyone who has helped. But truth is if I cant get
> to
> work better then I have I may have to ditch the effort and bite the 70K
> bullet. Its compounded by using 3 developers time for two months to yield
> an
> answer that my boss may just fire me for. I figured since my first test
> showed I could get data faster on the postgres box that I could with
> enough
> study get all our data to go faster, but I am afraid I have not been very
> successful.
>
> My failure is not a reflection postgres as you mentioned it is definatley
> great at some things. I have 90 some views not to mention as many stored
> procedures that have been converted. I wrote an app to move the data and
> it
> works great. But if it too slow I just will not be able to use for
> production.
>
> Joel
>
>
> Judging postgresql on one single data point (count(*) performance) is
> quite unfair. Unless your system only operates on static data and is
> used to mostly do things like counting, in which case, why are you using
> a database?
>
> PostgreSQL is a great fit for certain loads, and a poor fit for others.
> Are you going to have lots of people updating the database WHILE the
> select count(*) queries are running? Are you going to be doing other,
> more interesting things than simply counting? If so, you really should
> build a test case that emulates what you're really going to be doing
> with the system.
>
> I've found that the poor performance of aggregates in PostgreSQL is
> generally more than made up for by the outstanding behaviour it exhibits
> when under heavy parallel load.
>
> Note that the basic design of PostgreSQL's MVCC system is such that
> without using some kind of trigger to maintain pre-calculated aggregate
> information, it will NEVER be as fast as most other databases at doing
> aggregates across large chunks of your data.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
Have you posted your postgresql config files for the folks here to review? I
can't say I can help you with that because I too can only read the docs and
go from there. But for specific situations you need specific configs. I
would think you can get more out of postgresql with a some time and help
from the people around here. Though count(*) looks like it may be slow.
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-sql@postgresql.org
Cc: luckyratfoot@gmail.com, jfradkin@wazagua.com, smarlowe@g2switchworks.com, ajs@crankycanuck.ca
Subject: Re: getting count for a specific querry
In-Reply-To: <762e5c05040812392480b796@mail.gmail.com>
* 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