Re: [PERFORM] Query running slower than same on Oracle

2003-06-25 Thread Josh Berkus
is, for reasons discussed on this list ad nauseum. However, it also looks from the queries like you forgot to index your foriegn keys. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe

Re: [PERFORM] Memory question

2003-06-27 Thread Josh Berkus
, since it includes the kernel buffer which tries to take up all available memory. If you actually look at the list of processes, I think you'll find that you're only using 1-2% of memory for applications. I'm not sure what app would show your real free memory. -- -Josh Berkus Aglio Database

Re: [PERFORM] PostgreSQL vs. MySQL

2003-07-04 Thread Josh Berkus
Brian, Howdy! I'm Josh Berkus, I'm also on the Core Team for PostgreSQL, and I wanted to give some closure on your issue before you quit with a bad taste in your mouth. Your posting hit a sore point in the collective PostgreSQL community, so you got a strong reaction from several people

Re: [PERFORM] Sanity check requested

2003-07-17 Thread Josh Berkus
joins ... and a pooled connection could use many times sort_mem depending on activity. Thus connection pooling does not help you with sort_mem usage at all, unless your pooling mechanism can control the rate at which queries are fed to the planner. -- Josh Berkus Aglio Database Solutions San

[PERFORM] Table clustering -- useful, or not?

2003-07-17 Thread Josh Berkus
the feature. When I re-vamp my articles on indexing, I would like to include something about clustered indexes. Can people give me some examples of cases where they have found clustered indexes to be useful, preferably with some statistics? -- -Josh Berkus Aglio Database Solutions San

Re: [PERFORM] Sanity check requested

2003-07-18 Thread Josh Berkus
. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] Tuning PostgreSQL

2003-07-21 Thread Josh Berkus
, it performs around 40% of a single scsi disk for large read-write operation on Postgres. If you have only 3 disks, I'd advocate one disk for WAL and one RAID 1 array for the database. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast

[PERFORM] Commenting postgresql.conf

2003-07-21 Thread Josh Berkus
Folks, There was a general consensus (I think) on this list that we want more verbose comments in postgresql.conf for 7.4. Is anyone available to do the work? We'll need the patch this week ... -- -Josh Berkus Aglio Database Solutions San Francisco ---(end

Re: [PERFORM] Wrong plan or what ?

2003-07-22 Thread Josh Berkus
(warning: this will significantly increase the time required to do ANALYZE) Then test again! -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http

Re: [PERFORM] Wrong plan or what ?

2003-07-22 Thread Josh Berkus
for the above query? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] hardware performance and some more

2003-07-25 Thread Josh Berkus
dispacting middleware that parcels out requests among the servers, but works very well for the java-based company that's using it. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] index questions

2003-07-25 Thread Josh Berkus
. You can then EXECUTE the query and loop through it for a result. Of course, YMMV. My approach will require you to create more indexes which could be a problem if you have limited disk space. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end

Re: [PERFORM] Mapping a database completly into Memory

2003-07-27 Thread Josh Berkus
. Also, don't forget to raise your effective_cache_size so that PostgreSQL *knows* that you have lots of RAM. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL

Re: [PERFORM] Mapping a database completly into Memory

2003-07-28 Thread Josh Berkus
and rely completely on the OS cache. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Optimization

2003-07-28 Thread Josh Berkus
Justin, I am trying to understand the various factors used by Postgres to optimize. I presently have a dual-866 Dell server with 1GB of memory. I've done the following: Please set the performance articles at: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php -- -Josh Berkus

Re: [PERFORM] Optimization

2003-07-28 Thread Josh Berkus
not lock your database. You will also want to increase your FSM_relations so that VACUUM is more effective/efficient; again, see the articles. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting

Re: [PERFORM] Rebuild indexes

2003-07-28 Thread Josh Berkus
it without worrying that any updates will get turned back. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

[PERFORM] Getting Started Guide?

2003-07-30 Thread Josh Berkus
Folks, Sorry for the cross-posting! Somebody approached me with the skeleton of a Gettting started with PostgreSQL page, and now I can't find the e-mail. Who was it? Please send again! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast

Re: [PERFORM] How number of columns affects performance

2003-08-01 Thread Josh Berkus
of developers who make questionable design decisions for performance reasons without every verifying that they were, in fact, improving performance ... -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain

Re: [PERFORM] How number of columns affects performance

2003-08-01 Thread Josh Berkus
Ron, You snipped out too much, because that's exactly what I said... Another way of writing it: only split the table if some of the fields are not unitary to the entity. Sorry! No offense meant. -- Josh Berkus Aglio Database Solutions San Francisco ---(end

