Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread PFC
It would also be reasonable to allow clustering individual partitions; creating table or column constraints on some partitions and not others; I have a session mamagement which works like that, using views now. sessions.online is a table of the online sessions. It has a UNIQUE on user_id.

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread Tom Lane
Greg Stark [EMAIL PROTECTED] writes: So I think Phase I should look like: An ALTER TABLE command to make an inherited table abstract in the object oriented sense. That is, no records can be inserted in the parent table. If you follow the oracle model this is also where you specify the

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread Stacy White
From: Tom Lane [EMAIL PROTECTED] Alvaro Herrera [EMAIL PROTECTED] writes: We probably also need multi-table indexes. As Josh says, that seems antithetical to the main point of partitioning, which is to be able to rapidly remove (and add) partitions of a table. If you have to do index

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread Josh Berkus
Alvaro, Greg, Tom, Hmm. You are right, but without that we won't be able to enforce uniqueness on the partitioned table (we could only enforce it on each partition, which would mean we can't partition on anything else than primary keys if the tables have one). IMHO this is something to

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread Oleg Bartunov
On Sun, 20 Mar 2005, Josh Berkus wrote: whole system make a lot more sense: individual partitions are really tables. The partitioned tables themselves are just meta-objects like views. If partition is a table, so I could define different indices for them ? In our prototype of scaled full text

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: A command to remove a partition from the partitioned table and turn it into a regular table. Ugh. Why? You can access the table directly anyway. A command to take a regular table and turn it into a partition. Double ugh. Verifying that

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread Tom Lane
Stacy White [EMAIL PROTECTED] writes: FWIW, we see large benefits from partitioning other than the ability to easily drop data, for example: - We can vacuum only the active portions of a table - Postgres automatically keeps related records clustered together on disk, which makes it more

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread Tom Lane
Greg Stark [EMAIL PROTECTED] writes: You could argue for some sort of setup where you could take a partition offline during which you could safely do things like export or manipulate the data. But that's awfully limiting. What if I want to do things like add columns, or change data types, or

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread Greg Stark
Josh Berkus josh@agliodbs.com writes: Well, I would think that specifying an expression that defines a new partition at each change in value (like EXTRACT(day FROM timestamp) on a time-based partitioning) would cover 90% of implemenations and be a lot simpler to administer. The Oracle

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: Global indexes would seriously reduce the performance of both vacuum and cluster for a single partition, and if you want seq scans you don't need an index for that at all. So the above doesn't strike me as a strong argument for global indexes ... I think

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: You could argue for some sort of setup where you could take a partition offline during which you could safely do things like export or manipulate the data. But that's awfully limiting. What if I want to do things like

[PERFORM] View vs function

2005-03-20 Thread Keith Worthington
Hi All, I have been reading about set returning functions. What I would like to know is is there a performance advantage in using SRFs versus querying a view. Assuming the underlying SQL is the same for the view vs the function except for the WHERE clause which of these would you expect to

Re: [PERFORM] View vs function

2005-03-20 Thread Bruno Wolff III
On Sun, Mar 20, 2005 at 22:39:57 -0500, Keith Worthington [EMAIL PROTECTED] wrote: Hi All, I have been reading about set returning functions. What I would like to know is is there a performance advantage in using SRFs versus querying a view. Assuming the underlying SQL is the same for

Re: [PERFORM] Postgres on RAID5

2005-03-20 Thread David Dougall
In my experience, if you are concerned about filesystem performance, don't use ext3. It is one of the slowest filesystems I have ever used especially for writes. I would suggest either reiserfs or xfs. --David Dougall On Fri, 11 Mar 2005, Arshavir Grigorian wrote: Hi, I have a RAID5 array

Effect of Stripe Size (was [PERFORM] Postgres on RAID5)

2005-03-20 Thread Ruth Ivimey-Cook
Folks, You said: If your write size is smaller than chunk_size*N (N = number of data blocks in a stripe), in order to calculate correct parity you have to read data from the remaining drives. Neil explained it in this message:

Re: [PERFORM] Postgres on RAID5

2005-03-20 Thread Michael Tokarev
David Dougall wrote: In my experience, if you are concerned about filesystem performance, don't use ext3. It is one of the slowest filesystems I have ever used especially for writes. I would suggest either reiserfs or xfs. I'm a bit afraid to start yet another filesystem flamewar, but. Please

[PERFORM] Which one is faster: one way reading =single pass reading

