Re: [PERFORM] [ADMIN] Raw vs Filesystem
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 postgresql? That's going to depend on a number of things: 1. Size of database 2. Usage patterns (many updates or mostly reads? single user or many?...) 3. What hardware you've got 4. What OS you're running. 5. How you've configured your hardware, OS and PG. There are some test results people have provided in the archives, but whether they apply to your setup is open to argument. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Nested Sets WAS: column size too large, is this a bug?
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 last few months.This list and Hackers are pretty much the only ones I read all of. Maybe I should get back on -SQL and we can compare move_tree functions :-) Did yours use a temp table, or some other means? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] column size too large, is this a bug?
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 Josh, real deep. :-)
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.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} 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] [ADMIN] Raw vs Filesystem
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 all looked good at one time or another. Ext3 has never been a leader for performance, though, so that's an easy elimination. 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. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] select slow?
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 SMALLINT NOT NULL, Descripcion CHARACTER VARYING(60) NOT NULL, RefContable NUMERIC, Estado CHAR(1) NOT NULL, FecRegistro DATE NOT NULL, CONSTRAINT EstadoBanco CHECK ((Estado = 'A') or (Estado = 'I')), PRIMARY KEY(CodBanco) ); select * from icc_m_banco where codbanco = 1; it tooks 13s from it's send until it's executed. explain analyze give me this result: explain analyze select * from icc_m_banco where codbanco = 1; Seq Scan on icc_m_banco (cost=0.00..1.06 rows=6 width=41) (actual time=7.94..7.96 rows=4 loops=1) Total runtime: 63.37 msec (2 rows) so i think its not a database problem (at least it's not all the problem), though it seems to me it is taking a lot of time executing this. am i right? any suggestions? _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Nested Sets WAS: column size too large, is this a bug?
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 another.
I have to admit to failing to follow -SQL over the last few months.This
list and Hackers are pretty much the only ones I read all of.
Maybe I should get back on -SQL and we can compare move_tree functions :-)
Did yours use a temp table, or some other means?
Nope, Greg Mullane and I worked out the math and came up with an algorithm of
sorts that we could apply to the tree when moving elements.
digs a little
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 :-)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Nested Sets WAS: column size too large, is this a bug?
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 approach. Seems like it could be very vunerable to order-of-exection issues. I'll start a GUIDE on it, people can post their various Nested Sets solutions. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [ADMIN] Raw vs Filesystem
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 defaults to Reiser but also allows XFS. I would suggest XFS. Sincerely, Joshua D. Drake -- 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 begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0034 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [ADMIN] Raw vs Filesystem
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: Don't 'kill -9' the postmaster
[PERFORM] LIKE should use index when condition doesn't include wildcard
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 --- -- Index Scan using person_pkey on person (cost=0.00..5.98 rows=1 width=84) (actual time=0.034..0.039 rows=1 loops=1) Index Cond: (userid = 'girgen'::text) Total runtime: 0.091 ms (3 rader) girgen=# explain analyze select * from person where userid like 'girgen'; QUERY PLAN --- --- Seq Scan on person (cost=0.00..77.08 rows=1 width=84) (actual time=1.137..1.143 rows=1 loops=1) Filter: (userid ~~ 'girgen'::text) Total runtime: 1.193 ms (3 rader) The result cannot be different between the two cases. The second query does not use the index since database is initiaized with a locale, sv_SE.ISO8859-1, and I need it for correct sorting. (Still dreaming about indices with like and locale)... But, since there is no wildcard in the string 'girgen', it should easily be able to use the index, if it only bothered to note that there is a wildcard around, right? Another thing on the same subject: I use an app that builds searches using some standard method, and it wants to always search case-insensitive. Hence, it uses ILIKE instead of `=', even for joins, and even for integers. This is a bit lazy, indeed, and also wrong. While this is wrong, no doubt, the odd thing I realized was that the optimizer didn't make use of the indices. Same thing here, the optimizer should ideally know that it is dealing with integers, where ILIKE and LIKE has no meaning, and it should use `=' instead implicitally, hence using indices. This one might be kind of low priority, but the one above really isn't, IMO. /Palle ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] LIKE should use index when condition doesn't include wildcard
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 around so that this special case is supported even with an index that doesn't otherwise support LIKE would complicate the code unduly IMHO, to support a rather pointless corner case... 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])
Re: [PERFORM] LIKE should use index when condition doesn't include
--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 have locale-insensitive indexes in place to support it. Switching the tests around so that this special case is supported even with an index that doesn't otherwise support LIKE would complicate the code unduly IMHO, to support a rather pointless corner case... OK, I agree. Sad, though, that throw away ability to use order by is the only way to get index scans using LIKE... :( But what about ILIKE. It does not take advantage of indices built with lower(): girgen=# create index person_foo on person (lower(last_name)); girgen=# vacuum analyze person; girgen=# explain select * from person where lower(last_name) = 'girgensohn'; QUERY PLAN --- -- Index Scan using person_foo on person (cost=0.00..137.58 rows=78 width=96) Index Cond: (lower(last_name) = 'girgensohn'::text) (2 rows) girgen=# explain select * from person where last_name = 'Girgensohn'; QUERY PLAN - Seq Scan on person (cost=0.00..441.35 rows=4 width=96) Filter: (last_name = 'Girgensohn'::text) (2 rows) girgen=# explain select * from person where lower(last_name) like 'girgen%'; QUERY PLAN --- --- Index Scan using person_foo on person (cost=0.00..137.58 rows=78 width=96) Index Cond: ((lower(last_name) = 'girgen'::text) AND (lower(last_name) 'girgeo'::text)) Filter: (lower(last_name) ~~ 'girgen%'::text) (3 rows) girgen=# explain select * from person where last_name ilike 'girgen%'; QUERY PLAN - Seq Scan on person (cost=0.00..441.35 rows=5 width=96) Filter: (last_name ~~* 'girgen%'::text) (2 rows) postgresql 7.4.2, freebsd 4.9 stable. /Palle ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] LIKE should use index when condition doesn't include
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 Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] LIKE should use index when condition doesn't include
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 use it mostly for lookups in reference lists with 100 items, where an index doesn't matter. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] LIKE should use index when condition doesn't include
--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 aware that it is equivalent to lower(). 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. /Palle ---(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] LIKE should use index when condition doesn't include
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 while lower() definitely is not. Even if that's just a bug, it's a big leap to assume that ILIKE is equivalent to LIKE on lower(). Think about Turkish i/I, German esstet (did I spell that right?), ch in various languages, etc etc. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