Re: [PERFORM] How number of columns affects performance

2003-08-01 Thread Josh Berkus
and can be grouped together, and spin those off into a sub-table. That is, if you have 4 columns which are generally either all null or all filled, and are all null for 70% of records then those 4 could make a nice child table. -- -Josh Berkus __AGLIO DATABASE

Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec

2003-08-01 Thread Josh Berkus
= cont_publicacion.id_contenido AND cp1.generar_vainilla = cont_publicacion.generar_vainilla HAVING max(cp1.fecha_publicacion) = cont_publicacion.fecha_publicacion) -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast

Re: [PERFORM] OSDL Database Test Suite 3 is available on PostgreSQL

2003-08-01 Thread Josh Berkus
. I really look forward to trying it out. Further, OSDL-DBT3 can hopefully serve as the kernel of our internal performance option testing suite. We'll evaluate very soon. Thanks so much for your hard work! -- Josh Berkus PostgreSQL Core Team San Francisco ---(end

Re: [PERFORM] Odd problem with performance in duplicate database

2003-08-11 Thread Josh Berkus
that in that version I don't have the option of increasing the statistics sampling ... -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send

Re: [PERFORM] Perfomance Tuning

2003-08-14 Thread Josh Berkus
they are effectively an old dead-end fork of the Ingres/Postgres code, and have already been mined for most of the improvements they made. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all

Re: [PERFORM] Odd problem with performance in duplicate database

2003-08-14 Thread Josh Berkus
Ron, If the databases are on different machines, maybe the postgres.conf or pg_hba.conf files are different, and the buffer counts is affect- ing the optimizer? The databases are on the same machine, using the same postmaster. -- -Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] Odd problem with performance in duplicate database

2003-08-14 Thread Josh Berkus
on the other side) to a column with a very large fraction of nulls. We only discovered this recently; it's fixed as of 7.3.3: OK, I'll talk to the client about upgrading. -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus

Re: [PERFORM] Filesystems WAS: Perfomance Tuning

2003-08-14 Thread Josh Berkus
team any time in the forseeable future. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so

Re: [PERFORM] Odd problem with performance in duplicate database

2003-08-14 Thread Josh Berkus
? -- -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] about performance of postgreSQL

2003-08-14 Thread Josh Berkus
://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once

Re: [PERFORM] Odd problem with performance in duplicate database

2003-08-14 Thread Josh Berkus
tables and indexes were reproduced faithfully. Lowering random_page_cost seems to do the trick. But I'm still mystified; why would one identical database pick a different plan than its copy? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast

Re: [PERFORM] Odd problem with performance in duplicate database

2003-08-14 Thread Josh Berkus
is set pretty low already, at 1.5. It seems like I'd have to set random_page_cost to less than 1.0 to make sure that the planner never used a seq scan. Which kinda defies the meaning of the setting. *sigh* wish the client would pay for an upgrade -- -Josh Berkus Aglio Database Solutions

Re: [PERFORM] Insert performance

2003-08-16 Thread Josh Berkus
filesystems. ... but that's based on inadequate experimentation, just a few tests on Bonnie++ on a Netra running Solaris 8. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once

Re: [PERFORM] Tests

2003-08-22 Thread Josh Berkus
Tomka, Did you get my test suggestion? It never hit the lists, so I wonder if you got it -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] slow plan for min/max

2003-09-08 Thread Josh Berkus
far, nobody has been frustrated enough to spend 3 months tackling the problem. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send

[PERFORM] Quick question

2003-09-08 Thread Josh Berkus
Oracle doesn't have this performance issue. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] slow plan for min/max

2003-09-09 Thread Josh Berkus
. Working out the rules whereby the planner could decide the viability of index use is a non-trivial task. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http

Re: [PERFORM] Reading data in bulk - help?

2003-09-09 Thread Josh Berkus
from postgresql. If the computing app is complex and requires disk reads aside from postgres data, you should make sure that it lives on yet another disk. Or you can simplify this with a good, really large multi-channel RAID array. -- -Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] Reading data in bulk - help?

2003-09-10 Thread Josh Berkus
. OK. -- 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] software vs hw hard on linux

2003-09-12 Thread Josh Berkus
maintainer passed away in early 2002). The exception to this is if you are expecting to frequently max out your CPU and/or RAM with your application, in which case the SW RAID might not be so good because you would get query-vs.-RAID CPU contention. -- -Josh Berkus Aglio Database Solutions San

Re: [PERFORM] best arrangement of 3 disks for (insert) performance

