Re: [PERFORM] Pgsql - Red Hat Linux - VS MySQL VS MSSQL

2003-07-13 Thread Jim C. Nasby
using ports on FreeBSD is pretty painless (for that matter, so is keeping the OS itself up-to-date). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828

Re: [PERFORM] Odd explain estimate

2003-07-31 Thread Jim C. Nasby
On Thu, Jul 31, 2003 at 04:59:21PM -0400, Andrew Sullivan wrote: On Thu, Jul 31, 2003 at 02:51:45PM -0500, Jim C. Nasby wrote: If you really needed to set enable_seqscan=false (did you really? Are you sure that's not the cheapest way?), you might want to investigate expainding the statistics

Re: [PERFORM] Odd explain estimate

2003-08-02 Thread Jim C. Nasby
On Fri, Aug 01, 2003 at 08:16:12AM -0400, Andrew Sullivan wrote: On Thu, Jul 31, 2003 at 05:59:59PM -0500, Jim C. Nasby wrote: Well, if I don't do this it wants to seqscan a table that occupies 350k pages, instead of pulling a couple thousand rows. I started running it with the seqscan

[PERFORM] Poor pg_dump performance

2003-09-06 Thread Jim C. Nasby
process be at 100% CPU? It does seem a bit coincidental that the two procs seem to be taking 100% of one CPU (top shows them running on different CPUs though). This is version 7.3.4. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club

Re: [PERFORM] possible improvement between G4 and G5

2004-04-06 Thread Jim C. Nasby
if you're paying per CPU!). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go

[PERFORM] Poor performance of group by query

2004-04-16 Thread Jim C. Nasby
. Also, if I set enable_hashagg = false, it runs in less than a second. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go

[PERFORM] Horribly slow hash join

2004-04-16 Thread Jim C. Nasby
ms Even though the second case is only a select, it seems clear that something's wrong... -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows

Re: [PERFORM] Horribly slow hash join

2004-04-20 Thread Jim C. Nasby
Dammit, I somehow deleted a bunch of replies to this. Did a TODO ever come out of this? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

2004-08-31 Thread Jim C. Nasby
, the same kind of reorganization you would normally expect in a BTree index. This isn't true, at least in 9i. You can create whatever indexes you want on an index-organized table. I believe that the index stores the PK value instead of the ROWID. -- Jim C. Nasby, Database Consultant [EMAIL

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

2004-08-31 Thread Jim C. Nasby
. I'm sure there's a ton of things I'm missing, especially since I'm not familiar with the postgresql code, but hopefully others can explore this further. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-14 Thread Jim C. Nasby
the partitions to be eliminated from the query, and so run for much longer. Is there by any chance a set of functions to manage adding and removing partitions? Certainly this can be done by hand, but having a set of tools would make life much easier. I just looked but didn't see anything on GBorg. -- Jim C

Re: [PERFORM] Partitioning

2004-09-16 Thread Jim C. Nasby
that the table has over 130M rows right now. So it would be nice to have an easy way to partition the table based on unique project_id's and not waste space in the partition tables on a field that will be the same for every row (in each partition). -- Jim C. Nasby, Database Consultant

Re: [PERFORM] Article about PostgreSQL and RAID in Brazil

2004-09-16 Thread Jim C. Nasby
, we have a box with 8 drives, 2 in a mirror with the OS and WAL and pg_temp; the rest in a raid10 with the database on it. Do you think it would have been better to make one big raid10? What if it was raid5? And what if it was only 6 drives total? -- Jim C. Nasby, Database Consultant

Re: [PERFORM] Article about PostgreSQL and RAID in Brazil

2004-09-16 Thread Jim C. Nasby
setup is that the raid10 is almost always the IO bottleneck, and not the mirror with everything else on it. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do

Re: [PERFORM] Caching of Queries

2004-09-27 Thread Jim C. Nasby
is required. Even if locks were required, they would be shared read locks which wouldn't block each other. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you

Re: [PERFORM] Caching of Queries

2004-09-30 Thread Jim C. Nasby
will swamp the planning cost. This does not mean you have to use something like pgpool (which makes some rather questionable claims IMO); any decent web application language/environment will support connection pooling. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer

Re: [PERFORM] Caching of Queries

2004-10-01 Thread Jim C. Nasby
is persistent (i.e. not dropped and remade for each request), but not shared between processes, therefore not pooled. OK, that'd work too... the point is if you're re-connecting all the time it doesn't really matter what else you do for performance. -- Jim C. Nasby, Database Consultant

Re: [PERFORM] Caching of Queries

2004-10-04 Thread Jim C. Nasby
is how much will be saved. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what

Re: [PERFORM] Performance suggestions for an update-mostly database?

2004-10-04 Thread Jim C. Nasby
: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want

Re: [PERFORM] Does PostgreSQL run with Oracle?

2004-10-21 Thread Jim C. Nasby
licensing costs was usually enough to pay for the new hardware in one year. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow

Re: [PERFORM] OS desicion

2004-10-21 Thread Jim C. Nasby
on process priority, while linux won't. This can be very handy for things like vacuum. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow

Re: [PERFORM] Anything to be gained from a 'Postgres Filesystem'?

2004-10-21 Thread Jim C. Nasby
/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want

Re: [PERFORM] Sequential Scan with LIMIT

2004-10-28 Thread Jim C. Nasby
crude to detect. Isn't that exactly what pg_stats.correlation is? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD

Re: [PERFORM] Sequential Scan with LIMIT

2004-10-28 Thread Jim C. Nasby
On Thu, Oct 28, 2004 at 07:49:28PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Sun, Oct 24, 2004 at 04:11:53PM -0400, Tom Lane wrote: The test case you are showing is probably suffering from nonrandom placement of this particular data value; which is something

[PERFORM] seqscan strikes again

2004-11-09 Thread Jim C. Nasby
like to avoid putting a 'set enable_seqscan=false' in my code, especially since this query only has a problem if it's run on a large date/time window, which normally doesn't happen. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy

Re: [PERFORM] seqscan strikes again

2004-11-10 Thread Jim C. Nasby
date/time window, which normally doesn't happen. Try increasing your statistics target for the column and then rerunning analyze. Sincerely, Joshua D. Drake -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team

Re: [PERFORM] Analyzer is clueless

2004-11-17 Thread Jim C. Nasby
it'll be getting a unique value or a small set of values even though stats indicates that it should be. One final question... would there be interest in a process that would dynamically update the histogram settings for tables based on how distinct/unique each field was? -- Jim C. Nasby, Database

Re: [PERFORM] memcached and PostgreSQL

2004-11-24 Thread Jim C. Nasby
through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net

Re: [PERFORM] Postgres vs. DSpam

2004-11-29 Thread Jim C. Nasby
://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 These PRESERVES should be FORCE-FED to PENTAGON OFFICIALS!! - -- Jim C. Nasby, Database

Re: [PERFORM] Normalization or Performance

2004-12-02 Thread Jim C. Nasby
be found at http://minilink.org/cvs.distributed.net/l3.sql. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys

Re: [PERFORM] Partitioned table performance

2004-12-21 Thread Jim C. Nasby
can get through to the mailing list cleanly -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming

Re: [PERFORM] Partitioned table performance

2004-12-21 Thread Jim C. Nasby
} | {0.461133,0.4455,0.0444333,0.0418667,0.0049,0.00216667} | | 0.703936 -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what

Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft

