[PERFORM] Fwd: Index on table when using DESC clause

2005-05-23 Thread Yves Vindevogel
Begin forwarded message: From: Yves Vindevogel [EMAIL PROTECTED]> Date: Mon 23 May 2005 19:23:16 CEST To: [EMAIL PROTECTED] Subject: Index on table when using DESC clause Hi, I have a table with multiple fields. Two of them are documentname and pages I have indexes on documentn

Re: [PERFORM] Fwd: Index on table when using DESC clause

2005-05-23 Thread Yves Vindevogel
I tried that, but create index ixTest on table1 (pages desc, documentname) gives me a syntax error On 23 May 2005, at 20:03, Steinar H. Gunderson wrote: On Mon, May 23, 2005 at 07:41:19PM +0200, Yves Vindevogel wrote: However, when I query my db using for instance order by pages

[PERFORM] Updates on large tables are extremely slow

2005-06-12 Thread Yves Vindevogel
is an Asus portable, P4 with 1 Gig of RAM. Disk is speedy. All runs fine except for the update queries. I would appreciate some help or a document to point me to the settings I must change. Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements inline: Pasted Graphic 2.tiff

[PERFORM] View not using index

2005-06-13 Thread Yves Vindevogel
from tbl order by x, y, it works like I want it to work Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements inline: Pasted Graphic 2.tiff Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web

Re: [PERFORM] View not using index

2005-06-13 Thread Yves Vindevogel
ges from tblPrintjobs order by descpages, documentname ; /x-tad-bigger On 13 Jun 2005, at 09:05, Russell Smith wrote: On Mon, 13 Jun 2005 04:54 pm, Yves Vindevogel wrote: Still, when I use explain, pg says it will first sort my tables instead of using my index How is that possible ?

Re: [PERFORM] View not using index

2005-06-13 Thread Yves Vindevogel
, it still is very slow. I can't see anything in the explain here, but it seems to be using a table scan. On 13 Jun 2005, at 09:18, Yves Vindevogel wrote: rvponp=# explain select * from vw_document_pagesperjob ; QUERY PLAN

Re: [PERFORM] Updates on large tables are extremely slow

2005-06-13 Thread Yves Vindevogel
: Apologies - I should have said output of 'VACUUM VERBOSE mytable'. (been using 8.1, which displays dead tuple info in ANALYZE...). Mark Yves Vindevogel wrote: rvponp=# analyze verbose tblPrintjobs ; INFO: analyzing public.tblprintjobs INFO: tblprintjobs: 19076 pages, 3000 rows sampled, 588209

Fwd: [PERFORM] Updates on large tables are extremely slow

2005-06-13 Thread Yves Vindevogel
is faster !! On 13 Jun 2005, at 11:02, Yves Vindevogel wrote: rvponp=# vacuum verbose tblPrintjobs ; INFO: vacuuming public.tblprintjobs INFO: index pkprintjobs now contains 622972 row versions in 8410 pages DETAIL: 9526 index row versions were removed. 0 index pages have been deleted, 0

Re: [PERFORM] Updates on large tables are extremely slow

2005-06-13 Thread Yves Vindevogel
. On 13 Jun 2005, at 13:51, Yves Vindevogel wrote: I have started this on my testmachine at 11h20. It's still running and here it's 13h40. Setup: Intel P4 2Ghz, 1 Gb ram ReiserFS 3 (with atime in fstab, which is not optimal) Slackware 10 PG 7.4 I have the same problems on my OSX and other test

Fwd: [PERFORM] Updates on large tables are extremely slow

2005-06-13 Thread Yves Vindevogel
I forgot cc Begin forwarded message: From: Yves Vindevogel [EMAIL PROTECTED]> Date: Mon 13 Jun 2005 17:45:19 CEST To: Tom Lane [EMAIL PROTECTED]> Subject: Re: [PERFORM] Updates on large tables are extremely slow Yes, but if I update one column, why should PG update 21 indexes ? There'