2005-03-20 Thread Rosny
Hi, Which one is faster: one way reading =single pass reading Assumption : a. Need to have 3 millions records b. Need to have call 10 or 20 records repeatly (so for database it will be 10 times connection, each connection with one record. or can be fancy 1 connection call return 10 sets of

Re: [PERFORM] Questions about 2 databases.

2005-03-20 Thread Mirko Zeibig
jelle wrote: The insert heavy sessions average 175 page hits generating XML, 1000 insert/updates which comprise 90% of the insert/update load, of which 200 inserts need to be transferred to the master db. The other sessions are read/cache bound. I hoping to get a speed-up from moving the

[PERFORM] Hardware impact on performances

2005-03-20 Thread Camille Chafer
Hi, I'm using PostgreSQL 8 for a mmorpg. The part of each operation is : select: 50%, update: 40%, insert: 10%. I have no more than 4-5 concurrent connections to the database, but each of them does A LOT of queries (several per second). The database size is about 1GB, but it'll probably be

Re: [PERFORM] interesting benchmarks PG/Firebird Linux/Windows fsync/nofsync

2005-03-20 Thread Qingqing Zhou
Bruce Momjian pgman@candle.pha.pa.us writes Yes, we now enable FILE_FLAG_WRITE_THROUGH on Win32 for open_sync and I am about to open a discussion whether this should be the default for Win32, and whether we should backpatch this to 8.0.X. Just a short msg: Oracle/SQL Server enable it as

Re: [PERFORM] cpu_tuple_cost

2005-03-20 Thread Daniel Schuchardt
Tom Lane wrote: Reducing random_page_cost is usually the best way to get the planner to favor indexscans more. Ok, I tried a bit with random_page_cost and I have set it to 1 to become PG using the index on mitpln: CIMSOFT=# ANALYSE mitpln; ANALYZE CIMSOFT=# SET random_page_cost=2; SET CIMSOFT=#

Re: [PERFORM] Postgres on RAID5

2005-03-20 Thread Michael Tokarev
Arshavir Grigorian wrote: Alex Turner wrote: [] Well, by putting the pg_xlog directory on a separate disk/partition, I was able to increase this rate to about 50 or so per second (still pretty far from your numbers). Next I am going to try putting the pg_xlog on a RAID1+0 array and see if that

Re: [PERFORM] Postgres on RAID5 (possible sync blocking read type

2005-03-20 Thread David Greaves
Greg Stark wrote: Arshavir Grigorian [EMAIL PROTECTED] writes: Hi, I have a RAID5 array (mdadm) with 14 disks + 1 spare. This partition has an Ext3 filesystem which is used by Postgres. People are going to suggest moving to RAID1+0. I'm unconvinced that RAID5 across 14 drivers shouldn't

Re: [PERFORM] column name is LIMIT

2005-03-20 Thread Qingqing Zhou
So is it to make SQL parser context-sensitive - say the parser will understand that in statement SELECT * from LIMIT, LIMIT is just a table name, instead of keyword? There might be some conflicts when using Yacc, but I am not sure how difficult will be ... Cheers, Qingqing Christopher

Re: [PERFORM] Postgres on RAID5

2005-03-20 Thread Guy
You said: If your write size is smaller than chunk_size*N (N = number of data blocks in a stripe), in order to calculate correct parity you have to read data from the remaining drives. Neil explained it in this message: http://marc.theaimsgroup.com/?l=linux-raidm=108682190730593w=2 Guy

Re: [PERFORM] View vs function

2005-03-20 Thread Neil Conway
Bruno Wolff III wrote: Functions are just black boxes to the planner. ... unless the function is a SQL function that is trivial enough for the planner to inline it into the plan of the invoking query. Currently, we won't inline set-returning SQL functions that are used in the query's

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread Stacy White
From: Tom Lane [EMAIL PROTECTED] Stacy White [EMAIL PROTECTED] writes: FWIW, we see large benefits from partitioning other than the ability to easily drop data, for example: - We can vacuum only the active portions of a table - Postgres automatically keeps related records clustered

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-20 Thread Stacy White
From: Greg Stark [EMAIL PROTECTED] Tom Lane [EMAIL PROTECTED] writes: Not as good as pruning partitions entirely but if you're doing a sequential scan the performance hit of a few index lookups isn't a problem. Greg, I think you've got the right idea. For large databases, though, it won't be

Re: [PERFORM] View vs function

2005-03-20 Thread Tom Lane
Neil Conway [EMAIL PROTECTED] writes: Bruno Wolff III wrote: Functions are just black boxes to the planner. ... unless the function is a SQL function that is trivial enough for the planner to inline it into the plan of the invoking query. Currently, we won't inline set-returning SQL