Ron Peacetree wrote:
The good news is all this means it's easy to demonstrate that we can
improve the performance of our sorting functionality.
Assuming we get the abyssmal physical IO performance fixed...
(because until we do, _nothing_ is going to help us as much)
I for one would be p
FreeBSD or Linux , which system has better performance for PostgreSQL
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
On 9/28/05, Ron Peacetree <[EMAIL PROTECTED]> wrote:
> 2= We use my method to sort two different tables. We now have these
> very efficient representations of a specific ordering on these tables. A
> join operation can now be done using these Btrees rather than the
> original data tables that inv
On Sat, Oct 01, 2005 at 10:22:40AM -0400, Ron Peacetree wrote:
> Assuming we get the abyssmal physical IO performance fixed...
> (because until we do, _nothing_ is going to help us as much)
I'm still not convinced this is the major problem. For example, in my
totally unscientific tests on an oldis
On R, 2005-09-30 at 13:38 -0700, Luke Lonergan wrote:
>
> Bulk loading speed is irrelevant here - that is dominated by parsing, which
> we have covered copiously (har har) previously and have sped up by 500%,
> which still makes Postgres < 1/2 the loading speed of MySQL.
Is this < 1/2 of MySQL w
I see the following routines that seem to be related to sorting.
If I were to examine these routines to consider ways to improve it, what
routines should I key in on? I am guessing that tuplesort.c is the hub
of activity for database sorting.
Directory of U:\postgresql-snapshot\src\backend\acces
Judy definitely rates a WOW!!
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
> [EMAIL PROTECTED] On Behalf Of Gregory Maxwell
> Sent: Friday, September 30, 2005 7:07 PM
> To: Ron Peacetree
> Cc: Jeffrey W. Baker; pgsql-hackers@postgresql.org; pgsql-
> [EMAIL PROTECTE
I have perused the tuple sort stuff.
The good:
The documentation of the sort algorithm from Knuth's TAOCP was
beautifully done. Everyone who writes an algorithm should credit the
original source like this, and also where it deviates. That was done
very nicely.
The bad:
With random access, tape
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
> [EMAIL PROTECTED] On Behalf Of Tom Lane
> Sent: Friday, September 30, 2005 11:02 PM
> To: Jeffrey W. Baker
> Cc: Luke Lonergan; Josh Berkus; Ron Peacetree; pgsql-
> [EMAIL PROTECTED]; pgsql-performance@postgresql.org
>
First off, Mr. Trainor's response proves nothing about anyone or
anything except Mr. Trainor.
I'm going to offer an opinion on the caching topic. I don't have
any benchmarks; I'm offering a general sense of the issue based on
decades of experience, so I'll give a short summary of that.
I've be
Hi,
I'm trying to include a custom function in my SQL-queries, which
unfortunately leaves the server hanging...
I basically search through two tables:
* TABLE_MAPPING: lists that 'abc' is mapped to 'def'
id1 | name1 | id2 | name2
-
1 | abc | 2 | def
3 | uvw
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
> [EMAIL PROTECTED] On Behalf Of PFC
> Sent: Thursday, September 29, 2005 9:10 AM
> To: [EMAIL PROTECTED]
> Cc: Pg Hackers; pgsql-performance@postgresql.org
> Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
>
>
Some additional thoughts: what appears to take the most time (i.e.
account for the highest cost in the explain), is _not_ running the
function itself (cost=0.00..0.01), but comparing the result from that
function with the name1 column in the mappings table
(cost=0.00..35935.05). Am I right? (See EX
On 10/3/05, Ron Peacetree <[EMAIL PROTECTED]> wrote:
[snip]
> Just how bad is this CPU bound condition? How powerful a CPU is
> needed to attain a DB IO rate of 25MBps?
>
> If we replace said CPU with one 2x, 10x, etc faster than that, do we
> see any performance increase?
>
> If a modest CPU can
On E, 2005-10-03 at 14:16 -0700, Josh Berkus wrote:
> Jeff,
>
> > > Nope, LOTS of testing, at OSDL, GreenPlum and Sun. For comparison, A
> > > Big-Name Proprietary Database doesn't get much more than that either.
> >
> > I find this claim very suspicious. I get single-threaded reads in
> > exce
On Tue, Oct 04, 2005 at 11:06:54PM -0400, Ron Peacetree wrote:
> Unfortunately, no matter what I say or do, I'm not going to please
> or convince anyone who has already have made their minds up
> to the extent that they post comments like Mr Trainor's below.
> His response style pretty much proves
On Tue, Oct 04, 2005 at 11:06:54PM -0400, Ron Peacetree wrote:
> Some might even argue that IBM (where Codd and Date worked)
> and Oracle just _might_ have had justification for the huge effort
> they put into developing such infrastructure.
The OS and FS world is very, very different now than i
> Talk about your IO system a bit. There might be obvious ways to improve.
>
> What System/Motherboard are you using?
> What Controller Cards are you using?
> What kind of Disks do you have (SATA, SCSI 7.6k 10k 15k)
> What denominations (9, 18, 36, 72, 143, 80, 160, 200 240Gig)?
> What kind of RAID
Talk about your IO system a bit. There might be obvious ways to improve.
What System/Motherboard are you using?
What Controller Cards are you using?
What kind of Disks do you have (SATA, SCSI 7.6k 10k 15k)
What denominations (9, 18, 36, 72, 143, 80, 160, 200 240Gig)?
What kind of RAIDs do you hav
Unfortunately, no matter what I say or do, I'm not going to please
or convince anyone who has already have made their minds up
to the extent that they post comments like Mr Trainor's below.
His response style pretty much proves my earlier point that this
is presently a religious issue within the pg
Douglas J. Trainor wrote:
Ron Peacetree sounds like someone talking out of his _AZZ_.
He can save his unreferenced flapdoodle for his SQL Server
clients. Maybe he will post references so that we may all
learn at the feet of Master Peacetree. :-)
Although I agree that I would definitely like
I have an application that has a table that is both read and write intensive.
Data from iostat indicates that the write speed of the system is the factor
that is limiting performance. The table has around 20 columns and most of the
columns are indexed. The data and the indices for the table are
Ron Peacetree sounds like someone talking out of his _AZZ_.
He can save his unreferenced flapdoodle for his SQL Server
clients. Maybe he will post references so that we may all
learn at the feet of Master Peacetree. :-)
douglas
On Oct 4, 2005, at 7:33 PM, Ron Peacetree wrote:
pg is _ver
On Tue, Oct 04, 2005 at 07:33:47PM -0400, Ron Peacetree wrote:
> pg is _very_ stupid about caching. Almost all of the caching is left
> to the OS, and it's that way by design (as post after post by TL has
> pointed out).
>
> That means pg has almost no ability to take application domain
> specifi
pg is _very_ stupid about caching. Almost all of the caching is left
to the OS, and it's that way by design (as post after post by TL has
pointed out).
That means pg has almost no ability to take application domain
specific knowledge into account when deciding what to cache.
There's plenty of pap
Postgresql uses MVCC to ensure data integrity. Server must choose the right
version of tuple, according to transaction ID of statement. Even for a
select (ACID features of postgresql, I think C and I apply here), it must
accomplish some extra work.
-Mensaje original-
De: [EMAIL PROTECTED]
Which version of PG are you using? One of the new features for 8.0 was
an improved caching algorithm that was smart enough to avoid letting a
single big query sweep everything else out of cache.
-- Mark Lewis
On Tue, 2005-10-04 at 10:45 -0400, Lane Van Ingen wrote:
> Yes, Stefan, the kind of us
On Tue, Oct 04, 2005 at 04:15:41PM -0500, Jim C. Nasby wrote:
> >Index Cond: ((playerid)::text = '0'::text)
Also, why is playerid a text field? Comparing ints will certainly be
faster...
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://perv
On Tue, Oct 04, 2005 at 05:11:19PM -0400, Joe wrote:
> Hi Jim,
>
> Jim C. Nasby wrote:
> >Also, just because no one else has mentioned it, remember that it's very
> >easy to get MySQL into a mode where you have no data integrity. If
> >that's the case it's going to be faster than PostgreSQL (thoug
On Fri, Sep 23, 2005 at 04:53:55PM +0800, K C Lau wrote:
> Thank you all for your suggestions. I' tried, with some variations too, but
> still no success. The times given are the best of a few repeated tries on
> an 8.1 beta 2 db freshly migrated from 8.0.3 on Windows.
>
> For reference, only th
Jim C. Nasby wrote:
Make sure these indexes exist if you'll be updating or inserting into
entry:
CREATE INDEX topic__subject_id ON topic(subject_id);
CREATE INDEX topic__actor_id ON topic(actor_id);
Actually, topic's primary key is topic_id.
Also, the fact that subject and actor both point t
On Fri, Sep 23, 2005 at 08:17:03PM +0800, K C Lau wrote:
> esdt=> create or replace function player_max_atdate (varchar(32)) returns
> varchar(32) as $$
> esdt$> select distinct on (PlayerID) AtDate from player where PlayerID= $1
> order by PlayerID desc, AtDate desc limit 1;
> esdt$> $$ languag
On Thu, Sep 29, 2005 at 10:54:58PM +0300, [EMAIL PROTECTED] wrote:
> Hi All,
>
> I have a SQL function like :
>
> CREATE OR REPLACE FUNCTION
> fn_get_yetkili_inisyer_listesi(int4, int4)
> RETURNS SETOF kod_adi_liste_type AS
> $BODY$
> SELECT Y.KOD,Y.ADI
>FROM T_YER Y
> WHERE EX
PFC wrote:
- if you use a version before 8, type mismatch will prevent use of the
indexes.
I'm using 8.0.3, but the type mismatch between relationship.rel_type and
entry_type.type_id was unintended. The current databases use SMALLINT for both.
The PostgreSQL schema was derived from an expo
On Tue, Oct 04, 2005 at 12:31:42PM +0200, Stefan Weiss wrote:
> On 2005-09-30 01:21, Lane Van Ingen wrote:
> > (3) Assure that a disk-based table is always in memory (outside of keeping
> > it in
> > memory buffers as a result of frequent activity which would prevent
> > LRU
> > opera
Hi Jim,
Jim C. Nasby wrote:
Also, just because no one else has mentioned it, remember that it's very
easy to get MySQL into a mode where you have no data integrity. If
that's the case it's going to be faster than PostgreSQL (though I'm not
sure how much that affects the performance of SELECTs).
On Fri, Sep 30, 2005 at 10:34:35AM +0100, Richard Huxton wrote:
> xchris wrote:
> >
> >Let's suppose i need to add an info about addresses (which includes
> >country,city,capetc etc).
> >Addresses can vary from 1 to 20 entries..
> >
> >Talking about performance is it better to include a list of
There was a discussion about this about 2 months ago. See the archives.
On Mon, Oct 03, 2005 at 01:02:26PM +0200, Steinar H. Gunderson wrote:
> I thought this might be interesting, not the least due to the extremely low
> price ($150 + the price of regular DIMMs):
>
> http://www.tomshardware.co
On Thu, Sep 29, 2005 at 04:39:36PM -0400, Joe wrote:
> Andreas Pflug wrote:
> >Hm, if you only have 4 tables, why do you need 12 queries?
> >To reduce queries, join them in the query; no need to merge them
> >physically. If you have only two main tables, I'd bet you only need 1-2
> >queries for t
On Sat, Oct 01, 2005 at 12:51:08PM -0700, Roger Hand wrote:
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] Behalf Of Jim C. Nasby
> > Sent: Friday, September 30, 2005 4:49 PM
> > Subject: Re: [PERFORM] [HACKERS] Query in SQL statement
>
> > I suggest ditc
On Thu, Sep 29, 2005 at 08:44:16AM -0400, Joe wrote:
> CREATE TABLE entry (
> entry_id serial PRIMARY KEY,
> title VARCHAR(128) NOT NULL,
> subtitle VARCHAR(128),
> subject_type SMALLINT,
> subject_id INTEGER REFERENCES topic,
> actor_type SMALLINT,
> actor_id INTEGER REFERENCES topic
On Wed, Sep 28, 2005 at 06:03:03PM -0400, Ron Peacetree wrote:
> 1= keep more of the data set in RAM
> 2= increase the size of your HD IO buffers
> 3= make your RAID sets wider (more parallel vs sequential IO)
> 4= reduce the atomic latency of your RAID sets
> (time for Fibre Channel 15Krpm HD's vs
On Thu, Sep 29, 2005 at 07:59:34AM +0530, Rajesh Kumar Mallah wrote:
> > I see. These problems regularly come up in database design. The best thing
> > you can do is modify your database design/application such that instead of
> > incrementing a count in a single row, you insert a row into a table,
Yes, Stefan, the kind of usage you are mentioning is exactly why I was
asking.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Stefan Weiss
Sent: Tuesday, October 04, 2005 6:32 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Is There Any Way ..
On 2005-09-30 01:21, Lane Van Ingen wrote:
> (3) Assure that a disk-based table is always in memory (outside of keeping
> it in
> memory buffers as a result of frequent activity which would prevent
> LRU
> operations from taking it out) ?
I was wondering about this too. IMO it would
It's more understandable if the table names are in front of the column
names :
SELECT relationship.topic_id1, relationship.topic_id2, topic.topic_name,
topic.categ_id, topic.list_name, topic.title,
topic.url, topic.page_type, relationship.rel_type, entry_type.inverse_id,
relationship.desc
46 matches
Mail list logo