Re: [PERFORM] Updates on large tables are extremely slow

2005-06-13 Thread Yves Vindevogel
Ok, if all 21 are affected, I can understand the problem. But allow me to say that this is a functional error On 13 Jun 2005, at 18:02, Richard Huxton wrote: Yves Vindevogel wrote: I forgot cc Begin forwarded message: From: Yves Vindevogel [EMAIL PROTECTED]> Date: Mon 13 Jun 2005 17:45:19 C

Re: [PERFORM] Updates on large tables are extremely slow

2005-06-13 Thread Yves Vindevogel
to update a single field in one table with a simple value (negative value of another field) That can not be that hard ... Or is it the MVCC that is responsible for this ? It can't be indexes on other tables, right ? That would be absolutely sick On 13 Jun 2005, at 18:45, Yves Vindevogel wrote

[PERFORM] Multiple disks: RAID 5 or PG Cluster

2005-06-17 Thread Yves Vindevogel
? First concern is performance, not redundancy (we can do that a different way because all data comes from upload files) Met vriendelijke groeten, Bien vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel

Fwd: [PERFORM] Multiple disks: RAID 5 or PG Cluster

2005-06-17 Thread Yves Vindevogel
Ok, I will hate that day, but it's only 6 months Begin forwarded message: From: Vivek Khera [EMAIL PROTECTED]> Date: Fri 17 Jun 2005 23:26:43 CEST To: Yves Vindevogel [EMAIL PROTECTED]> Subject: Re: [PERFORM] Multiple disks: RAID 5 or PG Cluster On Jun 17, 2005, at 5:24 PM, Yves Vind

Fwd: [PERFORM] Multiple disks: RAID 5 or PG Cluster

2005-06-17 Thread Yves Vindevogel
BTW, tnx for the opinion ... I forgot to cc list ... Begin forwarded message: From: Yves Vindevogel [EMAIL PROTECTED]> Date: Fri 17 Jun 2005 23:29:32 CEST To: [EMAIL PROTECTED] Subject: Re: [PERFORM] Multiple disks: RAID 5 or PG Cluster Ok, striping is a good option ... I'll tell you wh

Fwd: [PERFORM] Multiple disks: RAID 5 or PG Cluster

2005-06-18 Thread Yves Vindevogel
cc ... Begin forwarded message: From: Yves Vindevogel [EMAIL PROTECTED]> Date: Sat 18 Jun 2005 18:18:53 CEST To: PFC [EMAIL PROTECTED]> Subject: Re: [PERFORM] Multiple disks: RAID 5 or PG Cluster There's a basic difference between striping (raid 0) and mirroring (raid 1) With striping

[PERFORM] Limit clause not using index

2005-06-21 Thread Yves Vindevogel
860.56..351416.15 rows=622236 width=206) Sort Key: loginuser, desceventdate, desceventtime -> Seq Scan on tblprintjobs (cost=0.00..25589.36 rows=622236 width=206) (3 rows) Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478)

Re: [PERFORM] Limit clause not using index

2005-06-21 Thread Yves Vindevogel
ixPrintjobsLoginDescEventdateDesceventtime on tblPrintjobs (loginuser, desceventdate, desceventtime) ; /x-tad-bigger On 21 Jun 2005, at 16:42, Tom Lane wrote: Yves Vindevogel [EMAIL PROTECTED]> writes: Can anyone explain me this ? rvponp=# explain select * from tblprintjobs order by loginu

Re: [PERFORM] Limit clause not using index

2005-06-21 Thread Yves Vindevogel
Nevermind guys There's an error in a function that is creating these indexes. The function never completed succesfully so the index is not there Very sorry about this !! On 21 Jun 2005, at 16:57, Yves Vindevogel wrote: x-tad-biggerThese are my indexes create index

Re: [PERFORM] Limit clause not using index