2005-01-10 Thread Jim C. Nasby
real-world examples where you could do something with a PostgreSQL procedural language that you couldn't do with PL/SQL. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today

Re: [PERFORM] Performance delay

2005-01-13 Thread Jim C. Nasby
)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-20 Thread Jim C. Nasby
it? I thought it did, I know it was discussed w/ the guy from Cox Communications and I thought he was using it :). No, PostgreSQL doesn't support any kind of partitioning, unless you write it yourself. I think there's some work being done in this area, though. -- Jim C. Nasby, Database

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-20 Thread Jim C. Nasby
across machines. So you better have fast access to the drive array, and the array better have caching of some kind. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-20 Thread Jim C. Nasby
people willing to work on it. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what

[PERFORM] Odd number of rows expected

2005-01-21 Thread Jim C. Nasby
: 299623.954 ms Everything is analyzed, and the statistics target is set to 1000. Basically, it seems that it doesn't understand that each row in log will match up with at most one row in bucket. There is a unique index on bucket(rrs_id, end_time), so it should be able to tell this. -- Jim C. Nasby

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-22 Thread Jim C. Nasby
. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy

Re: [PERFORM] Cheaper VACUUMing

2005-01-23 Thread Jim C. Nasby
to the mailing list cleanly -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what

Re: [PERFORM] Odd number of rows expected

