Re: [PERFORM] Delete query takes exorbitant amount of time

so the system *can't* use an index while loading. If you're going with the drop/load/recreate option, then I'd suggest increasing work_mem for the duration. Hmmm ... or maintenance_work_mem? What gets used for FK checks? Simon? -- Josh Berkus Aglio Database So

Re: [PERFORM] Script for getting a table of reponse-time breakdown

/scripts/waits.htm). Life's too short for reading Oracle docs. Can you just explain, in step-by-step detail, what you want? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched ou

Re: [PERFORM] Script for getting a table of reponse-time breakdown

her into sub-categories, > and the component wait events are shown. This would be very nice. And very, very hard to build. No, we don't have anything similar. You can, of course, use profiling tools. -- Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] How to improve db performance with $7K?

new machine with only a moderate amount of hardware redundancy while still having 100% confidence in staying running. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose

Re: [PERFORM] Query Optimizer Failure / Possible Bug

LSE ) AS replaced_serials This means that the planner pretty much has to iterate over the subquery, running it once for each row in the result set. If you want the optimizer to use a JOIN structure instead, put the subselect in the FROM clause. -- --Josh Josh Berkus Aglio Database

Re: [PERFORM] How to speed up word count in tsearch2?

arch2 index is getting pushed out of RAM. When the index is cached it's very, very fast but takes a long time to get loaded from disk. You need to look at what else is using RAM on that machine. And maybe buy more. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---

Re: [PERFORM] [sfpug] DATA directory on network attached storage

rding against? How likely is a machine failure if its hard drives are external? -- 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 not match

[PERFORM] Functionscan estimates

give it a row estimate for planning purposes. Thoughts? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Functionscan estimates

r world, we could tie it to a table, saying that, for example, proestrows = my_table*0.02. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Functionscan estimates

n flat constant estimate would be an improvement. > BTW, why is this on -performance? It should be on -hackers. 'cause I spend more time reading -performance, and I started the thread. Crossed over now. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] 4 way JOIN using aliases

rong; probably you need to ANALYZE tbl_item. But I doubt that will make a difference in execution time. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archive

Re: [PERFORM] PLM pulling from CVS nightly for testing in STP

Mark, > Just wanted everyone to know what we're pulling CVS HEAD nightly so it > can be tested in STP now. Let me know if you have any questions. Way cool.How do I find the PLM number? How are you nameing these? -- --Josh Josh Berkus Aglio Database Solutions Sa

Re: [PERFORM] Question on REINDEX

take > hours. This is too long to lock the client apps out. > Is there any other solution? Better to up your max_fsm_pages and do regular VACUUMs regularly and frequently so that you don't have to REINDEX at all. -- --Josh Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] Question on REINDEX

s to that would be assuming that you excpected 100% of the rows to be replaced by UPDATES or DELETEs before you ran VACUUM. I generally run VACUUM a little sooner than that. See the end portion of: http://www.powerpostgresql.com/PerfList -- Josh Berkus Aglio Database Solutions San Francisc

Re: [PERFORM] Question on REINDEX

lready been vacuumed and not have any useful free space left. Yes? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

[PERFORM] Bad n_distinct estimation; hacks suggested?

ctor of 10x and it's causing query planning problems. Any suggested hacks to improve the histogram on this? (BTW, increasing the stats to 1000 only doubles n_distinct, and doesn't solve the problem) -- --Josh Josh Berkus Aglio Database Solutions San Francisco --

Re: [PERFORM] Bad n_distinct estimation; hacks suggested?

19,6888329} | 0.41744 -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [PERFORM] What to do with 6 disks?

eavy, I'd recommend (d), with the WAL on the same disk as the OS, i.e. RAID1 2 disks OS, pg_xlog RAID 1+0 4 disks pgdata -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Opteron vs Xeon (Was: What to do with 6 disks?)

rd instead of an LSI or other good card If all you *need* is 1/2 the performance of an Opteron box, and you can get a good deal, then go for it. But don't be under the illusion that Dell is competitive with Sun, IBM, HP, Penguin or Microway on servers. -- --Jos

Re: [PERFORM] Bad n_distinct estimation; hacks suggested?

88,3621357,3645094,3718667,3740821,3762386,3783169,3804 >593,3826503,3904589,3931012,3957675,4141934,4265118,4288568,4316898,4365625, >4473965,4535752,4559700,4691802,4749478,5977208,6000272,6021416,6045939,6078 >912,6111900,6145155,6176422,6206627,6238291,6271270,6303067,6334117,6365200, >6395250,

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

