Re: [PERFORM] [ADMIN] Raw vs Filesystem

2004-03-30 Thread Richard Huxton
On Monday 29 March 2004 22:56, Jaime Casanova wrote: ok. if i don't misunderstand you (english is not my mother tongue, so i can be wrong). your point is that speed is not necesarily performance, that's right. so, the real question is what is the best filesystem for optimal speed in

Re: [PERFORM] Nested Sets WAS: column size too large, is this a bug?

2004-03-30 Thread Josh Berkus
Robert, If you search the pgsql-sql archives you'll find some helpful threads on using nested sets in PostgreSQL, one in particular I was involved with was a generic move_tree function that enabled moving a node from one branch to another. I have to admit to failing to follow -SQL over the

Re: [PERFORM] column size too large, is this a bug?

2004-03-30 Thread Robert Treat
On Sunday 28 March 2004 14:25, Josh Berkus wrote: Andrew, I used to use the connect-by patch, but have since rewritten everything to use a nested set model. Cool! You're probably the only person I know other than me using nested sets in a production environment. You cut me deep there

Re: [PERFORM] [ADMIN] Raw vs Filesystem

2004-03-30 Thread Richard Huxton
On Tuesday 30 March 2004 17:43, Josh Berkus wrote: Jaime, Richard, That's going to depend on a number of things: There are some test results people have provided in the archives, but whether they apply to your setup is open to argument. True. On Linux overall, XFS, JFS, and Reiser have

[PERFORM] select slow?

2004-03-30 Thread Jaime Casanova
hi all, i have an amd athlon with 256 ram (i know, this is not a *real* server but my tables are small) i'm using vb6 (win98) with pgsql-7.3.4 (rh8) trough the psqlodbc. when i do a select in took long to execute, here is an example table icc_m_banco CREATE TABLE ICC_M_BANCO ( CodBanco

Re: [PERFORM] select slow?

2004-03-30 Thread Paul Thomas
On 30/03/2004 20:25 Jaime Casanova wrote: hi all, i have an amd athlon with 256 ram (i know, this is not a *real* server but my tables are small) i'm using vb6 (win98) with pgsql-7.3.4 (rh8) trough the psqlodbc. when i do a select in took long to execute, here is an example table icc_m_banco

Re: [PERFORM] Nested Sets WAS: column size too large, is this a bug?

2004-03-30 Thread Robert Treat
On Tuesday 30 March 2004 11:38, Josh Berkus wrote: Robert, If you search the pgsql-sql archives you'll find some helpful threads on using nested sets in PostgreSQL, one in particular I was involved with was a generic move_tree function that enabled moving a node from one branch to

Re: [PERFORM] Nested Sets WAS: column size too large, is this a bug?

2004-03-30 Thread Josh Berkus
Robert, http://archives.postgresql.org/pgsql-sql/2002-11/msg00355.php Seemed to work though someone else had posted yet another version after ours... and in fact the one posted is not exactly what I use now either :-) Hmmm ... I'd want to do a *lot* of testing before I trusted that

Re: [PERFORM] [ADMIN] Raw vs Filesystem

2004-03-30 Thread Joshua D. Drake
True, but on the sorts of commodity boxes I use, it doesn't make sense for me to waste time setting up non-standard filesystems - it's cheaper to spend a little more for better performance. I think SuSE offer Reiser though, so maybe we'll see a wider selection available by default. SuSE

Re: [PERFORM] [ADMIN] Raw vs Filesystem

2004-03-30 Thread Josh Berkus
Josh, SuSE defaults to Reiser but also allows XFS. I would suggest XFS. I've found Reiser to perform very well for databases with many small tables. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4:

[PERFORM] LIKE should use index when condition doesn't include wildcard

2004-03-30 Thread Palle Girgensohn
Hi, Shouldn't the optimizer use indices if the like condition does not have any wildcards? An example: girgen=# explain analyze select * from person where userid = 'girgen'; QUERY PLAN

Re: [PERFORM] LIKE should use index when condition doesn't include wildcard

2004-03-30 Thread Tom Lane
Palle Girgensohn [EMAIL PROTECTED] writes: Shouldn't the optimizer use indices if the like condition does not have any wildcards? I can't get excited about this; if you are depending on LIKE to be fast then you should have locale-insensitive indexes in place to support it. Switching the tests

Re: [PERFORM] LIKE should use index when condition doesn't include

2004-03-30 Thread Palle Girgensohn
--On tisdag, mars 30, 2004 19.16.44 -0500 Tom Lane [EMAIL PROTECTED] wrote: Palle Girgensohn [EMAIL PROTECTED] writes: Shouldn't the optimizer use indices if the like condition does not have any wildcards? I can't get excited about this; if you are depending on LIKE to be fast then you should

Re: [PERFORM] LIKE should use index when condition doesn't include

2004-03-30 Thread Josh Berkus
Palle, But what about ILIKE. It does not take advantage of indices built with lower(): Nope. If you want to use a functional index, you'll need to use the function when you call the query. ILIKE is not somehow aware that it is equivalent to lower(). -- -Josh Berkus Aglio Database

Re: [PERFORM] LIKE should use index when condition doesn't include

2004-03-30 Thread Josh Berkus
Palle, Too bad... that was my idea, that it would somehow be aware that it is equivalent to lower() like. It really is, isn't it? I would have though they where synonymous. If not, makes ILIKE kind of unusable, at least unless you're pretty certain the field will never indexed. Yup. I

Re: [PERFORM] LIKE should use index when condition doesn't include

2004-03-30 Thread Palle Girgensohn
--On tisdag, mars 30, 2004 16.56.09 -0800 Josh Berkus [EMAIL PROTECTED] wrote: Palle, But what about ILIKE. It does not take advantage of indices built with lower(): Nope. If you want to use a functional index, you'll need to use the function when you call the query. ILIKE is not somehow

Re: [PERFORM] LIKE should use index when condition doesn't include

2004-03-30 Thread Tom Lane
Josh Berkus [EMAIL PROTECTED] writes: ILIKE is not somehow aware that it is equivalent to lower(). Is it? Given the wild and wonderful behaviors of locales here and there, I wouldn't want to assume that such an equivalence holds. In particular I note that iclike() seems to be multibyte-aware