2005-06-21 Thread Yves Vindevogel
tal runtime: 271583.422 ms (4 rows) On 21 Jun 2005, at 16:42, John A Meinel wrote: Yves Vindevogel wrote: Hi, I have a very simple query on a big table. When I issue a limit and/or offset clause, the query is not using the index. Can anyone explain me this ? You didn't give enough information. W

[PERFORM] Another question on indexes (drop and recreate)

2005-06-21 Thread Yves Vindevogel
my indexes and recreating them after the inserts 2) Just inserting it and have PG manage the indexes Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11

Re: [PERFORM] Another question on indexes (drop and recreate)

2005-06-21 Thread Yves Vindevogel
And, after let's say a week, would that index still be optimal or would it be a good idea to drop it in the weekend and recreate it. On 21 Jun 2005, at 17:22, John A Meinel wrote: Yves Vindevogel wrote: Hi, I have another question regarding indexes. I have a table with a lot of indexes

Re: [PERFORM] Another question on indexes (drop and recreate)

2005-06-21 Thread Yves Vindevogel
I only add records, and most of the values are random Except the columns for dates, On 21 Jun 2005, at 17:49, John A Meinel wrote: Yves Vindevogel wrote: And, after let's say a week, would that index still be optimal or would it be a good idea to drop it in the weekend and recreate

Re: [PERFORM] Another question on indexes (drop and recreate)

2005-06-21 Thread Yves Vindevogel
Ok, tnx !! On 21 Jun 2005, at 18:54, John A Meinel wrote: Yves Vindevogel wrote: I only add records, and most of the values are random Except the columns for dates, I doubt that you would need to recreate indexes. That really only needs to be done in pathological cases, most of which have

[PERFORM] Speed with offset clause

2005-06-24 Thread Yves Vindevogel
= page 24000 - 1 = 23999, I issue the offset of 23999 * 25 This take a long time to run, about 5-10 seconds whereas offset below 100 take less than a second. Can I speed this up ? Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile

Fwd: [PERFORM] Speed with offset clause

2005-06-24 Thread Yves Vindevogel
lubaczewski wrote: On 6/24/05, Yves Vindevogel [EMAIL PROTECTED]> wrote: So, when I want the last page, which is: 600k / 25 = page 24000 - 1 = 23999, I issue the offset of 23999 * 25 improving this is hard, but not impossible. if you have right index created, try to reverse the order and fetch fi

Re: [PERFORM] Speed with offset clause

2005-06-24 Thread Yves Vindevogel
Cocoon for the website, this is not such a problematic decision, disks are cheap and I need only a few modifications to my code. On 24 Jun 2005, at 21:22, John A Meinel wrote: Yves Vindevogel wrote: Hi again all, My queries are now optimised. They all use the indexes like they should. However

Re: [PERFORM] Speed with offset clause

2005-06-24 Thread Yves Vindevogel
Jun 2005, at 22:19, Yves Vindevogel wrote: Hmm, I can't do this, i'm afraid. Or it would be rather difficult My query is executed through a webpage (link to the page in a navigation bar) I do not know how many records there are (data is changing, and currently is 600k records) The only thing I

[PERFORM] Projecting currentdb to more users

2005-07-12 Thread Yves Vindevogel
, the diskspace and other related things, when we have database of for instance 10 million records or 100 million records. Is there any math to be done on that ? Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91

[PERFORM] Insert performance (OT?)

2005-07-18 Thread Yves Vindevogel
question ... How can I keep the same performance, but also with the new index in mind ??? Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web

Re: [PERFORM] Insert performance (OT?)

