Re: [PERFORM] Trigger performance
Hello try prepared statements, PQexecPrepared http://developer.postgresql.org/docs/postgres/libpq-exec.html Regards Pavel Stehule On Thu, 22 Jan 2004, pginfo wrote: > Hi, > > thanks for the answer. > It is very interest, because I readet many times that if I write the trigger > in "C" it will work faster. > In wich case will this trigger work faster if write it in "C"? > In all my triggres I have "select " or "insert into mytable select ..." > or "update mytable set ...where...". > I need this info because I have a table with ~1.5 M rows and if I start to > update 300 K from this rows it takes ~ 2h. > If I remove the trigger for this table all the time is ~ 1 min. > > regards, > ivan. > > Tom Lane wrote: > > > pginfo <[EMAIL PROTECTED]> writes: > > > I was supprised that the pgsql trigger take ~8 sec. to insert this rows > > > and the "C" trigger take ~ 17 sec. > > > > The reason is that plpgsql caches the plan for the invoked SELECT, > > whereas the way you coded the C function, it's re-planning that SELECT > > on every call. > > > > regards, tom lane > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > > ---(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 > ---(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
Re: [PERFORM] Postgres on Netapp
D'Arcy J.M. Cain wrote: > With the price of GigE adapters I wouldn't consider anything else. > > I have a huge database that takes about an hour to copy. The netApp snapshot > feature is very nice because I can get a "moment in time" image of the > database. Even though I can't run from the snapshot because it is read only > (*) and PG needs to write to files just to open the database, I can copy it > and get a runnable version of the DB. If I copy directly from the original I > can get many changes while copying and wind up with a copy that will not run. > > (*): It would be nice if PG had a flag that allowed a database to be opened in > read only mode without touching anything in the directory. PostgreSQL has to read the WAL to adjust the contents of the flat file on startup in such a setup, so I don't see how we could do it read-only. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] High Performance/High Reliability File system on SuSE64
Hello All Just wanted to gather opinions on what file system has the best balance between performance and reliability when used on a quad processor machine running SuSE64. Thanks DAve
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
Dave Thompson wrote: Hello All Just wanted to gather opinions on what file system has the best balance between performance and reliability when used on a quad processor machine running SuSE64. Thanks XFS.. hands down. DAve -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
[PERFORM] Optimizing SQL: bind variables, prepared stms, histograms
A few question regarding PostgreSQL handling of queries: - Is each query submitted parsed and planned even if it is identical to a query submitted before? For example, 10 queries "select * from animals where id=:b1" with possibly different bind variable :b1 values will be fully processed (parsed and planned) 10 times? - does it make difference for postgreSQL performance if bind variables are used or not? Does it make difference in performance if the same prepared statement is used just with different values of bind variables? - Does postgreSQL optimizer account for statistics like histograms when bind variables are used (i.e. try to built a new plan given a concrete value of bind variable)? Thank you in advance, Laimis ---(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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
On Fri, Jan 23, 2004 at 08:51:03AM -0800, Joshua D. Drake wrote: > > > XFS.. hands down. I thought it was you who recently said you thought there was some sort of possible caching problem there? A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
XFS.. hands down. I thought it was you who recently said you thought there was some sort of possible caching problem there? Not I. We have had issues with JFS and data corruption on a powerout but XFS has been rock solid in all of our tests. XFS also has the interesting ability (although I have yet to test it) that will allow you to take a snapshot of the filesystem. Thus you can have filesystem level backups of the PGDATA directory that are consistent even though the database is running. There is nothing else on Linux that comes close to that. Plus XFS has been proven in a 64 bit environment (Irix). Sincerely, Joshua D. Drake A -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] help with dual indexing
I've got a table with about 10 million events in it. Each has a user_id (about 1000 users) and a event_time timestamp covering a 4 year period with about 50% of the events being in the last year. Some users have only dozens of events. A few have hundreds of thousands. The queries usually are in the form of, where "user_id = something and event_time between something and something". Half of my queries index off of the user_id and half index off the event_time. I was thinking this would be a perfect opportunity to use a dual index of (user_id,event_time) but I'm confused as to weather this will help considering the size of this index given that there very few tuples that have the exact same timestamp as another and I'm not sure which order to put the user_id/event_time as I don't know what is meant when people on this list ask which is more selective. Also, would it make sense for me to raise my ANALYZE value and how would I go about doing this? Thanks for the help. -- Orion Henry <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
On Fri, Jan 23, 2004 at 10:18:35AM -0800, Joshua D. Drake wrote: > > > Not I. We have had issues with JFS and data corruption on a powerout but > XFS has been rock solid in all of our tests. Sorry, it was Josh Berkus: http://archives.postgresql.org/pgsql-performance/2004-01/msg00086.php > There is nothing else on Linux that comes close to that. Plus XFS has been > proven in a 64 bit environment (Irix). I had lots of happy experiences with XFS when administering IRIX boxes[1], but I don't know what differences the Linux port entailed. Do you have details on that? We're certainly looking for an option over JFS at the moment. A [1] I will note, however, that it was practically the only happy experience I had with them. IRIX made the early Debian installer look positively user-friendly, and SGI's desire to make everything whiz-bang nifty by running practically every binary setuid root gave me fits. But XFS was nice. -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
There is nothing else on Linux that comes close to that. Plus XFS has been proven in a 64 bit environment (Irix). I had lots of happy experiences with XFS when administering IRIX boxes[1], but I don't know what differences the Linux port entailed. Do you have details on that? http://oss.sgi.com/projects/xfs/ We're certainly looking for an option over JFS at the moment. A [1] I will note, however, that it was practically the only happy experience I had with them. IRIX made the early Debian installer look positively user-friendly, and SGI's desire to make everything whiz-bang nifty by running practically every binary setuid root gave me fits. But XFS was nice. -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
On Fri, Jan 23, 2004 at 11:05:41AM -0800, Joshua D. Drake wrote: > http://oss.sgi.com/projects/xfs/ Yes, I guess I shoulda thought of that, eh? Thanks. The docs do suggest that there are some significant differences between the two versions of the filesystem, so I'm not sure how sanguine I'd be about the degree of "testing" the filesystem has received on Linux. On the other hand, I wouldn't be surprised if it were no worse than the other options. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
Yes, I guess I shoulda thought of that, eh? Thanks. The docs do suggest that there are some significant differences between the two versions of the filesystem, so I'm not sure how sanguine I'd be about the degree of "testing" the filesystem has received on Linux. On the Well SuSE ships with XFS and SuSE tends to be really good about testing. Better than RedHat IMHO. Just the fact that RedHat uses ext3 as the default is a black eye. XFS has been around a LONG time, and on Linux for a couple of years now. Plus I believe it is the default FS for all of the really high end stuff SGI is doing with Linux. I would (and do) trust XFS currently over ANY other journalled option on Linux. Sincerely, Joshua D. Drake other hand, I wouldn't be surprised if it were no worse than the other options. A -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL ---(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
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
[EMAIL PROTECTED] ("Joshua D. Drake") writes:
>>Yes, I guess I shoulda thought of that, eh? Thanks. The docs do
>>suggest that there are some significant differences between the two
>>versions of the filesystem, so I'm not sure how sanguine I'd be about
>>the degree of "testing" the filesystem has received on Linux. On the
>>
> Well SuSE ships with XFS and SuSE tends to be really good about
> testing. Better than RedHat IMHO. Just the fact that RedHat uses
> ext3 as the default is a black eye.
Well, I'd point to one major factor with RHAT; they employ Stephen
Tweedie, creator of ext3, and have been paying him to work on it for
some time now. If they _didn't_ promote use of ext3, they would be
very much vulnerable to the "won't eat their own dogfood" criticism.
> XFS has been around a LONG time, and on Linux for a couple of years
> now. Plus I believe it is the default FS for all of the really high
> end stuff SGI is doing with Linux.
Ah, but there is a bit of a 'problem' nonetheless; XFS is not
'officially supported' as part of the Linux kernel until version 2.6,
which is still pretty "bleeding edge." Until 2.6 solidifies a bit
more (aside: based on experiences with 2.6.0, "quite a lot more"), it
is a "patchy" add-on to the 'stable' 2.4 kernel series.
Do the patches work? As far as I have heard, quite well indeed. But
the fact of it not having been 'official' is a fair little bit of a
downside.
> I would (and do) trust XFS currently over ANY other journalled
> option on Linux.
I'm getting less and less inclined to trust ext3 or JFS, which "floats
upwards" any other boats that are lingering around...
--
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
Well, I'd point to one major factor with RHAT; they employ Stephen Tweedie, creator of ext3, and have been paying him to work on it for some time now. If they _didn't_ promote use of ext3, they would be very much vulnerable to the "won't eat their own dogfood" criticism. True but frankly, they shouldn't. EXT3 has some serious issues. In fact if you are running a stock RH kernel before 2.4.20 you can destroy your PostgreSQL database with it. Not to mention how slow it is ;) XFS has been around a LONG time, and on Linux for a couple of years now. Plus I believe it is the default FS for all of the really high end stuff SGI is doing with Linux. Ah, but there is a bit of a 'problem' nonetheless; XFS is not 'officially supported' as part of the Linux kernel until version 2.6, which is still pretty "bleeding edge." That is not true see: http://kerneltrap.org/node/view/1751 Until 2.6 solidifies a bit more (aside: based on experiences with 2.6.0, "quite a lot more"), it is a "patchy" add-on to the 'stable' 2.4 kernel series. Again see above :) Do the patches work? As far as I have heard, quite well indeed. But the fact of it not having been 'official' is a fair little bit of a downside. What is official? Sincerely, Joshua D. Drake I would (and do) trust XFS currently over ANY other journalled option on Linux. I'm getting less and less inclined to trust ext3 or JFS, which "floats upwards" any other boats that are lingering around... -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] Slow delete times??
Please tell me if this timing makes sense to you for a Celeron 433 w/
RAM=256MB dedicated testing server. I expected some slowness, but not this
high.
db_epsilon=# \d t_active_subjects
Table "public.t_active_subjects"
Column | Type | Modifiers
+--+
id | integer | not null default
nextval('public.t_active_subjects_id_seq'::text)
old_id | integer |
ext_subject| integer | not null
ext_group | integer |
final_grade| integer |
type | character(1) |
ree| date |
borrado| boolean |
ext_active_student | integer |
sum_presences | integer |
sum_hours | integer |
Indexes: t_active_subjects_pkey primary key btree (id),
i_t_active_subjects__ext_active_student btree (ext_active_student),
i_t_active_subjects__ext_group btree (ext_group),
i_t_active_subjects__ext_subject btree (ext_subject),
i_t_active_subjects__old_id btree (old_id)
Foreign Key constraints: $4 FOREIGN KEY (ext_group) REFERENCES
t_groups(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$3 FOREIGN KEY (ext_subject) REFERENCES
t_subjects(id) ON UPDATE NO ACTION ON DELETE NO
ACTION
db_epsilon=# EXPLAIN DELETE FROM t_active_subjects;
QUERY PLAN
-
Seq Scan on t_active_subjects (cost=0.00..3391.73 rows=52373 width=6)
(1 row)
db_epsilon=# EXPLAIN ANALYZE DELETE FROM t_active_subjects;
QUERY PLAN
Seq Scan on t_active_subjects (cost=0.00..3391.73 rows=52373 width=6)
(actual time=0.11..4651.82 rows=73700 loops=1)
Total runtime: 3504528.15 msec
(2 rows)
db_epsilon=# SELECT version();
version
-
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)
[EMAIL PROTECTED] data]# cat postgresql.conf | grep -v \# | grep \=
tcpip_socket = true
fsync = false
LC_MESSAGES = 'en_US.UTF-8'
LC_MONETARY = 'en_US.UTF-8'
LC_NUMERIC = 'en_US.UTF-8'
LC_TIME = 'en_US.UTF-8'
Okay, some details:
* The query takes to run about 3,504.52815 sec for 52,373 rows, which
averages about 15 deletes per second.
* Each ext_* field is a foreign key to another table's pk.
* This is a dedicated testing server with 256 MB RAM, and is a Celeron
433 MHz. It still has enough disk space, I think: about 200 MB.
* Disk is 4 MB. I guess it must be about what, 4500 RPM?
* fsync is disabled.
I don't know what other info to provide...
Thanks in advance.
--
Octavio Alvarez Piza.
E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Slow delete times??
Octavio Alvarez wrote:
Please tell me if this timing makes sense to you for a Celeron 433 w/
RAM=256MB dedicated testing server. I expected some slowness, but not this
high.
Well delete is generally slow. If you want to delete the entire table
(and your really sure)
use truncate.
J
db_epsilon=# \d t_active_subjects
Table "public.t_active_subjects"
Column | Type | Modifiers
+--+
id | integer | not null default
nextval('public.t_active_subjects_id_seq'::text)
old_id | integer |
ext_subject| integer | not null
ext_group | integer |
final_grade| integer |
type | character(1) |
ree| date |
borrado| boolean |
ext_active_student | integer |
sum_presences | integer |
sum_hours | integer |
Indexes: t_active_subjects_pkey primary key btree (id),
i_t_active_subjects__ext_active_student btree (ext_active_student),
i_t_active_subjects__ext_group btree (ext_group),
i_t_active_subjects__ext_subject btree (ext_subject),
i_t_active_subjects__old_id btree (old_id)
Foreign Key constraints: $4 FOREIGN KEY (ext_group) REFERENCES
t_groups(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$3 FOREIGN KEY (ext_subject) REFERENCES
t_subjects(id) ON UPDATE NO ACTION ON DELETE NO
ACTION
db_epsilon=# EXPLAIN DELETE FROM t_active_subjects;
QUERY PLAN
-
Seq Scan on t_active_subjects (cost=0.00..3391.73 rows=52373 width=6)
(1 row)
db_epsilon=# EXPLAIN ANALYZE DELETE FROM t_active_subjects;
QUERY PLAN
Seq Scan on t_active_subjects (cost=0.00..3391.73 rows=52373 width=6)
(actual time=0.11..4651.82 rows=73700 loops=1)
Total runtime: 3504528.15 msec
(2 rows)
db_epsilon=# SELECT version();
version
-
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)
[EMAIL PROTECTED] data]# cat postgresql.conf | grep -v \# | grep \=
tcpip_socket = true
fsync = false
LC_MESSAGES = 'en_US.UTF-8'
LC_MONETARY = 'en_US.UTF-8'
LC_NUMERIC = 'en_US.UTF-8'
LC_TIME = 'en_US.UTF-8'
Okay, some details:
* The query takes to run about 3,504.52815 sec for 52,373 rows, which
averages about 15 deletes per second.
* Each ext_* field is a foreign key to another table's pk.
* This is a dedicated testing server with 256 MB RAM, and is a Celeron
433 MHz. It still has enough disk space, I think: about 200 MB.
* Disk is 4 MB. I guess it must be about what, 4500 RPM?
* fsync is disabled.
I don't know what other info to provide...
Thanks in advance.
--
Octavio Alvarez Piza.
E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] help with dual indexing
Orion Henry <[EMAIL PROTECTED]> writes: > The queries usually are in the form of, where "user_id = something and > event_time between something and something". > Half of my queries index off of the user_id and half index off the > event_time. I was thinking this would be a perfect opportunity to use a > dual index of (user_id,event_time) but I'm confused as to weather this > will help Probably. Put the user_id as the first column of the index --- if you think about the sort ordering of a multicolumn index, you will see why. With user_id first, a constraint as above describes a contiguous subrange of the index; with event_time first it does not. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
You can do snapshots in FreeBSD 5.x with UFS2 as well but that ( nor XFS snapshots ) will let you backup with the database server running. Just because you will get the file exactly as it was at a particular instant does not mean that the postmaster did not still have some some data that was not flushed to disk yet. Ahh... isn't that what fsync is for? -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] query slows under load
Hi, Sorry for the long e-mail. Here is a summary of my questions: I am running osdl-dbt1 against pgsql-7.3.3. The result is at: http://khack.osdl.org/stp/286627/ 1. Based on the hardware and software configuration, does my database configuration make sense? 2. Is 'defining a cursor and fetch multiple times' an efficient way to implement a stored procedure? 3. index with desc/asc is not supported in PG, why it is not needed? Is there any work-around? 4. I created a function to order the items, and created an index on that function. But the query did not pick up that index. What did I miss? Thanks, = The I/O is light <10% disk utility, memory is 100% used, and CPU is about 75%. My goal is to increase CPU utilization to about 85% (without swapping). I've tried several database parameters and it did not make much difference, I can get about 86 transactions/second. Since the same workload on SAPDB gives about 200 transactions/second, I must have missed some important parameters. So, the first question is: Based on the hardware and software configuration, does my database configuration make sense? My statistics showed that one transaction is responsible for the bad performance. It takes 3-5 seconds to finish this transaction. The storedprocedure for this transaction executes select and fetches 20 times if there is record: OPEN refcur FOR SELECT i_id, i_title, a_fname, a_lname FROM item, author WHERE i_subject = _i_subject AND i_a_id = a_id ORDER BY i_pub_date DESC, i_title ASC; FETCH refcur INTO _i_id1, i_title1, a_fname1, a_lname1; --RAISE NOTICE ''%,%,%,%'', _i_id1, i_title1, a_fname1, a_lname1; IF FOUND THEN items := items + 1; FETCH refcur INTO _i_id2, i_title2, a_fname2, a_lname2; END IF; IF FOUND THEN items := items + 1; FETCH refcur INTO _i_id3, i_title3, a_fname3, a_lname3; END IF; ... The second question is: Is this the efficient way to implement? The execution plan for the query is: > explain analyze select i_id, i_title, a_fname, a_lname from item, author where i_subject = 'ART' AND i_a_id = 1 ORDER BY i_pub_date DESC, i_title ASC; QUERY PLAN - Sort (cost=33.95..34.57 rows=250 width=103) (actual time=0.44..0.44 rows=0 loops=1) Sort Key: item.i_pub_date, item.i_title -> Nested Loop (cost=0.00..23.99 rows=250 width=103) (actual time=0.29..0.29 rows=0 loops=1) -> Index Scan using i_i_subject on item (cost=0.00..5.99 rows=1 width=64) (actual time=0.29..0.29 rows=0 loops=1) Index Cond: (i_subject = 'ART'::character varying) Filter: (i_a_id = 1::numeric) -> Seq Scan on author (cost=0.00..15.50 rows=250 width=39) (never executed) Total runtime: 0.57 msec (8 rows) I think an index on item (i_pub_date desc, i_title asc) would help. But from reading the mailing list, PG does not have this kind of index, and it is not needed (I could not find an answer for this). Is there any work-around? I created an function to cache the order and created an index on it, but the query did not pick it up. Do I need to rewrite the query? create or replace function item_order (varchar(60)) returns numeric(10) as ' DECLARE _i_subject alias for $1; _i_id numeric(10); rec record; BEGIN select i_id into _i_id from item where i_subject=_i_subject order by i_pub_date DESC, i_title ASC; return _i_id; END; 'IMMUTABLE LANGUAGE 'plpgsql'; create index i_item_order on item (item_order(i_subject)); TIA, -- Jenny Zhang Open Source Development Lab 12725 SW Millikan Way, Suite 400 Beaverton, OR 97005 (503)626-2455 ext 31 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Slow delete times??
"Octavio Alvarez" <[EMAIL PROTECTED]> writes: > Please tell me if this timing makes sense to you for a Celeron 433 w/ > RAM=256MB dedicated testing server. I expected some slowness, but not this > high. I'll bet you have foreign keys referencing this table, and the referencing columns do not have indexes. PG will let you do that ... but it makes updates and deletes horribly slow. You generally want to add those indexes. If they *are* indexed, check for datatype mismatches. That's another thing that kills performance ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] High Performance/High Reliability File system on SuSE64
They seem pretty clean (have patched vanilla kernels + xfs for Mandrake 9.2/9.0). And yes, I would recommend xfs - noticeably faster than ext3, and no sign of any mysterious hangs under load. best wishes Mark Christopher Browne wrote: Do the patches work? As far as I have heard, quite well indeed. But the fact of it not having been 'official' is a fair little bit of a downside. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