u first raised your query issue 6 days ago. 6 days is not a lot of time for getting *free* troubleshooting help by e-mail. Certainly it's going to take more than 6 days to port to MySQL. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

far more bang for the buck with some expert advice, that's all. But don't bother with Dell support any further, they don't really have the knowledge to help you. So ... new EXPLAIN ANALYZE ? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

STATISTICS 1000; ALTER TABLE tbllocation ALTER COLUMN regionid SET STATISTICS 1000; ANALYZE tblresponseheader; ANALYZE tbllocation; Then run the EXPLAIN ANALYZE again. (on Linux) -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast

Re: [PERFORM] Index bloat problem?

nd event like that. As you should now. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] How can an index be larger than a table

nt without a unique index, something is seriously broken. I suspect hacking of system tables. Otherwise, it sounds like you have index bloat due to mass deletions. Run REINDEX, or, preferably, VACUUM FULL and then REINDEX. -- Josh Berkus Aglio Database Solutions S

Re: [PERFORM] Index bloat problem?

eration upsets that, and generally needs to be followed by a REINDEX. > Is > this a common issue among all RDBMSs or is it > something that is PostgreSQL specific? Speaking from experience, this sort of thing affects MSSQL as well, although the maintenance routines are different.

Re: [PERFORM] Index bloat problem?

experience, this sort of thing affects MSSQL as well, although the maintenance routines are different. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Index bloat problem?

s on user tables are not processed. Also, indexes on shared system catalogs are skipped except in stand-alone mode (see below). " http://www.postgresql.org/docs/8.0/static/sql-reindex.html -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)---

Re: [PERFORM] Index bloat problem?

Michael, > Every five minutes, DBCC INDEXDEFRAG will report to the user an > estimated percentage completed. DBCC INDEXDEFRAG can be terminated at > any point in the process, and *any completed work is retained.*" Keen. Sounds like something for our TODO list. -- Jos

Re: [PERFORM] Index bloat problem?

5 posts a day, it's easy for people to lose track of stuff they meant to comment on. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Index bloat problem?

xpecting.The FSM relates the the re-use of nodes, not taking up free space. So after you've deleted 75% of rows, the index wouldn't shrink. It just wouldn't grow when you start adding rows. -- --Josh Josh Berkus Aglio Database Solutions San Francisco -

Re: [PERFORM] Bad n_distinct estimation; hacks suggested?

h? Hmmm. Good point. Will have to test on Linux. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Bad n_distinct estimation; hacks suggested?

k the problem is in our heuristic sampling code. I'm not the first person to have this kind of a problem. Will be following up with tests ... -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: su

Re: [PERFORM] Updating table, precautions?

#x27;public') and relname = > 'r_itemcategory'; See the code in CVS in the "newsysviews" project in pgFoundry. Andrew coded up a nice pg_user_table_storage view which gives table, index and TOAST size. -- --Josh Josh Berkus Aglio Database Solutions San Fr

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

timing comparisons are deceptive unless you're careful: MSSQL returns the results on block at a time, and reports execution time as the time required to return the *first* block, as opposed to Postgres which reports the time required to return the whole dataset. -- Josh Berkus Aglio Databas

Re: [PERFORM] Bad n_distinct estimation; hacks suggested?

x than the formula we're using. Can someone whose math is more recent than calculus in 1989 take a look at that paper, and look at the formula toward the bottom of page 10, and see if we are correctly interpreting it?I'm particularly confused as to what "q" and &q

Re: [PERFORM] Bad n_distinct estimation; hacks suggested?

uot; represent.  Thanks! Actually, I managed to solve for these and it appears we are using the formula correctly. It's just a bad formula. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get

Re: [HACKERS] [PERFORM] Bad n_distinct estimation; hacks suggested?

ints out we presumably do page sampling rather than purely random sampling so I should probably read the paper he referenced. Working on it now -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the plan

Re: [HACKERS] [PERFORM] Bad n_distinct estimation; hacks suggested?

lve for D(sub)Md on page 6? I'd like to test it on samples of < 0.01%. Tom, how does our heuristic sampling work? Is it pure random sampling, or page sampling? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)

Re: [HACKERS] [PERFORM] Bad n_distinct estimation; hacks suggested?