2005-01-23 Thread Jim C. Nasby
On Sat, Jan 22, 2005 at 10:18:00PM -0500, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: (SELECT b.bucket_id AS rrs_bucket_id, s.* FROM rrs.bucket b JOIN page_log.log s

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-23 Thread Jim C. Nasby
of large tables would make for a significant reduction in the cost of VACUUM. FWIW, that's already on the TODO. See also http://lnk.nu/archives.postgresql.org/142.php. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-24 Thread Jim C. Nasby
valid to assume that minimizing the amount of work you do in a transaction means better throughput without considering what it will cost to do the work you're putting off until later. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy

Re: [PERFORM] Automagic tuning

2005-01-31 Thread Jim C. Nasby
pg_stats_* or explain analize and turn them into configuration settings (such and random page cost)? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want

Re: [PERFORM] High end server and storage for a PostgreSQL OLTP system

2005-01-31 Thread Jim C. Nasby
. If you look in the archives you'll find people running postgresql on 30 and 40 drive arrays. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want

Re: [PERFORM] Automagic tuning

2005-01-31 Thread Jim C. Nasby
On Tue, Feb 01, 2005 at 12:06:27AM -0500, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Mon, Jan 31, 2005 at 03:26:12PM -0500, Tom Lane wrote: Preferably a whole lot of queries. All the measurement techniques I can think of are going to have a great deal of noise, so you

Re: [PERFORM] High end server and storage for a PostgreSQL OLTP system

2005-02-01 Thread Jim C. Nasby
you can lose half your drives without any data loss (if each dead drive is part of a different mirror). Recovery is also faster. You'll almost certainly be much happier with hardware raid instead of software raid. stats.distributed.net runs a 3ware controller and SATA drives. -- Jim C. Nasby

Re: [PERFORM] Peformance Tuning Opterons/ Hard Disk Layout

2005-02-25 Thread Jim C. Nasby
of my head, but I think it was a 'wedge' that clients would connect to as if it was the database, and the wedge would then find an available database process to use. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team

Re: [PERFORM] multi billion row tables: possible or insane?

2005-03-04 Thread Jim C. Nasby
not for memory size but because of memory *bandwidth*). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-21 Thread Jim C. Nasby
... WHERE customer_id = 1 would be a good example of such a query (assuming the table is partitioned on something like invoice_date). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want

Re: [PERFORM] What needs to be done for real Partitioning?

2005-03-21 Thread Jim C. Nasby
to recognize if the partition that tuple was in had been removed, and just ignore that index entry. Granted, you'd need to clean the index up at some point (presumably via vacuum), but it doesn't need to occur at partition drop time. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED

Re: [PERFORM] Preventing query from hogging server

2005-03-28 Thread Jim C. Nasby
it's a more effective means of limiting the impact of large queries. I don't know how other OS's handle this. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where

[PERFORM] Compressing WAL

2005-04-04 Thread Jim C. Nasby
bound than CPU bound. And unlike the base tables, you generally don't need to read the WAL, so you don't really need to worry about not being able to quickly scan through the data without decompressing it. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Jim C. Nasby
to -admin about a database that's doing 340M inserts a day in 300M transactions... -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow

Re: [HACKERS] Recognizing range constraints (was Re: [PERFORM] Plan for relatively simple query seems to be very inefficient)

2005-04-06 Thread Jim C. Nasby
at what b and c look like each time 'through the loop'. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys

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

2005-04-06 Thread Jim C. Nasby
give each drive it's own dedicated bandwidth). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming

Re: [HACKERS] Recognizing range constraints (was Re: [PERFORM] Plan for relatively simple query seems to be very inefficient)

2005-04-07 Thread Jim C. Nasby
On Wed, Apr 06, 2005 at 06:35:10PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Wed, Apr 06, 2005 at 06:09:37PM -0400, Tom Lane wrote: Can anyone suggest a more general rule? Do we need for example to consider whether the relation membership is the same in two clauses

Re: [PERFORM] Any way to speed this up?

2005-04-09 Thread Jim C. Nasby
do not match -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what

Re: [PERFORM] Functionscan estimates

2005-04-09 Thread Jim C. Nasby
(estimate_rows_for_sql(text)) would probably be very useful to functions that wanted to support returning a rows estimate. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today

Re: [PERFORM] Compressing WAL

2005-04-13 Thread Jim C. Nasby
like you could just put a compression routine between the log writer and the filesystem. Is this a TODO? ISTM it's at least worth hacking something together and doing some performance testing... -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain

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

2005-04-19 Thread Jim C. Nasby
the faster spindle speed of drive A? The increased data density will help transfer speed off the platter, but that's it. It won't help rotational latency. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828

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

2005-04-19 Thread Jim C. Nasby
different RAID configurations using dbt2. I don't know if this is something that the lab is setup for or capable of, though. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today

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

2005-04-19 Thread Jim C. Nasby
or two very large tables, the extra spindles won't help unless you break up the tables and glue them together with query magic. But it's still a point to consider. Huh? Do you know how RAID10 works? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some

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