2003-09-12 Thread Josh Berkus
RAID with few disks imposes a heavy performance penalty on data writes (particularly updates), sometimes as much as 50% for a RAID5-3disk config. I am a little curious why you've got a dual-xeon, but could only afford 3 disks -- -Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] restore time: sort_mem vs. checkpoing_segments

2003-09-15 Thread Josh Berkus
of one database. Cool! Thank you for posting this. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Inconsistent performance

2003-09-15 Thread Josh Berkus
Joseph, Please see this web page before posting anything else: http://techdocs.postgresql.org/guides/SlowQueryPostingGuidelines Currently, you are not posting enough data for anyone to be of meaningful help. -- -Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-17 Thread Josh Berkus
of 3 indexes per table affect that? Not until 7.4. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-18 Thread Josh Berkus
Guys, I also wrote a perl script that reindexes all tables, if anyone can't get reindexdb working or find it for 7.2. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] rewrite in to exists?

2003-09-18 Thread Josh Berkus
the DISTINCT in the main query instead; it depends on how many results you expect the subquery to have. Still, I'd suggest trying the EXISTS version first under most circumstances, DISTINCT is pretty slow. -- Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] Find one record

2003-09-18 Thread Josh Berkus
somewhere else whenever the table is updated. Much more reliable -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] How to force an Index ?

2003-09-18 Thread Josh Berkus
.data_emissao,'MM') AS VARCHAR)) AND ... -- 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] How to force an Index ?

2003-09-18 Thread Josh Berkus
that earlier I need to know if there is a way to set the to_char output to varchar instead of text ! Did you try: ... gsames00.ano_mes = (to_char(ftnfco00.data_emissao,'MM')::VARCHAR) AND ... -- Josh Berkus Aglio Database Solutions San Francisco ---(end

Re: [PERFORM] LIKE query running slow

2003-09-24 Thread Josh Berkus
Garrett, Recently we upgraded the RAM in our server. After the install a LIKE query that used to take 5 seconds now takes 5 minutes. We have tried the usual suspects, VACUUM, ANALYZE and Re-indexing. Any thoughts on what might have happened? Bad RAM? Have you tested it? -- -Josh Berkus

Re: [PERFORM] TPC-R benchmarks

2003-09-25 Thread Josh Berkus
not useful (for example, FK fields with only 3 possible values). So it may be that you need to create an index on that field. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ

Re: [PERFORM] Plan-Reading

2003-09-26 Thread Josh Berkus
Rhaoni, Where can I find a plan-readinf tutorial ? It's a little out of date, but is very well written and gives you the basics: http://www.argudo.org/postgresql/soft-tuning.html -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast

Re: [PERFORM] Performance: BigInt vs Decimal(19,0)

2003-09-27 Thread Josh Berkus
-NUMERIC -- 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] avoiding seqscan?

2003-09-28 Thread Josh Berkus
PostgreSQL and try your query again. What version, btw? -- 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] avoiding seqscan?

2003-09-28 Thread Josh Berkus
. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Performance: BigInt vs Decimal(19,0)

2003-09-29 Thread Josh Berkus
idea, on general principles. Abstracted design is a good thing. Regrettably, though, a lot of commercial databases do not support DOMAIN. You'll need to check which databases you are thinking of porting to first. -- Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] TPC-R benchmarks

2003-09-29 Thread Josh Berkus
, unless I've totally lost track. As I remarked before, Postgres does *not* automatically create indexes for FKs. Many, but not all, other database products do, so comparing PostgreSQL against those products without the index is unfair. -- Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] TPC-R benchmarks

2003-10-01 Thread Josh Berkus
to complete, I think we need you to run EXPLAIN ANALYZE instead of just EXPLAIN. Without the real-time statistics, we simply can't see what's slowing the query down. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP

Re: [PERFORM] [NOVICE] Ideal Hardware?

2003-10-01 Thread Josh Berkus
across to the client. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message

Re: [PERFORM] TPC-R benchmarks

2003-10-01 Thread Josh Berkus
Oleg, The output of the query should contain about 200 rows. So, I guess the planer is off assuming that the query should return 1 row. Oh, also did you post the query before? Can you re-post it with the planner results? -- Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] TPC-R benchmarks

2003-10-01 Thread Josh Berkus
' -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] TPC-R benchmarks

2003-10-02 Thread Josh Berkus
those multi-column indexes to determine the least columns you need for the indexes still to be used, since more columns = more index maintainence. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you

Re: [PERFORM] TPC-R benchmarks

2003-10-03 Thread Josh Berkus
it will finish any time soon. Can I get a copy of the database so that I can tinker? I'm curious now, plus I want our benchmarks to look good. I have a private FTP if that helps. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast

Re: [PERFORM] Postgres low end processing.

2003-10-03 Thread Josh Berkus
this. 4. Tune all of your queries carefully to avoid anything requiring a RAM-intensive merge join or CPU-eating calculated expression hash join, or similar computation-or-RAM-intensive operations. -- Josh Berkus Aglio Database Solutions San Francisco ---(end

Re: [PERFORM] Speeding up Aggregates

2003-10-03 Thread Josh Berkus
(dtstamp) from items where channel = $1 and link = $2; Change it to SELECT dtstamp from iterm where channel = $1 and link = $2 ORDER BY dtstamp DESC LIMIT 1 and possibly build an index on channel, link, dtstamp -- -Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] Speeding up Aggregates

2003-10-03 Thread Josh Berkus
Dror, I am using 7.4, and had tried NOT EXISTS and didn't see any improvements. It wouldn't if you're using 7.4, which has improved IN performance immensely. What happens if you stop using a function and instead use a subselect? -- -Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] reindex/vacuum locking/performance?

2003-10-03 Thread Josh Berkus
it. For heavily updated tables run it often - I run it every 5 minutes on some tables. A VACUUM FULL is only needed if you haven't been running VACUUM often enough in the first place. Also, if you find that you need to run VACUUM FULL often, then you need to raise your max_fsm_pages. -- -Josh Berkus

Re: [PERFORM] count(*) slow on large tables

2003-10-05 Thread Josh Berkus
that the effort-result payoff was not worthwhile. Overall, I think the stuff we already have planned ... the hash aggregates in 7.4 and Tom's suggestion of adding an indexable flag to pg_aggs ... are far more likely to yeild useful fruit than any caching plan. -- Josh Berkus Aglio Database

Re: [PERFORM] Shopping for hardware

2003-10-06 Thread Josh Berkus
databases you'll want a setting of 2 (which sounds huge but it's really only about 1mb memory). -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http

Re: [PERFORM] Postgres low end processing.

2003-10-06 Thread Josh Berkus
management. But on a low-end, 2-IDE-disk machine, you have to do it. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] TPC-R benchmarks

2003-10-07 Thread Josh Berkus
pardon me for not doing an ANALYZE, but I didn't want to wait overnight. Manually disabling Seqscan and Nestloop did nothing to affect this query plan; neither did removing the aggregate. Tommorrow I will test 7.4 Beta 4. How can we fix this? -- -Josh Berkus Aglio Database Solutions San

Re: [PERFORM] Presentation

2003-10-08 Thread Josh Berkus
Jeff, Its avail in powerpoint and (ugg) powerpoint exported html. I can probably convert it to OpenOffice.org and Flash. OK? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists

Re: [PERFORM] Compare rows

2003-10-08 Thread Josh Berkus
? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Compare rows

2003-10-08 Thread Josh Berkus
to use a subselect to force the issue. Or, as Joe Conway suggested, you could figure out some kind of value hash that uniquely identifies your rows. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain

Re: [PERFORM] PostgreSQL vs. MySQL

2003-10-08 Thread Josh Berkus
version which would cover the major points of setting PostgreSQL.conf. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] PostgreSQL vs. MySQL

2003-10-08 Thread Josh Berkus
they need to do this. Barring an objection, I'll get to work on this. -- 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] Compare rows

2003-10-08 Thread Josh Berkus
. Tommorrow I'll (hopefully) write up how to query this for comparisons. It would help if you gave a little more details about what specific comparison you're doing, e.g. between tables or table to value, comparing just the last value or all rows, etc. -- -Josh Berkus Aglio Database Solutions San

Re: [PERFORM] Compare rows

2003-10-08 Thread Josh Berkus
row bug in 7.3 and would be complaining about it. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do

Re: [PERFORM] OFFTOPIC: PostgreSQL vs MySQL

2003-10-09 Thread Josh Berkus
also, come to think about it, MySQL has done us a favor in some ways by making our project take advocacy and user-friendliness seriously, something we didn't always do. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast

Re: [pgsql-advocacy] [PERFORM] OFFTOPIC: PostgreSQL vs MySQL

2003-10-09 Thread Josh Berkus
better connections.) This is my responsibility; I'll add it to the list. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail

Re: [pgsql-advocacy] [PERFORM] OFFTOPIC: PostgreSQL vs MySQL

2003-10-09 Thread Josh Berkus
to it. Actually, I'm working on that this weekend. -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law

Re: [PERFORM] Speeding up Aggregates

