[PERFORM] Executing a shell command from a PG function
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 tel;home:+32 (11) 43 55 76 tel;cell:+32 (478) 80 82 91 x-mozilla-html:TRUE url:http://www.implements.be version:2.1 end:vcard ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Executing a shell command from a PG function
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 SQL or PL/pgSQL functions, but you can execute shell commands with the untrusted versions of PL/Perl, PL/Tcl, PL/Python, etc. There's even a PL/sh: http://pgfoundry.org/projects/plsh/ 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 tel;home:+32 (11) 43 55 76 tel;cell:+32 (478) 80 82 91 x-mozilla-html:TRUE url:http://www.implements.be version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Stored Procedure
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: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Stored Procedure
But this does not work without the second line, right ? BTW, the thing returned is not a record. It's a bunch of fields, not a complete record or fields of multiple records. I'm not so sure it works. On 22 Nov 2005, at 19:42, Jim Buttafuoco wrote: create function abc() returns setof RECORD ... 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 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 --- End of Original Message --- 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Stored Procedure
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 first ? In 8.1 some languages support OUT and INOUT parameters. CREATE FUNCTION foo(IN x integer, INOUT y integer, OUT z integer) AS $$ BEGIN y := y * 10; z := x * 10; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; SELECT * FROM foo(1, 2); y | z + 20 | 10 (1 row) CREATE FUNCTION fooset(IN x integer, INOUT y integer, OUT z integer) RETURNS SETOF record AS $$ BEGIN y := y * 10; z := x * 10; RETURN NEXT; y := y + 1; z := z + 1; RETURN NEXT; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; SELECT * FROM fooset(1, 2); y | z + 20 | 10 21 | 11 (2 rows) -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] IO Error
ns30966:~# NOTICE: Executing SQL: update tblPrintjobs set ApplicationType = 1 where ApplicationType is null and upper(DocumentName) like '%.DOC' ns30966:~# NOTICE: Executing SQL: update tblPrintjobs set ApplicationType = 1 where ApplicationType is null and upper(DocumentName) like 'DOCUMENT%' ns30966:~# ns30966:~# ERROR: could not read block 3231 of relation 1663/165707259/173511769: Input/output error CONTEXT: SQL statement update tblPrintjobs set ApplicationType = 1 where ApplicationType is null and upper(DocumentName) like 'DOCUMENT%' PL/pgSQL function fnapplicationtype line 30 at execute statement [1]+ Exit 1 psql -d kpmg -c select fnApplicationType() I get this error. Is this hardware related or could it be something with the postgresql.conf settings. I changed them for performance reasons. (More memory, more wal buffers). There are 2 databases. 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] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Some help on buffers and other performance tricks
Hi all, I've got PG 8.0 on Debian sarge set up ... I want to speed up performance on the system. The system will run PG, Apache front-end on port 80 and Tomcat / Cocoon for the webapp. The webapp is not so heavily used, so we can give the max performance to the database. The database has a lot of work to do, we upload files every day. The current server has 8 databases of around 1 million records. This will be more in the future. There's only one main table, with some smaller tables. 95% of the records are in that one table. A lot of updates are done on that table, affecting 10-20% 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 PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(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
Re: [PERFORM] (Re)-indexing on updates
The option with T1: A B C and T2 A D (to avoid the updates) works very well with a simple query Insert into T2 (A, D) select A, functionToGetD from T1 left join T2 on T1.A = T2.A where T2.A is null The above gives me the new records for those where D was not filled yet. Since they are all 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 on it (primary key) B and C have a normal index on it D has no index If I perform a query likeupdate tbl set D = 'whatever' ; that should make no difference on the indexes on the other columns, right ? What postgresql does on update is to make a new record, so there will be two records in your table and two records in your index. You would need to vacuum the table to mark the space for the old record free, and you would need to reindex the table to shrink the index. Or is there some kind of mechanism that does create a sort of new record, thus makes the indexes go wild. Yes. -jwb ---(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 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Insert performance (OT?)
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) A, B, F, D (this will pass u1, but not u2, thus not inserted) Now, for performance ... I have tables like this with 500.000 records where there's a new upload of approx. 20.000 records. It is only now that we say index u2 to be necessary. So, until now, I did something like insert into ... select f1, f2, f2, max(f4) group by f1, f2, f3 That is ok ... and also logically ok because of the data definition I cannot do this with 2 group by's. I tried this on paper and I'm not succeeding. 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 function is way slower that my insert query. So, my 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 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Insert performance (OT?)
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 function is way slower that my insert query. So - you have a table, called something like upload with 20,000 rows and you'd like to know whether it is safe to insert them. Well, it's easy enough to identify which ones are duplicates. SELECT * FROM upload JOIN main_table ON u1=f1 AND u2=f2 AND u3=f3; SELECT * FROM upload JOIN main_table ON u1=f1 AND u2=f2 AND u3=f4; That is a good idea. I can delete the ones that would fail my first unique index this way, and then delete the ones that would fail my second unique index and then upload them. Hmm, why did I not think of that myself. I've spent a lot of time moving data from one system to another, usually having to clean it in the process. At 9pm on a Friday, you decide that on the next job you'll find an efficient way to do it :-) Are you saying that deleting these rows and then inserting takes too long? This goes very fast, but not with a function that checks each record one by one. You could get away with one query if you converted them to left-joins: INSERT INTO ... SELECT * FROM upload LEFT JOIN ... WHERE f3 IS NULL UNION SELECT * FROM upload LEFT JOIN ... WHERE f4 IS NULL The UNION will remove duplicates for you, but this might turn out to be slower than two separate queries. -- Richard Huxton Archonet Ltd 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Insert performance (OT?)
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) A, B, F, D (this will pass u1, but not u2, thus not inserted) Now, for performance ... I have tables like this with 500.000 records where there's a new upload of approx. 20.000 records. It is only now that we say index u2 to be necessary. So, until now, I did something like insert into ... select f1, f2, f2, max(f4) group by f1, f2, f3 That is ok ... and also logically ok because of the data definition I cannot do this with 2 group by's. I tried this on paper and I'm not succeeding. 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 function is way slower that my insert query. So, my 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Projecting currentdb to more users
Hi, We have a couple of database that are identical (one for each customer). They are all relatively small, ranging from 100k records to 1m records. There's only one main table with some smaller tables, a lot of indexes and some functions. I would like to make an estimation of the performance, 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 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Speed with offset clause
Hi again all, My queries are now optimised. They all use the indexes like they should. However, there's still a slight problem when I issue the offset clause. We have a table that contains 600.000 records We display them by 25 in the webpage. So, when I want the last page, which is: 600k / 25 = 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: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Fwd: [PERFORM] Speed with offset clause
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 could do, is doing this in a function where I first get the page, and then decide whether to use the normal sort order or the reversed order That would put my weak point right in the middle, which is not that bad, but I would like to find an easier way, if that is possible Huge memory would help ? On 24 Jun 2005, at 20:54, hubert depesz 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 first adverts, and then resort it (just the 25 adverts) in correct order. it will be faster. depesz 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smallerMet 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Speed with offset clause
Hi, Indeed, I would have to do it through a function, where I check the number of pages, It puts my weakest point in the middle then. I could simply rewrite my query like you state, just to check. I think all my queries are on one table only. (I report in a website on one table, that has been denormalized into other smaller tables for speed) But the problem is on the big table. I'm currently looking at another possibility, and that is generating XML files based upon my database. This would increase disk space enormously, but limit my problems with the database. Since I am using 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, there's still a slight problem when I issue the offset clause. We have a table that contains 600.000 records We display them by 25 in the webpage. So, when I want the last page, which is: 600k / 25 = 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* Postgres has the optimization that it will plan a query, and once it reaches the limit, it can stop even though there is more data available. The problem you are having is that it has to go through offset rows first, before it can apply the limit. If you can, (as mentioned in the other post), try to refine your index so that you can reverse it for the second half of the data. This is probably tricky, as you may not know how many rows you have (or the amount might be changing). A potentially better thing, is if you have an index you are using, you could use a subselect so that the only portion that needs to have 60k rows is a single column. Maybe an example: Instead of saying: SELECT * FROM table1, table2 WHERE table1.id = table2.id ORDER BY table1.date OFFSET x LIMIT 25; You could do: SELECT * FROM (SELECT id FROM table1 OFFSET x LIMIT 25) as subselect JOIN table1 ON subselect.id = table1.id , table2 WHERE table1.id = table2.id; That means that the culling process is done on only a few rows of one table, and the rest of the real merging work is done on only a few rows. It really depends on you query, though, as what rows you are sorting on has a big influence on how well this will work. John =:-> 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Speed with offset clause
I just ran this query select p.* from tblPrintjobs p , (select oid from tblPrintjobs limit 25 offset 622825) as subset where p.oid = subset.oid And it seems to be a bit faster than without the subselect, probably because I'm only getting one column. The speed gain is not that high though On 24 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 could do, is doing this in a function where I first get the page, and then decide whether to use the normal sort order or the reversed order That would put my weak point right in the middle, which is not that bad, but I would like to find an easier way, if that is possible Huge memory would help ? On 24 Jun 2005, at 20:54, hubert depesz 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 first adverts, and then resort it (just the 25 adverts) in correct order. it will be faster. depesz Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smallerMet vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] Limit clause not using index
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 ? rvponp=# explain select * from tblprintjobs order by loginuser, desceventdate, desceventtime offset 25 limit 25 ; QUERY PLAN --- Limit (cost=349860.62..349860.68 rows=25 width=206) -> Sort (cost=349860.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) (4 rows) rvponp=# explain select * from tblprintjobs order by loginuser, desceventdate, desceventtime ; QUERY PLAN - Sort (cost=349860.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) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Limit clause not using index
x-tad-biggerThese are my indexes create index ixprintjobsapplicationtype on tblPrintjobs (applicationtype); create index ixprintjobsdesceventdate on tblPrintjobs (desceventdate); create index ixprintjobsdesceventtime on tblPrintjobs (desceventtime); create index ixprintjobsdescpages on tblPrintjobs (descpages); create index ixprintjobsdocumentname on tblPrintjobs (documentname) ; create index ixprintjobseventcomputer on tblPrintjobs (eventcomputer); create index ixprintjobseventdate on tblPrintjobs (eventdate); create index ixprintjobseventtime on tblPrintjobs (eventtime); create index ixprintjobseventuser on tblPrintjobs (eventuser); create index ixprintjobshostname on tblPrintjobs (hostname) ; create index ixprintjobsipaddress on tblPrintjobs (ipaddress) ; create index ixprintjobsloginuser on tblPrintjobs (loginuser) ; create index ixprintjobspages on tblPrintjobs (pages) ; create index ixprintjobsprintport on tblPrintjobs (printport) ; create index ixprintjobsprintqueue on tblPrintjobs (printqueue) ; create index ixprintjobsrecordnumber on tblPrintjobs (recordnumber) ; create index ixprintjobssize on tblPrintjobs (size) ; create index ixprintjobsusertype on tblPrintjobs (usertype) ; create index ixPrintjobsDescpagesDocumentname on tblPrintjobs (descpages, documentname) ; create index ixPrintjobsHostnamePrintqueueDesceventdateDesceventtime on tblPrintjobs (hostname, printqueue, desceventdate, desceventtime) ; create index 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 loginuser, desceventdate, desceventtime offset 25 limit 25 ; QUERY PLAN --- Limit (cost=349860.62..349860.68 rows=25 width=206) -> Sort (cost=349860.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) (4 rows) Do you have an index matching that sort key? I'd certainly expect the above to use it if it were there. For the full table case it's not so clear --- an indexscan isn't always better. regards, tom lane 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Limit clause not using index
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 ixprintjobsapplicationtype on tblPrintjobs (applicationtype); create index ixprintjobsdesceventdate on tblPrintjobs (desceventdate); create index ixprintjobsdesceventtime on tblPrintjobs (desceventtime); create index ixprintjobsdescpages on tblPrintjobs (descpages); create index ixprintjobsdocumentname on tblPrintjobs (documentname) ; create index ixprintjobseventcomputer on tblPrintjobs (eventcomputer); create index ixprintjobseventdate on tblPrintjobs (eventdate); create index ixprintjobseventtime on tblPrintjobs (eventtime); create index ixprintjobseventuser on tblPrintjobs (eventuser); create index ixprintjobshostname on tblPrintjobs (hostname) ; create index ixprintjobsipaddress on tblPrintjobs (ipaddress) ; create index ixprintjobsloginuser on tblPrintjobs (loginuser) ; create index ixprintjobspages on tblPrintjobs (pages) ; create index ixprintjobsprintport on tblPrintjobs (printport) ; create index ixprintjobsprintqueue on tblPrintjobs (printqueue) ; create index ixprintjobsrecordnumber on tblPrintjobs (recordnumber) ; create index ixprintjobssize on tblPrintjobs (size) ; create index ixprintjobsusertype on tblPrintjobs (usertype) ; create index ixPrintjobsDescpagesDocumentname on tblPrintjobs (descpages, documentname) ; create index ixPrintjobsHostnamePrintqueueDesceventdateDesceventtime on tblPrintjobs (hostname, printqueue, desceventdate, desceventtime) ; create index 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 loginuser, desceventdate, desceventtime offset 25 limit 25 ; QUERY PLAN --- Limit (cost=349860.62..349860.68 rows=25 width=206) -> Sort (cost=349860.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) (4 rows) Do you have an index matching that sort key? I'd certainly expect the above to use it if it were there. For the full table case it's not so clear --- an indexscan isn't always better. regards, tom lane Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller---(end of broadcast)--- TIP 8: explain analyze is your friend 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(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
Re: [PERFORM] Limit clause not using index
rvponp=# explain analyze select * from tblPrintjobs order by loginuser, desceventdate, desceventtime ; QUERY PLAN Sort (cost=345699.06..347256.49 rows=622972 width=203) (actual time=259438.952..268885.586 rows=622972 loops=1) Sort Key: loginuser, desceventdate, desceventtime -> Seq Scan on tblprintjobs (cost=0.00..25596.72 rows=622972 width=203) (actual time=21.155..8713.810 rows=622972 loops=1) Total 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. What does you index look like that you are expecting it to use? Generally, you want to have matching columns. So you would want CREATE INDEX blah ON tblprintjobs(loginuser, desceventdate, desceventtime); Next, you should post EXPLAIN ANALYZE instead of regular explain, so we can have an idea if the planner is actually making correct estimations. John =:-> rvponp=# explain select * from tblprintjobs order by loginuser, desceventdate, desceventtime offset 25 limit 25 ; QUERY PLAN --- Limit (cost=349860.62..349860.68 rows=25 width=206) -> Sort (cost=349860.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) (4 rows) rvponp=# explain select * from tblprintjobs order by loginuser, desceventdate, desceventtime ; QUERY PLAN - Sort (cost=349860.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* 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PERFORM] Another question on indexes (drop and recreate)
Hi, I have another question regarding indexes. I have a table with a lot of indexes on it. Those are needed to perform my searches. Once a day, a bunch of records is inserted in my table. Say, my table has 1.000.000 records and I add 10.000 records (1% new) What would be faster. 1) Dropping 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) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Another question on indexes (drop and recreate)
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 on it. Those are needed to perform my searches. Once a day, a bunch of records is inserted in my table. Say, my table has 1.000.000 records and I add 10.000 records (1% new) What would be faster. 1) Dropping 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* I'm guessing for 1% new that (2) would be faster. John =:-> 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Another question on indexes (drop and recreate)
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 it. It depends a little bit on the postgres version you are using. If you are only ever adding to the table, and you are not updating it or deleting from it, I think the index is always optimal. Once you start deleting from it there are a few cases where older versions would not properly re-use the empty entries, requiring a REINDEX. (Deleting low numbers and always adding high numbers was one of the cases) However, I believe that as long as you vacuum often enough, so that the system knows where the unused entries are, you don't ever have to drop and re-create the index. John =:-> 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(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
Re: [PERFORM] Another question on indexes (drop and recreate)
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 been fixed in the latest postgres. If you are only inserting (never updating or deleting), the index can never bloat, since you are only adding new stuff. (You cannot get dead items to bloat your index if you never delete anything.) John =:-> 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Fwd: [PERFORM] Multiple disks: RAID 5 or PG Cluster
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, each file is distributed over several disks, making the physical write faster because several disks can do the work. Same for reading, multiple disks return a part of the file. Striping can not be used for safety/backup, if one disk fails, your file is lost (if it is partly on that failing disk). With mirroring you do not lose any disk space. Mirroring is a technique for avoiding disasters when you have a disk failure. Every file is written twice, each time to a different disk, which is a mirror of the first one. You effectively lose half of your diskspace to that mirror. But when a disk fails, you don't lose anything, since you can rely on the other mirrored disk. Raid 10, which is the combination of that, has both. You have multiple disks that form your first part of the raid and you have an equal amount of disks for the mirror. On each part of the mirror, striping is used to spread the files like in a raid 0. This is a very costly operation. You need a minimum of 4 disks, and you lose 50% of your capacity. BTW: mirroring is always slower than striping. On 18 Jun 2005, at 18:00, PFC wrote: I do not know what clustering would do for you. But striping will provide a high level of assurance that each of your hard drives will process equivalent amounts of IO operations. I don't know what I'm talking about, but wouldn't mirorring be faster than striping for random reads like you often get on a database ? (ie. the reads can be dispatched to any disk) ? (or course, not for writes, but if you won't use fsync, random writes should be reduced no ?) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smallerMet 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Multiple disks: RAID 5 or PG Cluster
Hi, We are looking to build a new machine for a big PG database. We were wondering if a machine with 5 scsi-disks would perform better if we use a hardware raid 5 controller or if we would go for the clustering in PG. If we cluster in PG, do we have redundancy on the data like in a RAID 5 ? 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-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 8: explain analyze is your friend
Fwd: [PERFORM] Multiple disks: RAID 5 or PG Cluster
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 Vindevogel wrote: That means that only 2 / 5 of my discs are actual storage. That's a bit low, imho. Maybe I can ask my question again: Would I go for RAID 5, RAID 0 or PG clustering On 17 Jun 2005, at 22:21, Vivek Khera wrote: If you're allergic to RAID10, then do RAID5. but you'll sacrifice performance. You'll hate life the day you blow a disk and have to rebuild everything, even if it is all easily restored. 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Fwd: [PERFORM] Multiple disks: RAID 5 or PG Cluster
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 why I don't care about dataloss 1) The database will run 6 months, no more. 2) The database is fed with upload files. So, if I have a backup each day, plus my files of that day, I can restore pretty quickly. 3) Power failure is out of the question: battery backup (UPS), disk failure is minimal change: new server, new discs, 6 months ... We do have about 500.000 new records each day in that database, so that's why I want performance Records are uploaded in one major table and then denormalised into several others. But, I would like to hear somebody about the clustering method. Isn't that much used ? Or isn't it used in a single machine ? On 17 Jun 2005, at 22:38, [EMAIL PROTECTED] wrote: If you truly do not care about data protection -- either from drive loss or from sudden power failure, or anything else -- and just want to get the fastest possible performance, then do RAID 0 (striping). It may be faster to do that with software RAID on the host than with a special RAID controller. And turn off fsyncing the write ahead log in postgresql.conf (fsync = false). But be prepared to replace your whole database from scratch (or backup or whatever) if you lose a single hard drive. And if you have a sudden power loss or other type of unclean system shutdown (kernel panic or something) then your data integrity will be at risk as well. To squeeze evena little bit more performance, put your operating system, swap and PostgreSQL binaries on a cheap IDE or SATA drive--and only your data on the 5 striped SCSI drives. I do not know what clustering would do for you. But striping will provide a high level of assurance that each of your hard drives will process equivalent amounts of IO operations. Quoting Yves Vindevogel [EMAIL PROTECTED]>: Hi, We are looking to build a new machine for a big PG database. We were wondering if a machine with 5 scsi-disks would perform better if we use a hardware raid 5 controller or if we would go for the clustering in PG. If we cluster in PG, do we have redundancy on the data like in a RAID 5 ? 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smallerMet 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] View not using index
Hi, I have a view that has something like this: select x, y, z from tbl order by x, y I have created a special index on x + y I have run analyze Still, when I use explain, pg says it will first sort my tables instead of using my index How is that possible ? When I do explain select x,y,z 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] View not using index
rvponp=# explain select * from vw_document_pagesperjob ; QUERY PLAN Subquery Scan vw_document_pagesperjob (cost=82796.59..90149.20 rows=588209 width=706) -> Sort (cost=82796.59..84267.11 rows=588209 width=74) Sort Key: tblprintjobs.descpages, tblprintjobs.documentname -> Seq Scan on tblprintjobs (cost=0.00..26428.61 rows=588209 width=74) (4 rows) rvponp=# explain select * from vw_document_pagesperjob limit 10 ; QUERY PLAN -- Limit (cost=82796.59..82796.72 rows=10 width=706) -> Subquery Scan vw_document_pagesperjob (cost=82796.59..90149.20 rows=588209 width=706) -> Sort (cost=82796.59..84267.11 rows=588209 width=74) Sort Key: tblprintjobs.descpages, tblprintjobs.documentname -> Seq Scan on tblprintjobs (cost=0.00..26428.61 rows=588209 width=74) (5 rows) rvponp=# explain select documentname, eventdate, eventtime, loginuser, pages from tblPrintjobs order by descpages, documentname ; QUERY PLAN Sort (cost=81326.07..82796.59 rows=588209 width=74) Sort Key: descpages, documentname -> Seq Scan on tblprintjobs (cost=0.00..24958.09 rows=588209 width=74) (3 rows) rvponp=# explain select documentname, eventdate, eventtime, loginuser, pages from tblPrintjobs order by descpages, documentname limit 10 ; QUERY PLAN - Limit (cost=0.00..33.14 rows=10 width=74) -> Index Scan using ixprintjobspagesperjob on tblprintjobs (cost=0.00..1949116.68 rows=588209 width=74) (2 rows) x-tad-biggercreate or replace view vw_document_pagesperjob as select documentname, eventdate, eventtime, loginuser, fnFormatInt(pages) as pages 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 ? Can we see the output of the explain analyze? The definition of the view? Regards Russell Smith 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] View not using index
Note the last query below (prev post) There it does use the index rvponp=# create type tpJobsPerDay as rvponp-# ( documentname varchar(1000), rvponp(# eventdate date, rvponp(# eventtime time, rvponp(# loginuser varchar(255), rvponp(# pages varchar(20) rvponp(# ) ; CREATE TYPE rvponp=# create function fnJobsPerDay (bigint, bigint) returns setof tpJobsPerDay as rvponp-# ' rvponp'# select documentname, eventdate, eventtime, loginuser, fnFormatInt(pages) as pages rvponp'# from tblPrintjobs order by descpages, documentname rvponp'# offset $1 limit $2 ; rvponp'# ' language 'sql' ; CREATE FUNCTION rvponp=# analyze ; ANALYZE rvponp=# explain select * from fnJobsperday (1, 10) ; QUERY PLAN --- Function Scan on fnjobsperday (cost=0.00..12.50 rows=1000 width=697) (1 row) With the function, 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 Subquery Scan vw_document_pagesperjob (cost=82796.59..90149.20 rows=588209 width=706) -> Sort (cost=82796.59..84267.11 rows=588209 width=74) Sort Key: tblprintjobs.descpages, tblprintjobs.documentname -> Seq Scan on tblprintjobs (cost=0.00..26428.61 rows=588209 width=74) (4 rows) rvponp=# explain select * from vw_document_pagesperjob limit 10 ; QUERY PLAN -- Limit (cost=82796.59..82796.72 rows=10 width=706) -> Subquery Scan vw_document_pagesperjob (cost=82796.59..90149.20 rows=588209 width=706) -> Sort (cost=82796.59..84267.11 rows=588209 width=74) Sort Key: tblprintjobs.descpages, tblprintjobs.documentname -> Seq Scan on tblprintjobs (cost=0.00..26428.61 rows=588209 width=74) (5 rows) rvponp=# explain select documentname, eventdate, eventtime, loginuser, pages from tblPrintjobs order by descpages, documentname ; QUERY PLAN Sort (cost=81326.07..82796.59 rows=588209 width=74) Sort Key: descpages, documentname -> Seq Scan on tblprintjobs (cost=0.00..24958.09 rows=588209 width=74) (3 rows) rvponp=# explain select documentname, eventdate, eventtime, loginuser, pages from tblPrintjobs order by descpages, documentname limit 10 ; QUERY PLAN - Limit (cost=0.00..33.14 rows=10 width=74) -> Index Scan using ixprintjobspagesperjob on tblprintjobs (cost=0.00..1949116.68 rows=588209 width=74) (2 rows) x-tad-biggercreate or replace view vw_document_pagesperjob as select documentname, eventdate, eventtime, loginuser, fnFormatInt(pages) as pages 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 ? Can we see the output of the explain analyze? The definition of the view? Regards Russell Smith Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Updates on large tables are extremely slow
in 19382 pages DETAIL: 0 dead row versions cannot be removed yet. There were 75443 unused item pointers. 0 pages are entirely empty. CPU 3.43s/6.83u sec elapsed 97.86 sec. INFO: vacuuming pg_toast.pg_toast_2169880 INFO: index pg_toast_2169880_index now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: pg_toast_2169880: found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM rvponp=# On 13 Jun 2005, at 10:54, Mark Kirkwood wrote: 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 estimated total rows ANALYZE On 13 Jun 2005, at 04:43, Mark Kirkwood wrote: Yves Vindevogel wrote: I'm trying to update a table that has about 600.000 records. The update query is very simple : update mytable set pagesdesc = - pages ; The query takes about half an hour to an hour to execute. I have tried a lot of things. Half an hour seem a bit long - I would expect less than 5 minutes on reasonable hardware. You may have dead tuple bloat - can you post the output of 'ANALYZE VERBOSE mytable' ? 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Fwd: [PERFORM] Updates on large tables are extremely slow
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 machines. It's frustrating. Even Microsoft Access 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 are currently reusable. CPU 0.60s/0.31u sec elapsed 31.68 sec. INFO: index uxprintjobs now contains 622972 row versions in 3978 pages DETAIL: 9526 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.15s/0.48u sec elapsed 3.59 sec. INFO: index ixprintjobsipaddress now contains 622972 row versions in 2542 pages DETAIL: 9526 index row versions were removed. 49 index pages have been deleted, 0 are currently reusable. CPU 0.13s/0.24u sec elapsed 2.57 sec. INFO: index ixprintjobshostname now contains 622972 row versions in 2038 pages DETAIL: 9526 index row versions were removed. 35 index pages have been deleted, 0 are currently reusable. CPU 0.09s/0.30u sec elapsed 1.14 sec. INFO: index ixprintjobsrecordnumber now contains 622972 row versions in 1850 pages DETAIL: 9526 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.07s/0.28u sec elapsed 1.51 sec. INFO: index ixprintjobseventdate now contains 622972 row versions in 1408 pages DETAIL: 9526 index row versions were removed. 4 index pages have been deleted, 0 are currently reusable. CPU 0.05s/0.24u sec elapsed 2.61 sec. INFO: index ixprintjobseventtime now contains 622972 row versions in 1711 pages DETAIL: 9526 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.12s/0.53u sec elapsed 11.66 sec. INFO: index ixprintjobseventcomputer now contains 622972 row versions in 2039 pages DETAIL: 9526 index row versions were removed. 36 index pages have been deleted, 0 are currently reusable. CPU 0.12s/0.23u sec elapsed 1.27 sec. INFO: index ixprintjobseventuser now contains 622972 row versions in 2523 pages DETAIL: 9526 index row versions were removed. 19 index pages have been deleted, 0 are currently reusable. CPU 0.14s/0.24u sec elapsed 1.74 sec. INFO: index ixprintjobsloginuser now contains 622972 row versions in 2114 pages DETAIL: 9526 index row versions were removed. 13 index pages have been deleted, 0 are currently reusable. CPU 0.07s/0.32u sec elapsed 4.29 sec. INFO: index ixprintjobsprintqueue now contains 622972 row versions in 2201 pages DETAIL: 9526 index row versions were removed. 30 index pages have been deleted, 0 are currently reusable. CPU 0.10s/0.34u sec elapsed 1.92 sec. INFO: index ixprintjobsprintport now contains 622972 row versions in 3040 pages DETAIL: 9526 index row versions were removed. 40 index pages have been deleted, 0 are currently reusable. CPU 0.18s/0.27u sec elapsed 2.63 sec. INFO: index ixprintjobssize now contains 622972 row versions in 1733 pages DETAIL: 9526 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.16s/0.43u sec elapsed 4.07 sec. INFO: index ixprintjobspages now contains 622972 row versions in 1746 pages DETAIL: 9526 index row versions were removed. 24 index pages have been deleted, 0 are currently reusable. CPU 0.13s/0.22u sec elapsed 1.58 sec. INFO: index ixprintjobsapplicationtype now contains 622972 row versions in 1395 pages DETAIL: 9526 index row versions were removed. 27 index pages have been deleted, 0 are currently reusable. CPU 0.07s/0.29u sec elapsed 1.20 sec. INFO: index ixprintjobsusertype now contains 622972 row versions in 1393 pages DETAIL: 9526 index row versions were removed. 24 index pages have been deleted, 0 are currently reusable. CPU 0.07s/0.22u sec elapsed 0.82 sec. INFO: index ixprintjobsdocumentname now contains 622972 row versions in 4539 pages DETAIL: 9526 index row versions were removed. 6 index pages have been deleted, 0 are currently reusable. CPU 0.24s/0.38u sec elapsed 5.83 sec. INFO: index ixprintjobsdesceventdate now contains 622972 row versions in 1757 pages DETAIL: 9526 index row versions were removed. 4 index pages have been deleted, 0 are currently reusable. CPU 0.08s/0.25u sec elapsed 1.16 sec. INFO: index ixprintjobsdesceventtime now contains 622972 row versions in 1711 pages DETAIL: 9526 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.18s/0.52u sec elapsed 9.44 sec. INFO: index ixprintjobsdescpages now contains 622972 row versions in 1748 pages DETAIL: 9526 index row versions were removed. 24 index pages have been deleted, 0 are currently reusable. CPU 0.06s/0.26u sec elapsed 0.94 sec. INFO: index ixprintjobspagesperjob now contains
Re: [PERFORM] Updates on large tables are extremely slow
What else I don't understand is that an update is so slow, whereas this rvponp=# insert into tblTest (id, descpages) select oid, -pages from tblPrintjobs ; INSERT 0 622972 rvponp=# delete from tblTest ; DELETE 622972 rvponp=# takes about 1 minute for the insert, and 5 seconds for the delete. 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 machines. It's frustrating. Even Microsoft Access 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 are currently reusable. CPU 0.60s/0.31u sec elapsed 31.68 sec. INFO: index uxprintjobs now contains 622972 row versions in 3978 pages DETAIL: 9526 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.15s/0.48u sec elapsed 3.59 sec. INFO: index ixprintjobsipaddress now contains 622972 row versions in 2542 pages DETAIL: 9526 index row versions were removed. 49 index pages have been deleted, 0 are currently reusable. CPU 0.13s/0.24u sec elapsed 2.57 sec. INFO: index ixprintjobshostname now contains 622972 row versions in 2038 pages DETAIL: 9526 index row versions were removed. 35 index pages have been deleted, 0 are currently reusable. CPU 0.09s/0.30u sec elapsed 1.14 sec. INFO: index ixprintjobsrecordnumber now contains 622972 row versions in 1850 pages DETAIL: 9526 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.07s/0.28u sec elapsed 1.51 sec. INFO: index ixprintjobseventdate now contains 622972 row versions in 1408 pages DETAIL: 9526 index row versions were removed. 4 index pages have been deleted, 0 are currently reusable. CPU 0.05s/0.24u sec elapsed 2.61 sec. INFO: index ixprintjobseventtime now contains 622972 row versions in 1711 pages DETAIL: 9526 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.12s/0.53u sec elapsed 11.66 sec. INFO: index ixprintjobseventcomputer now contains 622972 row versions in 2039 pages DETAIL: 9526 index row versions were removed. 36 index pages have been deleted, 0 are currently reusable. CPU 0.12s/0.23u sec elapsed 1.27 sec. INFO: index ixprintjobseventuser now contains 622972 row versions in 2523 pages DETAIL: 9526 index row versions were removed. 19 index pages have been deleted, 0 are currently reusable. CPU 0.14s/0.24u sec elapsed 1.74 sec. INFO: index ixprintjobsloginuser now contains 622972 row versions in 2114 pages DETAIL: 9526 index row versions were removed. 13 index pages have been deleted, 0 are currently reusable. CPU 0.07s/0.32u sec elapsed 4.29 sec. INFO: index ixprintjobsprintqueue now contains 622972 row versions in 2201 pages DETAIL: 9526 index row versions were removed. 30 index pages have been deleted, 0 are currently reusable. CPU 0.10s/0.34u sec elapsed 1.92 sec. INFO: index ixprintjobsprintport now contains 622972 row versions in 3040 pages DETAIL: 9526 index row versions were removed. 40 index pages have been deleted, 0 are currently reusable. CPU 0.18s/0.27u sec elapsed 2.63 sec. INFO: index ixprintjobssize now contains 622972 row versions in 1733 pages DETAIL: 9526 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.16s/0.43u sec elapsed 4.07 sec. INFO: index ixprintjobspages now contains 622972 row versions in 1746 pages DETAIL: 9526 index row versions were removed. 24 index pages have been deleted, 0 are currently reusable. CPU 0.13s/0.22u sec elapsed 1.58 sec. INFO: index ixprintjobsapplicationtype now contains 622972 row versions in 1395 pages DETAIL: 9526 index row versions were removed. 27 index pages have been deleted, 0 are currently reusable. CPU 0.07s/0.29u sec elapsed 1.20 sec. INFO: index ixprintjobsusertype now contains 622972 row versions in 1393 pages DETAIL: 9526 index row versions were removed. 24 index pages have been deleted, 0 are currently reusable. CPU 0.07s/0.22u sec elapsed 0.82 sec. INFO: index ixprintjobsdocumentname now contains 622972 row versions in 4539 pages DETAIL: 9526 index row versions were removed. 6 index pages have been deleted, 0 are currently reusable. CPU 0.24s/0.38u sec elapsed 5.83 sec. INFO: index ixprintjobsdesceventdate now contains 622972 row versions in 1757 pages DETAIL: 9526 index row versions were removed. 4 index pages have been deleted, 0 are currently reusable. CPU 0.08s/0.25u sec elapsed 1.16 sec. INFO: index ixprintjobsdesceventtime now contains 622972 row versions in 1711 pages DETAIL: 9526 index row versions were removed. 0 index pages have been
Fwd: [PERFORM] Updates on large tables are extremely slow
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's only one index affected ! On 13 Jun 2005, at 16:32, Tom Lane wrote: Yves Vindevogel [EMAIL PROTECTED]> writes: rvponp=3D# vacuum verbose tblPrintjobs ; INFO: vacuuming public.tblprintjobs [ twenty-one different indexes on one table ] Well, there's your problem. You think updating all those indexes is free? It's *expensive*. Heed the manual's advice: avoid creating indexes you are not certain you need for identifiable commonly-used queries. (The reason delete is fast is it doesn't have to touch the indexes ... the necessary work is left to be done by VACUUM.) regards, tom lane 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smallerMet 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Updates on large tables are extremely slow
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 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's only one index affected ! No - all 21 are affected. MVCC creates a new row on disk. -- Richard Huxton Archonet Ltd 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Updates on large tables are extremely slow
I just dropped 19 of the 21 indexes. I just left the primary key constraint and my unique index on 3 fields ... I did a vacuum full and an analyse I just ran the query again some 20 minutes ago. Guess what It's still running !! So it's not that much faster for the moment. I just want 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: 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 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's only one index affected ! No - all 21 are affected. MVCC creates a new row on disk. -- Richard Huxton Archonet Ltd Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(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
[PERFORM] Updates on large tables are extremely slow
Hi, I'm trying to update a table that has about 600.000 records. The update query is very simple :update mytable set pagesdesc = - pages ; (I use pagesdesc to avoid problems with sort that have one field in ascending order and one in descending order. That was a problem I had a week ago) The query takes about half an hour to an hour to execute. I have tried a lot of things. This is my setup Linux Slackware 10.1 Postgres 8.0.1 My filesystem has EXT2 filesystem so I don't have journaling. My partition is mounted in fstab with the noatime option. I have tried to change some settings in $PGDATA/postgresql.conf. But that does not seem to matter a lot. I'm not even sure that file is being used. I ran KSysGuard when executing my query and I don't see my processor being used more than 20% The memory increases for the cache, but not for the app itself. My testsystem 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 Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Fwd: Index on table when using DESC clause
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 documentname and on pages, and one extra on documentname + pages However, when I query my db using for instance order by pages, documentname, it is very fast. If I use order by pages desc, documentname, it is not fast at all, like it is not using the index properly at all. How can I avoid this ? 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi. /x-tad-smallerMet 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(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
Re: [PERFORM] Fwd: Index on table when using DESC clause
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, documentname, it is very fast. If I use order by pages desc, documentname, it is not fast at all, like it is not using the index properly at all. Make an index on pages desc, documentname asc. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(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 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: http://www.implements.be x-tad-smaller First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi./x-tad-smaller ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