2005-04-19 Thread Jim C. Nasby
eager to find out how PG handles all this. AFAIK PostgreSQL requests data one database page at a time (normally 8k). Of course the OS might do something different. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828

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

2005-04-19 Thread Jim C. Nasby
and not raw SCSI... -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what

Re: [PERFORM] immutable functions vs. join for lookups ?

2005-04-19 Thread Jim C. Nasby
You should re-run the function test using SQL as the function language instead of plpgsql. There might be some performance to be had there. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do

Re: [PERFORM] How to improve postgres performace

2005-04-19 Thread Jim C. Nasby
on how frequently a table is vacuumed, it will settle down to a steady-state size that is greater than it's size after a vacuum full. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want

Re: [PERFORM] Sort and index

2005-04-19 Thread Jim C. Nasby
, probably can). Actually, the planner (at least in 7.4) isn't smart enough to consider if the sort would fit in memory or not. I'm running a test right now to see if it's actually faster to use an index in this case. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your

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

2005-04-19 Thread Jim C. Nasby
---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team

[PERFORM] Slow copy with little CPU/disk usage

2005-04-19 Thread Jim C. Nasby
C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast

Re: [PERFORM] Sort and index

2005-04-19 Thread Jim C. Nasby
On Tue, Apr 19, 2005 at 11:01:26PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Actually, the planner (at least in 7.4) isn't smart enough to consider if the sort would fit in memory or not. Really? Have you read cost_sort()? It's certainly possible

Re: [PERFORM] Slow copy with little CPU/disk usage

2005-04-20 Thread Jim C. Nasby
No, this is a single process. And there's known issues with context storms on Xeons, so that might be what you're seeing. On Tue, Apr 19, 2005 at 09:37:21PM -0700, Mischa Sandberg wrote: Quoting Tom Lane [EMAIL PROTECTED]: Jim C. Nasby [EMAIL PROTECTED] writes: A friend of mine has

Re: [PERFORM] Slow copy with little CPU/disk usage

2005-04-20 Thread Jim C. Nasby
No, he's using either COPY or \COPY. On Wed, Apr 20, 2005 at 12:34:27AM -0400, Greg Stark wrote: Jim C. Nasby [EMAIL PROTECTED] writes: What's really odd is that neither the CPU or the disk are being hammered. The box appears to be pretty idle; the postgresql proces is using 4-5% CPU

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

2005-04-22 Thread Jim C. Nasby
could use \timing. In any case, it's not valid to use pgadmin to time things. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow

Re: [PERFORM] Sort and index

2005-04-22 Thread Jim C. Nasby
the wrong choice here. BTW, changing random_page_cost to 3 or 4 doesn't change the plan. On Tue, Apr 19, 2005 at 10:40:41PM -0500, Jim C. Nasby wrote: On Tue, Apr 19, 2005 at 11:01:26PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Actually, the planner (at least in 7.4) isn't smart

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

2005-04-22 Thread Jim C. Nasby
not a valid test case (and by the way, this is true no matter what database you're looking at. Multiuser access is where you uncover your real bottlenecks.) -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828

[PERFORM] Interesting numbers on a CREATE INDEX

2005-04-22 Thread Jim C. Nasby
be thinking wrong. BTW, the column I'm indexing is a bigint with a low correlation. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go

Re: [PERFORM] Sort and index

2005-04-22 Thread Jim C. Nasby
On Fri, Apr 22, 2005 at 10:08:06PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: I've run some performance tests. The actual test case is at http://stats.distributed.net/~decibel/timing.sql, and the results are at http://stats.distributed.net/~decibel/timing.log

Re: [PERFORM] Index bloat problem?

2005-04-22 Thread Jim C. Nasby
)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow

Re: [PERFORM] Sort and index

2005-04-24 Thread Jim C. Nasby
On Sat, Apr 23, 2005 at 01:00:40AM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Feel free to propose better cost equations. Where would I look in code to see what's used now? All the gold is hidden in src/backend/optimizer/path/costsize.c

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

2005-04-27 Thread Jim C. Nasby
through to the mailing list cleanly -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what

Re: [PERFORM] Kernel Resources and max_connections

2005-05-03 Thread Jim C. Nasby
be set in /boot/loader.conf. hw.ata.wc=0 is an example (which you want to set on any box with IDE drives if you want fsync to actually do what it thinks it's doing). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team

Re: [PERFORM] COPY vs INSERT

2005-05-06 Thread Jim C. Nasby
) ); I'm not sure how standard that is or if other databases support it. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow

Re: [PERFORM] COPY vs INSERT

2005-05-08 Thread Jim C. Nasby
, but it's been a few years... -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what

Re: [PERFORM] [GENERAL] Hash index vs. b-tree index (PostgreSQL

2005-05-09 Thread Jim C. Nasby
On Tue, May 10, 2005 at 02:38:41AM +1000, Neil Conway wrote: Jim C. Nasby wrote: Having indexes that people shouldn't be using does add confusion for users, and presents the opportunity for foot-shooting. Emitting a warning/notice on hash-index creation is something I've suggested

Re: [PERFORM] [GENERAL] Hash index vs. b-tree index (PostgreSQL

2005-05-10 Thread Jim C. Nasby
On Tue, May 10, 2005 at 10:14:11AM +1000, Neil Conway wrote: Jim C. Nasby wrote: No, hash joins and hash indexes are unrelated. I know they are now, but does that have to be the case? I mean, the algorithms are fundamentally unrelated. They share a bit of code such as the hash functions

Re: [PERFORM] [GENERAL] Hash index vs. b-tree index (PostgreSQL

2005-05-10 Thread Jim C. Nasby
the entire index every time it looks like a different bucket size would help.) -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD

Re: [PERFORM] [GENERAL] Hash index vs. b-tree index (PostgreSQL

2005-05-10 Thread Jim C. Nasby
On Tue, May 10, 2005 at 11:49:50AM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: What's the challange to making it adaptive, comming up with an algorithm that gives you the optimal bucket size (which I would think there's research on...) or allowing the index to accommodate

Re: [PERFORM] PGSQL Capacity

2005-05-10 Thread Jim C. Nasby
)--- TIP 7: don't forget to increase your free space map settings -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go

Re: [PERFORM] Configing 8 gig box.

2005-05-10 Thread Jim C. Nasby
contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want

Re: [PERFORM] Partitioning / Clustering

2005-05-10 Thread Jim C. Nasby
: syncronous). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what

Re: [PERFORM] Partitioning / Clustering

2005-05-10 Thread Jim C. Nasby
out Bizgres. (http://pgfoundry.org/projects/bizgres/) I'm sure your insights would be most welcome. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you

Re: [PERFORM] Sort and index

2005-05-11 Thread Jim C. Nasby
), but that alone isn't enough to explain the difference. On Wed, May 11, 2005 at 05:59:10PM +0200, Manfred Koizar wrote: On Sun, 24 Apr 2005 17:01:46 -0500, Jim C. Nasby [EMAIL PROTECTED] wrote: Feel free to propose better cost equations. I did. More than once. estimated index scan cost

Re: [PERFORM] Partitioning / Clustering

2005-05-11 Thread Jim C. Nasby
stays up and it's fast. Though granted, LJ is quite a bit faster than it was 6 months ago. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want

Re: [PERFORM] Sort and index

2005-05-14 Thread Jim C. Nasby
On Thu, May 12, 2005 at 08:54:48PM +0200, Manfred Koizar wrote: On Wed, 11 May 2005 16:15:16 -0500, Jim C. Nasby [EMAIL PROTECTED] wrote: This is divided by the number of index columns, so the index correlation is estimated to be 0.219. That seems like a pretty bad assumption to make

[PERFORM] Tuning planner cost estimates

2005-05-17 Thread Jim C. Nasby
or not. Does this sound like a good way to determine actual costs for index scans (and hopefully other access methods in the future)? If so, what would be a good way to implement this? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy

Re: [PERFORM] Tuning planner cost estimates

2005-05-20 Thread Jim C. Nasby
analyze) just to make sure we knew what the optimizer was doing, but I think we shouldn't need the info to produce cost estimates. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go

Re: [PERFORM] Tuning planner cost estimates

2005-05-20 Thread Jim C. Nasby
On Fri, May 20, 2005 at 04:47:38PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Thu, May 19, 2005 at 09:31:47AM -0700, Josh Berkus wrote: can test our formula for accuracy and precision. However, such a formula *does* need to take into account concurrent activity

Re: [PERFORM] Tuning planner cost estimates

2005-05-20 Thread Jim C. Nasby
step. I would argue that doing it that way is actually more accurate, because the overhead of explain analyze is huge and tends to swamp other factors out. As I mentioned in my other email, my tests show explain analyze select * from table is 5x slower than select count(*) from table. -- Jim C

Re: [PERFORM] Select performance vs. mssql

2005-05-29 Thread Jim C. Nasby
for that tuple. I looked on the TODO but didn't see this, maybe it fell through the cracks? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go

  1   2   3   4   5   6   7   >