I just need a little help doing the math ... please? -- --Josh 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 [

Re: [HACKERS] [PERFORM] Bad n_distinct estimation; hacks suggested?

" here, I'm talking about "accurate enough for planner purposes" which in my experience is a range between 0.2x to 5x. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get of

Re: [PERFORM] half the query time in an unnecessary(?) sort?

to do. Looking at your analyze, though, I think it's not the sort that's taking the time as it is that the full sorted entity_id column won't fit in work_mem. Try increasing it? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadca

Re: [PERFORM] Table Partitioning: Will it be supported in Future?

le is not in need of partitioning. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] [PERFORM] Bad n_distinct estimation; hacks suggested?

more specifically overly conservative. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] [PERFORM] Bad n_distinct estimation; hacks suggested?

sample a large % of pages. If we're doing sampling-based estimation, I really don't want people to lose sight of the fact that page-based random sampling is much less expensive than row-based random sampling. We should really be focusing on methods which are page-based. -- Josh Ber

Re: [PERFORM] Final decision

rray set up for fast reads, high shared mem and work mem. If reporting is at least 1/4 of your workload, I'd suggest spinning that off to the 2nd machine before putting one client on that machine. That way you can also use the 2nd machine as a failover back-up. -- Josh Berkus Aglio D

Re: [PERFORM] Final decision

riting /their/ > driver] OK. Well, let's put it this way: the v3 and v3.5 drivers will not be based on the current driver, unless you suddenly have a bunch of free time. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Why is this system swapping?

hold 100 free connections centrally rather than 10 per server might be a win. Better would be getting some of this stuff offloaded onto database replication slaves. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--

Re: [PERFORM] Final decision

nd you, having 2 different teams working on two different ODBC drivers is a problem for another list ... -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Distinct-Sampling (Gibbons paper) for Postgres

> Now, if we can come up with something better than the ARC algorithm ... Tom already did. His clock-sweep patch is already in the 8.1 source. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have

Re: [PERFORM] batch inserts are "slow"

m on the JDBC list for details; I think he needs testers. -- 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 not match

Re: [HACKERS] [PERFORM] Bad n_distinct estimation; hacks suggested?

is that to do, for example, 10% of rows purely randomly would actually mean loading 50% of pages. With 20% of rows, you might as well scan the whole table. Unless, of course, we use indexes for sampling, which seems like a *really good* idea to me -- --Josh Josh Berkus Aglio Database

Re: [HACKERS] [PERFORM] Bad n_distinct estimation; hacks suggested?

John, > But doesn't an index only sample one column at a time, whereas with > page-based sampling, you can sample all of the columns at once. Hmmm. Yeah, we're not currently doing that though. Another good idea ... -- --Josh Josh Berkus Aglio Database Solutio

Re: [PERFORM] Whence the Opterons?

, though: is HP still using their proprietary RAID card? And, if so, have they fixed its performance problems? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregis

Re: [PERFORM] Query tuning help

t found them to be congruous with my > application. Sounds like you either need to restructure your application, restructure your database (so that you're not doing "anywhere in field" searches), or buy 32GB of ram so that you can cache the whole table. -- Josh Berkus Aglio Data

Re: [PERFORM] Query tuning help

en cached, are *very* fast). So if you have a variety of other processes that tend to fill up RAM between searches, you may find them less useful. 3) You have to create a materialized index column next to recordtext, which will increase the size of the table. -- Josh Berkus Aglio Database Solut

Re: [PERFORM] Partitioning / Clustering

> but PG   > just has a bunch of third party extensions, I wonder why these are   > not being integrated into the main trunk :/ Because it represents a host of complex functionality which is not applicable to most users? Because there are 4 types of replication and 3 kinds of cluserin

Re: [PERFORM] Partitioning / Clustering

that these were not high-availability clusters; it's impossible to add a server to an existing cluster, and a server going down is liable to take the whole cluster down. Mind you, I've not tried that aspect of it myself; once I saw the ram-only rule, we switched to something else. -

Re: [PERFORM] Partitioning / Clustering

way to improve horizontal scalability by taking disposable data (like session information) out of the database and putting it in protected RAM. On some websites, adding memcached can result is as much as a 60% decrease in database traffic. -- --Josh Josh Berkus Aglio Databa

Re: [PERFORM] Bad plan after vacuum analyze

decision seems to have been made entirely on the basis of the cost of the join itself (total of 17) without taking the cost of the sort and index access (total of 2600+) into account. Tom, is this a possible error in planner logic? -- --Josh Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] tuning Postgres for large data import (using Copy from)

py from on: same table / different > tables ? Same table is useless; the imports will effectively serialize (unless you use pseudo-partitioning). You can parallel load on multiple tables up to the lower of your number of disk channels or number of processors. -- Josh Berkus Aglio Database

Re: [PERFORM] Partitioning / Clustering