2005-07-19 Thread Yves Vindevogel
nobody ? On 18 Jul 2005, at 21:29, Yves Vindevogel wrote: Hi, Suppose I have a table with 4 fields (f1, f2, f3, f4) I define 2 unique indexes u1 (f1, f2, f3) and u2 (f1, f2, f4) I have 3 records A, B, C, D (this will be inserted) A, B, C, E (this will pass u2, but not u1, thus not inserted

Fwd: [PERFORM] Insert performance (OT?)

2005-07-19 Thread Yves Vindevogel
BTW: thank you for the idea Begin forwarded message: From: Yves Vindevogel [EMAIL PROTECTED]> Date: Tue 19 Jul 2005 12:20:34 CEST To: Richard Huxton dev@archonet.com> Subject: Re: [PERFORM] Insert performance (OT?) On 19 Jul 2005, at 11:39, Richard Huxton wrote: Yves Vindevogel wro

Re: [PERFORM] Insert performance (OT?)

2005-07-19 Thread Yves Vindevogel
I will use 2 queries. They run within a function fnUpload(), so I'm going to keep it simple. On 19 Jul 2005, at 12:51, Richard Huxton wrote: Yves Vindevogel wrote: >>> So, I must use a function that will check against u1 and u2, and then insert if it is ok. I know that such a functi

Re: [PERFORM] (Re)-indexing on updates

2005-08-22 Thread Yves Vindevogel
new records, I have no trouble with the MVCC On 21 Aug 2005, at 21:06, Jeffrey W. Baker wrote: On Sun, 2005-08-21 at 20:32 +0200, Yves Vindevogel wrote: __ Hi, Say I have a table with column A, B, C, D A has a unique index

[PERFORM] Some help on buffers and other performance tricks

2005-11-09 Thread Yves Vindevogel
% of the records. The system has 1 gig of ram. I could give 512Mb to PG. Filesystem is ext2, with the -noatime parameter in fstab Could I get some suggestions in how to configure my buffers, wals, ? Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL

[PERFORM] IO Error

2005-11-12 Thread Yves Vindevogel
. One got the error yesterday, I dropped it (was brand new), recreated it and the error was gone. Now the error is there again on another database. Personally, I think it's a HD error. Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED

[PERFORM] Stored Procedure

2005-11-22 Thread Yves Vindevogel
Is there another way in PG to return a recordset from a function than to declare a type first ? create function fnTest () returns setof myDefinedTypeIDontWantToDefineFirst ... Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements Mail: [EMAIL PROTECTED] - Mobile

Re: [PERFORM] Stored Procedure

2005-11-22 Thread Yves Vindevogel
... then to call it you would do select * from abc() as (a text,b int,...); -- Original Message --- From: Yves Vindevogel [EMAIL PROTECTED]> To: pgsql-performance@postgresql.org Sent: Tue, 22 Nov 2005 19:29:37 +0100 Subject: [PERFORM] Stored Procedure Is there another way in

Re: [PERFORM] Stored Procedure

2005-11-22 Thread Yves Vindevogel
8.1, hmm, that's brand new. But, still, it's quite some coding for a complete recordset, not ? On 22 Nov 2005, at 19:59, Michael Fuhr wrote: On Tue, Nov 22, 2005 at 07:29:37PM +0100, Yves Vindevogel wrote: Is there another way in PG to return a recordset from a function than to declare a type

[PERFORM] Executing a shell command from a PG function

2005-12-10 Thread Yves Vindevogel
Hi, Is it possible to run a shell script, passing values of fields to it, in a Postgres function ? Yves Vindevogel begin:vcard fn:Yves Vindevogel n:Vindevogel;Yves org:Implements adr:;;Kempische Steenweg 206;Hasselt;;3500;Belgium email;internet:[EMAIL PROTECTED] tel;work:+32 (11) 43 55 76

Re: [PERFORM] Executing a shell command from a PG function

2005-12-10 Thread Yves Vindevogel
Thanks Michael and Jaime. The pg/sh thing is probably what I was looking for. Tnx Michael Fuhr wrote: On Sat, Dec 10, 2005 at 04:55:56PM +0100, Yves Vindevogel wrote: Is it possible to run a shell script, passing values of fields to it, in a Postgres function ? Not directly from