2003-10-10 Thread Josh Berkus
to be surprised by the need to use this idiom. It IS in the FAQ. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes

Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

2003-10-10 Thread Josh Berkus
Nick, Having been following the thread on go for a script! / ex: PostgreSQL vs. MySQL. I thought I would throw something together in Perl. Cool! Would you be willing to work with me so that I can inject some of my knowledge of .conf tuning? -- Josh Berkus Aglio Database Solutions San

Re: [PERFORM] PostgreSQL Scalable ?

2003-10-10 Thread Josh Berkus
. That sounds doable on proper hardware with good tuning.Might I suggest that you consider hiring a consultant and start from there? I believe that Afilias Limited (www.afilias.info) has the requisite experience in Europe. -- Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] One or more processor ?

2003-10-10 Thread Josh Berkus
over the multiple processors. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] go for a script! / ex: PostgreSQL vs. MySQL

2003-10-10 Thread Josh Berkus
? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] One or more processor ?

2003-10-10 Thread Josh Berkus
Chris, If I do a SQL request ... does PostgreSQL use one or more processor ? Just one processor. For one query, yes. For multiple queries, PostgreSQL will use multiple processors, and that's what he's concerned about given his earlier posts. -- Josh Berkus Aglio Database Solutions San

Re: [PERFORM] further testing on IDE drives

2003-10-10 Thread Josh Berkus
Bruce, I would be interested to see if wal_sync_method = fsync is slower than wal_sync_method = open_sync. How often are we doing more then one write before a fsync anyway? OK. I'll see if I can get to it around my other stuff I have to do this weekend. -- Josh Berkus Aglio Database

Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

2003-10-12 Thread Josh Berkus
this? Depends on size/type of database. For large OLTP databases, I recommend vacuum as often as every 5 mintues, analyze every hour, and Vacuum Full + Reindex once a week. For a workstation database, your frequencies are probably OK. -- Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

2003-10-12 Thread Josh Berkus
be able to script for FreeBSD. Bug the FreeBSD developers. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Any issues with my tuning...

2003-10-13 Thread Josh Berkus
on the effect of tinkering with these values on different machines and setups. We would welcome your results. On high-end machines, random_page_cost almost inevatibly needs to be lowered to 2 or even 1.5 to encourage the use of indexes. -- -Josh Berkus Aglio Database Solutions San

Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

2003-10-14 Thread Josh Berkus
) :) On second thought, I'm not sure what an unusual locale would be. Scratch that. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Large Text Search Help

2003-10-14 Thread Josh Berkus
, Moore's law has prevented us from needing to devote serious effort to the above approach. Any other helpful comments or sugestions on how to improve query times using different hardware or software techniques would be appreciated. Read the archives of this list. -- Josh Berkus Aglio Database

Re: [PERFORM] vacuum locking

2003-10-17 Thread Josh Berkus
10.19s/6.03u sec elapsed 261.44 sec. What sort of disk array do you have? That seems like a lot of time considering how little work VACUUM is doing. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't

Re: [PERFORM] backup/restore - another area.

2003-10-16 Thread Josh Berkus
the indexes as well. but this is a price you have to pay. The other downside is, of course, that the database needs to be shut down. COOL HUH? Certainly very useful in the DBA's arsenal of backup tools. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast

Re: [PERFORM] backup/restore - another area.

2003-10-16 Thread Josh Berkus
on this thread. Want to refresh me? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so

Re: [PERFORM] Performance weirdness with/without vacuum analyze

2003-10-20 Thread Josh Berkus
be somewhat slow due to the forced join order, which is unavodable with a left outer join. However, regarding your peculiar behaviour, please post: 1) Your random_page_cost and effective_cache_size settings 2) The EXPLAIN ANALYZE of each query instead of just the EXPLAIN Thanks! -- Josh Berkus Aglio

[PERFORM] SRFs ... no performance penalty?

2003-10-20 Thread Josh Berkus
if this is comparitive time for string-parsing or something else; the 0.01 seems to be consistent regardless of scale. The difference between using a PL/pgSQL function as a query-builder only (the 7.2.x method) and using SRFs was small enough not to be significant. -- -Josh Berkus Aglio Database Solutions

Re: [PERFORM] Low Insert/Update Performance

2003-10-20 Thread Josh Berkus
Rhaoni, My problem is that I must substitute this Oracle for a PostgreSQL database and this same Delphi aplication takes 45 min to update Jan/2003. All delphi routines are converted and optmized to work with PgSQL. Obviously not. How about posting the update queries? -- Josh

Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Josh Berkus
. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

  1   2   3   4   5   6   7   8   9   10   >