Use memcached, squid, lighttpd caching, ASP.NET caching, pools, etc. Keep the load off the database except for the stuff that only the database can do. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Partitioning / Clustering

ing in front of your web server, and serve far more page views than you could with Apache alone. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Recommendations for set statistics

1000 when it comes to "bad" queries. I have an unfinished patch in the works which goes through and increases the stats_target for all *indexed* columns to 100 or so. However, I've needed to work up a test case to prove the utility of it. -- Josh Berkus Aglio Database Solutions

Re: [PERFORM] Postgresql Performance via the LSI MegaRAID 2x Card

and 256MB. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] ok you all win what is best opteron (I dont want a hosed system again)

t *all* of our queries run from materialized aggregate tables. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Postgresql Performance via the LSI MegaRAID 2x Card

William, > I'm sure there's some corner case where more memory helps. QUite possibly. These were not scientific tests. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] checkpoint segments

m wondering if recycling is > known to be a big hit in general, and if I should strive to tune so that > it never happens (if that's possible)? Yes, and yes. Simply allocating more checkpoint segments (which can eat a lot of disk space -- requirements are 16mb*(2 * segments +1) ) will p

Re: [PERFORM] checkpoint segments

#x27;s possible that the checkpoints which do occur are worse, but they're not enough worse to counterbalance their infrequency. I have not yet been able to do a full scalability series on bgwriter. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of b

Re: [PERFORM] Tuning planner cost estimates

ly estimate the relative cost on a live database, not a test one. This is also going to be a moving target because Tom's in-memory-bitmapping changes relative cost equations. I think a first step would be, in fact, to develop a tool that allows us to put EXPLAIN ANALYZE results in a database t

Re: [PERFORM] Tuning planner cost estimates

ting formula for nesting levels? -- 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 strugling during high load

Anjan, > As far as disk I/O is concerned for flushing the buffers out, I am not > ruling out the combination of Dell PERC4 RAID card, and the RH AS 3.0 > Update3 being a problem. You know that Update4 is out, yes? Update3 is currenly throttling your I/O by about 50%. -- --Josh Jo

Re: [PERFORM] seqential vs random io

Web == random access. Data Warehouse == sequential access. -- --Josh 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

Re: [PERFORM] Need help to decide Mysql vs Postgres

ion. It's been my personal experience that MySQL does not scale well beyond about 75GB without extensive support from MySQL AB. PostgreSQL more easily scales up to 200GB, and to as much as 1TB with tuning expertise. -- --Josh Josh Berkus Aglio Database Soluti

Re: [PERFORM] Need help to decide Mysql vs Postgres

currently beta. --Josh -- __Aglio Database Solutions___ Josh BerkusConsultant josh@agliodbs.comwww.agliodbs.com Ph: 415-752-2500Fax: 415-752-2387 2166 Hayes Suite 200San Francisco, CA ---(end of broadcast)---

Re: [PERFORM] Select performance vs. mssql

gt; > to every update and the opinion of the developers is > > that this would be > > a net loss overall. Pretty much. There has been discussion about allowing index-only access to "frozen" tables, i.e. archive partitions. But it all sort of hinges on someone implementing

Re: [PERFORM] Postgresql and xeon.

Eric, > What about xeon and postgresql, i have been told that > postgresql wouldn't perform as well when running > under xeon processors due to some cache trick that postgresql > uses? Search the archives of this list. This has been discussed ad nauseum. www.pgsql.ru --

Re: [PERFORM] Index not used on join with inherited tables

cessary to use UNION; just select from the parent. -- 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 on an AMD64 machine

hingly flat once it reaches the required level, which will take a lot of the guesswork out of allocating buffers. Regarding 2GB memory allocation, though, we *could* really use support for work_mem and maintenance_mem of > 2GB. -- Josh Berkus Aglio Database Solutions San Franci

Re: [PERFORM] Resource Requirements

am and Manual 8-15MB > Regression Tests 30MB > Compiled Source 60-160MB Well, my compiled source takes up 87mb, and the installed PostgreSQL seems to be about 41mb including WAL. Not sure how much the regression tests are. -- --Josh Josh Berkus Aglio Database Solutions S

Re: [PERFORM] Needed: Simplified guide to optimal memory configuration

Dennis, > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html > > > NOTICE: shared_buffers is 256 For everyone's info, the current (8.0) version is at: http://www.powerpostgresql.com/PerfList -- --Josh Josh Berkus Aglio Database Soluti

Re: [PERFORM] How does the transaction buffer work?

o begin with :) Quite possibly, but the visibility issue won't be the problem. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Needed: Simplified guide to optimal memory

ly that can be true. Oddly, 7.2 -> 8.0 is less trouble than 7.2 -> 7.4 because of some type casting issues which were resolved. Mind you, in the past a quick "sed" script has been adequate for me to fix compatibility issues. -- Josh Berkus Aglio Database Solutions San Francis

Re: [PERFORM] autovacuum suggestions for 500,000,000+ row tables?

ve > autovacuum configuration? Hmmm, good point, you could use autovacuum for ANALYZE only. Just set the VACUUM settings preposterously high (like 10x) so it never runs. Then it'll run ANALYZE only. I generally threshold 200, multiple 0.1x for analyze; that is, re-analyze af

Re: [PERFORM] autovacuum suggestions for 500,000,000+ row tables?

ough. As long as your update/deletes are less than 10% of the table for all time, you should never have to vacuum, pending XID wraparound. > Is this an 8.0 thing? I don't have a pg_controldata from what I can > see. Thats nice to hear though. 'fraid so, yes. -- --Josh Josh Berkus

[PERFORM] Configurator project launched

ovide an option between the poor-performing default configuration, and the in-depth knowledge required for hand-tuning. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensiv

Re: [PERFORM] Configurator project launched

w, I can bump it up on > my todo list. Um, can't we just get that from pg_settings? Anyway, I'll be deriving settings from the .conf file, since most of the time the Configurator will be run on a new installation. -- --Josh Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] Performance Tuning Article

t SATA disks still suck for read-write applications. I generally rate 1 UltraSCSI = 2 SATA disks for anything but a 99% read application. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: In versions below 8

Re: [PERFORM] parameterized LIKE does not use index

could use the > index?  Or can I convince the (Perl) driver to do so? There should be an option to tell DBD::Pg not to cache a query plan. Let's see yes. pg_server_prepare=0, passed to the prepare() call. -- --Josh Josh Berkus Aglio Database Solutions San Francisco

Re: [PERFORM] parameterized LIKE does not use index

; know if there is a way to save some of the prepare working while > doing this. That wouldn't help much in Kurt's case.Nor in most "real" cases, which is why I think the idea never went anywhere. -- Josh Berkus Aglio Database Solutions San Francisco -

Re: [PERFORM] Poor index choice -- multiple indexes of the same columns

TARGET for the relevant columns set to? When's the last time you ran analyze? If this is all updated, you want to post the pg_stats rows for the relevant columns? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--

Re: [PERFORM] start time very high

Jean-Max, > I have two computers, one laptop (1.5 GHz, 512 Mb RAM, 1 disk 4200) > and one big Sun (8Gb RAM, 2 disks SCSI). Did you run each query several times? It looks like the index is cached on one server and not on the other. -- --Josh Josh Berkus Aglio Database Solutio

Re: [PERFORM] Planner constants for RAM resident databases

Emil, > -> Merge Left Join (cost=9707.71..13993.52 rows=1276 width=161) > (actual time=164.423..361.477 rows=49 loops=1) That would indicate that you need to either increase your statistical sampling (SET STATISTICS) or your frequency of running ANALYZE, or both. -- --Josh Jo

Re: [PERFORM] Data Warehousing Tuning

sk.Make sure the other disk is dedicated exclusively to the xlog, set it forcedirectio, and increase your checkpoint_segments to something like 128. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: su

Re: [PERFORM] Need suggestion high-level suggestion on how to solve a performance problem

tree structure. It has some nice features which makes it siginifcanly better than using a delimited text field. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Mount database on RAM disk?

aching is up to the OS/filesystem. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] What is best way to stream terabytes of data into postgresql?

Jeff, > Streaming being the operative word. Not sure how much hacking you want to do, but the TelegraphCQ project is based on PostgreSQL: http://telegraph.cs.berkeley.edu/telegraphcq/v0.2/ -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end

Re: [Bizgres-general] Re: [PERFORM] faster INSERT with possible

since: a) Temp tables can't be shared by several writers, and b) you can't index a temp table. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

[PERFORM] wal_buffer tests in

/view.php/141/79/wal_buffer_test.pdf As always, detailed test results are available from OSDL, just use: http://khack.osdl.org/stp/# where # is the test number. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: I

Re: [PERFORM] Performance problems testing with Spamassassin 3.1.0 Bayes module.

ught is that this is a pretty complicated procedure for something you want to peform well.Is all this logic really necessary? How does it get done for MySQL? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)

  1   2   3   4   5   6   7   8   9   10   >