Re: [PERFORM] PostgreSQL on VMWare vs Windows vs CoLinux

2004-06-02 Thread Merlin Moncure
Shachar Shemesh wrote: Greg Stark wrote: That said, I'm curious why the emulated servers performed better than the Native Windows port. My first thought is that they probably aren't syncing every write to disk so effectively they're defeating the fsyncs, allowing the host OS to buffer

Re: [PERFORM] postgres performance: comparing 2 data centers

2004-06-04 Thread Merlin Moncure
Michael wrote: I have two instances of a production application that uses Postgres 7.2, deployed in two different data centers for about the last 6 months. The sizes, schemas, configurations, hardware, and access patterns of the two databases are nearly identical, but one consistently takes at

[PERFORM] is it possible to for the planner to optimize this form?

2004-06-07 Thread Merlin Moncure
Right now, I am having trouble getting the planner to optimize queries in the form of select t.key, t.field from t a where ( select count(*) from t b where b.field a.field ) = k The subplan (either index or seq. scan) executes once for each row in t, which of

[PERFORM] in-transaction insert performance in 7.5devel

2004-06-11 Thread Merlin Moncure
I am batch inserting insert statements into a database with fsync = on. My single disk system is on a 10k drive...even though I am inside a transaction there is at least 1 file sync per row insert. Is this normal? On my hardware, which is pretty quick, I am limited to about 50 inserts/sec. If

Re: [PERFORM] in-transaction insert performance in 7.5devel

2004-06-11 Thread Merlin Moncure
Tom Lane wrote: Merlin Moncure [EMAIL PROTECTED] writes: I am batch inserting insert statements into a database with fsync = on. My single disk system is on a 10k drive...even though I am inside a transaction there is at least 1 file sync per row insert. Are you certain you're inside

Re: [PERFORM] in-transaction insert performance in 7.5devel

2004-06-17 Thread Merlin Moncure
Was that 15% before or after updating from CVS? The more I think about the looping aspect the less I like it, so I'd prefer not to pursue making the unlock change for real. But if it's really a 15% win then maybe we need to... regards, tom lane After. So far, I

Re: [PERFORM] postgresql and openmosix migration

2004-06-23 Thread Merlin Moncure
Bill wrote: Ok, so maybe someone on this group will have a better idea. We have a database of financial information, and this has literally millions of entries. I have installed indicies, but for the rather computationally demanding processes we like to use, like a select query to find the

Re: [PERFORM] Working on huge RAM based datasets

2004-07-09 Thread Merlin Moncure
What is it about the buffer cache that makes it so unhappy being able to hold everything? I don't want to be seen as a cache hit fascist, but isn't it just better if the data is just *there*, available in the postmaster's address space ready for each backend process to access it, rather than

Re: [PERFORM] Working on huge RAM based datasets

2004-07-12 Thread Merlin Moncure
Jan wrote: The disk cache on most operating systems is optimized. Plus, keeping shared buffers low gives you more room to bump up the sort memory, which will make your big queries run faster. Plus, the situation will change dramatically with 7.5 where the disk cache will have less

Re: [PERFORM] Working on huge RAM based datasets

2004-07-12 Thread Merlin Moncure
Andy wrote: Whether the OS caches the data or PG does, you still want it cached. If your sorting backends gobble up the pages that otherwise would be filled with the database buffers, then your postmaster will crawl, as it'll *really* have to wait for stuff from disk. In my scenario, you'd

Re: [PERFORM] arrays and indexes

2004-07-26 Thread Merlin Moncure
Ross wrote: Hi all - I've got a schema I'm working on modifying, nad I need some help getting the best performance out. The orginal schema has a many to many linkage between a couple tables, using a two column linkage table. This is used to represent groups of people and their relationship to

[PERFORM] best way to fetch next/prev record based on index

2004-07-27 Thread Merlin Moncure
I am in a situation where I have to treat a table as logically ordered based on an index. Right now I'm doing this via queries, and a I need a better way to do it. Cursors do not meet my requirements, because they are always insensitive. Also, my performance requirements are extreme...I need

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-27 Thread Merlin Moncure
Greg Stark wrote: do it for multi-column keys. It seems it would be nice if some syntax similar to (a,b,c) (a1,b1,c1) worked for this. Hum. It would seem my intuition matches the SQL92 spec and Postgres gets this wrong. [...] Even if Postgres did this right I'm not sure that would

[PERFORM] best way to fetch next/prev record based on index

2004-07-27 Thread Merlin Moncure
SELECT * FROM t WHERE (a = a1 AND b=b1 AND c=c1) ORDER BY a,b,c LIMIT 1 OFFSET 1; using the way LIMIT cuts down on sort time (I've never tried it with both LIMIT and OFFSET, though; you could always use LIMIT 2 and skip a record client-side if that works better). Don't want to further

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-27 Thread Merlin Moncure
Greg wrote: One thing that can help is to add ORDER BY a,b,c LIMIT 1 to your query. That will virtually guarantee that it uses an index scan, which will at least avoid making it scan all the records *after* finding the match. However it still doesn't seem to make Postgres use an Index Cond

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-27 Thread Merlin Moncure
So, for a table t with a three part key over columns a,b,c, the query to read the next value from t for given values a1, b1, c1 is select * from t where a = a1 and (a a1 or b = b1) and (a a1 or b b1 or c c1) You mut not rely on such trickery to get any

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-27 Thread Merlin Moncure
select * from t where a = a1 and (a a1 or b = b1) and (a a1 or b b1 or c c1) In about 95% of cases, the planner correctly selects the index t(a,b,c) and uses it. I'm surprised it's that good. Why not do It is. In fact, it's so good, I mistakenly assumed it

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-27 Thread Merlin Moncure
Hmm, it sure seems like there ought to be an easy way to do this... Here is the only alternative that I see: create function column_stacker(text[] columns, text[] types) returns text [...] language 'C' immutable; the above function stacks the columns together in a single string for easy range

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-27 Thread Merlin Moncure
Markus wrote: The basic problem is the planner can't always match the query to the index. So, either the planner has to be helped/fixed or I have to explore another solution. This seems to happen most when the 'a' column has very poor selectivity. In this case, the planner will only

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-28 Thread Merlin Moncure
Greg Stark [EMAIL PROTECTED] writes: This approach won't get the optimizer to actually use an index for these comparisons, but it will fix the semantics to match the spec. Later we can either improve the optimizer to detect expressions like this (which I think would be cooler since some

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-28 Thread Merlin Moncure
Greg Stark [EMAIL PROTECTED] writes: Removing ,=,,= would be trivial. ... and not backwards-compatible. If we did that then cases involving unlabeled row expressions would no longer work as they did in prior releases. For example select (1,2,3) (4,5,6); is accepted by all

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-29 Thread Merlin Moncure
Just an update on this: queries in the 'next key' form on fields(a,b,c) only ever use the index for the first field (a). I just never noticed that before...in most cases this is selective enough. In most logical cases in multi part keys the most important stuff comes first. Curiously, queries

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-29 Thread Merlin Moncure
Greg Stark wrote: Well I'm not sure whether you caught it, but Tom did come up with a work-around that works with the current infrastructure if all the columns involved are the same datatype. You can create a regular btree index on the expression array[a,b,c] and then do your lookup using

Re: [PERFORM] best way to fetch next/prev record based on index

2004-07-29 Thread Merlin Moncure
Merlin Moncure [EMAIL PROTECTED] writes: I would much rather see postgres 'get' (a,b,c) (a1, b1, c1)...if there is even a chance this is possible, I'll direct my efforts there. For the ISAM context this whole discussion is kinda moot, because you really don't want to have to plan

Re: [PERFORM] my boss want to migrate to ORACLE

2004-07-30 Thread Merlin Moncure
Stephane wrote: Hi everyone,   somebody can help me??? my boss want to migrate to ORACLE #fsync = true [snip] Are you using battery baked RAID? Your problem is probably due to the database syncing all the time. With fsync one, you get 1 sync per transaction that

Re: [PERFORM] What kind of performace can I expect and how to measure?

2004-08-02 Thread Merlin Moncure
Hi Merlin, The 9206 ms time is what the database actually spent gathering the data and sending it to you. This is non-negotiable unless you bump up hardware, etc, or fetch less data. This time usually scales linearly (or close to it) with the size of the dataset you fetch. The

Re: [PERFORM] NOT IN query takes forever

2004-08-03 Thread Merlin Moncure
Trying to run this query: EXPLAIN ANALYSE select * FROM trans WHERE query_id NOT IN (select query_id FROM query) but it will remain like that forever (cancelled after 30 min). explain analyze actually runs the query to do timings. Just run explain and see what you come up with. More than

Re: [PERFORM] NOT IN query takes forever

2004-08-03 Thread Merlin Moncure
Try bumping up shared buffers some and sort mem as much as you safely can. Thank you, that did it! With shared_buffers = 3000 # min 16, at least max_connections*2, 8KB each sort_mem = 128000 # min 64, size in KB it takes 3 seconds (my hardware is not

FW: [PERFORM] Tuning queries on large database

2004-08-04 Thread Merlin Moncure
[forwarded to performance] The result is that for short queries (Q1 and Q2) it runs in a few seconds on both Oracle and PG. The difference becomes important with Q3 : 8 seconds with oracle 80 sec with PG and too much with Q4 : 28s with oracle 17m20s with PG !

[PERFORM] tablespaces and ramdisks

2004-08-09 Thread Merlin Moncure
If you use tablespaces to put a high-update, non-critical table on a ramdisk, will updates to that table will still cause the WAL files to sync? I'm looking for a way to turn off syncing completely for a table. Temporary tables do this, but they can only be accessed from a single backend. Merlin

Re: [PERFORM] [HACKERS] fsync vs open_sync

2004-08-10 Thread Merlin Moncure
Anyway, with fsync enabled using standard fsync(), I get roughly 300-400 inserts per second. With fsync disabled, I get about 7000 inserts per second. When I re-enable fsync but use the open_sync option, I can get about 2500 inserts per second. You are getting 300-400 inserts/sec with fsync

Re: [PERFORM] Hardware upgrade for a high-traffic database

2004-08-11 Thread Merlin Moncure
On Wed, 2004-08-11 at 17:51, Merlin Moncure wrote: One thing you might consider is materialized views. Your aggregate functions are killing you...try to avoid using them (except min/max on an index). Just watch out for mutable functions like now(). http://www.varlena.com/varlena

Re: [PERFORM] Hardware upgrade for a high-traffic database

2004-08-11 Thread Merlin Moncure
We do have a lot of INSERT/UPDATE calls, specifically on tables that track user sessions, then of course things like comments, etc (where we'll see 10-30 INSERT's per second, with TEXT field, and hundreds of reads per second). Additionally, our system does use a lot of aggregate functions.

Re: [PERFORM] Hardware upgrade for a high-traffic database

2004-08-12 Thread Merlin Moncure
This example looks fine, but since userid 51 evidently only has 35 posts, there's not much time needed to read 'em all and sort 'em. The place where the double-column index will win big is on userids with hundreds of posts. You have to keep in mind that each index costs time to maintain

Re: [PERFORM] Hardware upgrade for a high-traffic database

2004-08-12 Thread Merlin Moncure
I don't have a huge amount of experience with this in pg, but one of the tricks we do in the ISAM world is a 'reverse date' system, so that you can scan forwards on the key to pick up datetimes in descending order. This is often a win because the o/s cache may assume read/forwards giving you

Re: [PERFORM] fsync vs open_sync

2004-08-13 Thread Merlin Moncure
There is also the fact that NTFS is a very slow filesystem, and Linux is a lot better than Windows for everything disk, caching and IO related. Try to copy some files in NTFS and in ReiserFS... I'm not so sure I would agree with such a blanket generalization. I find NTFS to be very

Re: [PERFORM] odbc/ado problems

2004-08-31 Thread Merlin Moncure
I'm new here, so hello to everybody! I'm in a deep truble using postgesSQL 7.2.0 on a low-end pc with SUSE 8. I'm using some databases from that pc through odbc (7.3.200). Until now i had no problems with this solution, everithing worked fine. But today i wrote a small app, that

Re: [PERFORM] odbc/ado problems

2004-08-31 Thread Merlin Moncure
thanks for the quick answer! My db driver is the native MS ADO, and from Delphi i use the AODExpress components which are wrapper classes to reach the ActiveX components from delhpi. The strange behaviour of that query is, that all other queries executed in this environment are running fast,

Re: [PERFORM] seqscan instead of index scan

2004-09-01 Thread Merlin Moncure
On Mon, 30 Aug 2004, Martin Sarsale wrote: Multicolumn indexes can only be used if the clauses involving the indexed columns are joined with AND. For instance, SELECT name FROM test2 WHERE major = constant OR minor = constant; You can use DeMorgan's Theorem to transform an OR clause to

Re: [PERFORM] fsync vs open_sync

2004-09-03 Thread Merlin Moncure
There is also the fact that NTFS is a very slow filesystem, and Linux is a lot better than Windows for everything disk, caching and IO related. Try to copy some files in NTFS and in ReiserFS... I'm not so sure I would agree with such a blanket generalization. I find NTFS to be

Re: [PERFORM] [pgsql-hackers-win32] Performance on Win32 vs Cygwin

2004-10-15 Thread Merlin Moncure
Thanks Magnus, So are we correct to rely on - 8 being slower than 7.x in general and - 8 on Win32 being a little faster than 8 on Cygwin? Will the final release of 8 be faster than the beta? I'm pretty certain that previous to 8.0 no win32 based postgesql properly sync()ed the files.

[PERFORM] spurious function execution in prepared statements.

2004-09-30 Thread Merlin Moncure
OK, I have a situation that might be a performance problem, a bug, or an unavoidable consequence of using prepared statements. The short version is that I am getting function executions for rows not returned in a result set when they are in a prepared statement. In other words, I have a query:

Re: [PERFORM] [HACKERS] spurious function execution in prepared statements.

2004-09-30 Thread Merlin Moncure
Here's another workaround that may let you use a prepared statement: prepare ps(...) as select f(c) from (select c from t where [expr] limit 1) as t1 -Mike I was just exploring that. In fact, the problem is not limited to prepared statements...it's just that they are more likely to run a

Re: [PERFORM] spurious function execution in prepared statements.

2004-09-30 Thread Merlin Moncure
Stephan Szabo wrote: On Thu, 30 Sep 2004, Merlin Moncure wrote: OK, I have a situation that might be a performance problem, a bug, or an unavoidable consequence of using prepared statements. The short version is that I am getting function executions for rows not returned in a result

[PERFORM] postgresql amd-64

2004-11-05 Thread Merlin Moncure
Does anybody have any experiences with postgresql 7.4+ running on amd-64 in 64 bit mode? Specifically, does it run quicker and if so do the performance benefits justify the extra headaches running 64 bit linux? Right now I'm building a dual Opteron 246 with 4 gig ddr400. Merlin

Re: [PERFORM] postgresql amd-64

2004-11-05 Thread Merlin Moncure
I have two dual opteron 248's with 4g of ram each, 6x36G 15k rpm ultra 320 scsi disks in hardware raid 5, and they are by far the fastest machines I've user used. As far as this headache of using 64 bit Linux, I've experienced no such thing. I'm using gentoo on both machines, which are

Re: [PERFORM] postgresql amd-64

2004-11-08 Thread Merlin Moncure
Good, I'll give it a shot and see what I come up with...thx. Do share your experience with us. Will do. I have to ship the server on Friday, and the parts are on order. If they come today, I'll have time to test Gentoo, Redhat 32/64, and win32 by then. If I can't get it built until

[PERFORM] scalability issues on win32

2004-11-22 Thread Merlin Moncure
Following is the promised writeup in performance related issues comparing win32 with linux x86 and linux x86-64. Unfortunately, the 64 bit portion of the test is not yet completed and won't be for a bit. However there are some telling things about the win32/linux comparison. If you are

Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32

2004-11-23 Thread Merlin Moncure
Reini Urban wrote: Merlin Moncure schrieb: A good benchmark of our application performance is the time it takes to read the entire bill of materials for a product. This is a recursive read of about 2500 records in the typical case (2408 in the test case). I always knew that COBOL

Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32

2004-11-23 Thread Merlin Moncure
Is this for Postgresql Cygwin? You surely can't mean for all server tasks - if so, I would say that's *way* off. There is a difference, but it's more along the line of single-digit percentage in my experience - provided you config your machines reasonably, of course. (In my experience,

Re: [PERFORM] Alternatives to Dell?

2004-12-01 Thread Merlin Moncure
Folks, A lot of people have been having a devilish time with Dell hardware lately. It seems like the quality control just isn't there on the Dell servers. Thing is, some companies are required to use 1st-tier or at least 2nd-tier vendors for hardware; they won't home-build. For those

Re: [PERFORM] Alternatives to Dell?

2004-12-02 Thread Merlin Moncure
On Wed, Dec 01, 2004 at 05:43:10PM -0500, Merlin Moncure wrote: Also, if choice of RAID controller is an option, I'd definitely suggest 3ware. They are cheap, have excellent linux support (including open source drivers) The drivers are open source, but the management tools

Re: [PERFORM] [pgsql-hackers-win32] scalability issues on win32

2004-12-02 Thread Merlin Moncure
This was an intersting Win32/linux comparison. I expected Linux to scale better, but I was surprised how poorly XP scaled. It reinforces our perception that Win32 is for low traffic servers. That's a bit harsh given the lack of any further investigation so far isn't it? Win32 can run

Re: [PERFORM] Tips for a system with _extremely_ slow IO?

2004-12-21 Thread Merlin Moncure
A demo I've set up for sales seems to be spending much of it's time in disk wait states. The particular system I'm working with is: Ext3 on Debian inside Microsoft VirtualPC on NTFS on WindowsXP on laptops of our sales team. As this is only for demo purposes, you might

[PERFORM] sudden drop in statement turnaround latency -- yay!.

2004-12-30 Thread Merlin Moncure
I took advantage of the holidays to update a production server (dual Opteron on win2k) from an 11/16 build (about beta5 or so) to the latest release candidate. No configuration changes were made, just a binary swap and a server stop/start. I was shocked to see that statement latency dropped by

Re: [PERFORM] Low Performance for big hospital server ..

2005-01-03 Thread Merlin Moncure
amrit wrote: I try to adjust my server for a couple of weeks with some sucess but it still slow when the server has stress in the moring from many connection . I used postgresql 7.3.2-1 with RH 9 on a mechine of 2 Xeon 3.0 Ghz and ram of 4 Gb. Since 1 1/2 yr. when I started to use the

Re: [PERFORM] first postgrreSQL tunning

2005-01-06 Thread Merlin Moncure
Hi there! I'm doing my first tunning on my postgreSQL, my server is for a small app, largest table shall never exceed 10k rows, and less than 1k transactions/day. So I don't think I should run out of resources. The machine is a Fedora Core 3.0 with 1gb ran and kernel 2.6. I'm thinking in

Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

2005-01-11 Thread Merlin Moncure
Subject: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL? I'm sorry if there's a URL out there answering this, but I couldn't find it. For those of us that need the best performance possible out of a dedicated dual-CPU PostgreSQL server, what is recommended?

Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

2005-01-11 Thread Merlin Moncure
$4000 is not going to get you much disk - If you buy components from the cheapest source I know (newegg.com) you end up around $5k with 14x36gig Raptor SATA drives and a 4U chasis with a 14xSATA built in back plane packing 2x9500S AMCC Escalade RAID cards, which are supported in Linux, 4Gig

Re: [PERFORM] query optimization help

2005-01-14 Thread Merlin Moncure
Please post in plaintext, not html where possible. Your group by clause was 'myst'...was that supposed to be mystate? Her is something you might try...use the original query form and create a function which resolves the state code from the input data...you are already doing that with upper.

Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

2005-01-14 Thread Merlin Moncure
Alex wrote: Without starting too much controvesy I hope, I would seriously recommend you evaluate the AMCC Escalade 9500S SATA controller. It has many of the features of a SCSI controler, but works with cheaper drives, and for half the price or many SCSI controlers (9500S-8MI goes for abour

Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

2005-01-14 Thread Merlin Moncure
Greg wrote: Josh Berkus josh@agliodbs.com writes: Merlin, I think the danger about SATA is that many SATA components are not server quality, so you have to be more careful about what you buy. For example, you can't just assume your SATA backplane has hot swap lights (got bit by

Re: [PERFORM] OFFSET impact on Performance???

2005-01-20 Thread Merlin Moncure
Andrei: Hi to all, I have the following 2 examples. Now, regarding on the offset if it is small(10) or big(5) what is the impact on the performance of the query?? I noticed that if I return more data's(columns) or if I make more joins then the query runs even slower if the OFFSET is

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-20 Thread Merlin Moncure
No please do not talk about this again ... I'm looking about a PostgreSQL solution ... I know RAC ... and I'm not able to pay for a RAC certify hardware configuration plus a RAC Licence. Are you totally certain you can't solve your problem with a single server solution? How about: Price out

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-20 Thread Merlin Moncure
I am also very interesting in this very question.. Is there any way to declare a persistant cursor that remains open between pg sessions? This would be better than a temp table because you would not have to do the initial select and insert into a fresh table and incur those IO costs, which

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-20 Thread Merlin Moncure
this will only work unchanged if the index is unique. imagine , for example if you have more than 50 rows with the same value of col. one way to fix this is to use ORDER BY col,oid nope! oid is 1. deprecated 2. not guaranteed to be unique even inside a (large) table. Use a sequence

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-20 Thread Merlin Moncure
Dealing about the hardware, for the moment we have only a bi-pentium Xeon 2.8Ghz with 4 Gb of RAM ... and we saw we had bad performance results ... so we are thinking about a new solution with maybe several servers (server design may vary from one to other) ... to get a kind of cluster to get

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-21 Thread Merlin Moncure
Now I read all the posts and I have some answers. Yes, I have a web aplication. I HAVE to know exactly how many pages I have and I have to allow the user to jump to a specific page(this is where I used limit and offset). We have this feature and I cannot take it out. If your working set

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-21 Thread Merlin Moncure
Technically, you can also set up a rule to do things on a select with DO ALSO. However putting update statements in there would be considered (at least by me) very bad form. Note that this is not a trigger because it does not operate at the row level [I know you knew that already :-)].

Re: [PERFORM] How to boost performance of ilike queries ?

2005-01-24 Thread Merlin Moncure
Russell wrote: I am not sure what the effect of it being prepared will be, however I've had much success with the method above without the queries being prepared. Others may be able to offer advice about if prepare will effect it. There are two general cases I tend to use prepared queries.

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-26 Thread Merlin Moncure
The problem with this approach is TTFB (Time to first Byte). The initial query is very slow, but additional requests are fast. In most situations we do not want the user to have to wait a disproportionate amount of time for the initial query. If this is the first time using the system this

Re: [PERFORM] Flattening a kind of 'dynamic' table

2005-01-27 Thread Merlin Moncure
Alexandre wrote: Here a normal listing of design.product_department_time: product_id | department_id | req_time +---+-- 906 | A | 3000 906 | C | 3000 906 | D | 1935 907 | A

Re: [PERFORM] Ideal disk setup for Postgresql 7.4?

2005-01-27 Thread Merlin Moncure
Steve wrote: Okay. Darn. While I don't write the queries for the application, I do interact with the company frequently. Their considering moving the queries into the database with PL/pgSQL. Currently their queries are done through ProvIV development using ODBC. Will context switching be

Re: [PERFORM] Flattening a kind of 'dynamic' table

2005-01-27 Thread Merlin Moncure
Alexandre wrote: On Thu, 27 Jan 2005 10:44:45 -0500, Merlin Moncure [EMAIL PROTECTED] wrote: Alexandre wrote: ok, you have a couple of different options here. The first thing that jumps out at me is to use arrays to cheat using arrays. Let's start with the normalized result set

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-27 Thread Merlin Moncure
Greg Stark wrote: test= create or replace function array_push (anyarray, anyelement) returns anyarray as 'select $1 || $2' language sql immutable strict; CREATE FUNCTION test= create aggregate array_aggregate (basetype=anyelement, sfunc=array_push, stype=anyarray, initcond = '{}'); CREATE

Re: [PERFORM] Flattening a kind of 'dynamic' table

2005-01-27 Thread Merlin Moncure
Alexandre wrote: Right. I expanding departments into columns is basically a dead end. First of all, SQL is not really designed to do this, and second of all (comments continued below) Ok, I got it. The basic message is to avoid making columns out of rows yes. This is wrong. like

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

2005-02-01 Thread Merlin Moncure
Hi all, 1) What kind of performance gain can I expect switching from 7.1 to 7.4 (or 8.0)? Obviously I'm doing my own testing, but I'm not very impressed by 8.0 speed, may be I'm doing testing on a low end server... 8.0 gives you savepoints. While this may not seem like a big

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

2005-02-02 Thread Merlin Moncure
By now, our system has never used stored procedures approach, due to the fact that we're staying on the minimum common SQL features that are supported by most db engines. I realize though that it would provide an heavy performance boost. I feel your pain. Well, sometimes you have to bite

Re: [PERFORM] Planner really hates nested loops

2005-02-03 Thread Merlin Moncure
Magnus wrote: I'm hoping someone can shed some light on these results. Not without a lot more detail on how you *got* the results. What exactly did you do to force the various plan choices? (I see some ridiculous choices of indexscans, for instance, suggesting improper use of

Re: [PERFORM] Flattening a kind of 'dynamic' table

2005-02-04 Thread Merlin Moncure
Alexandre Leclerc wrote: Sorry for jumping in on this thread so late -- I haven't been able to select * from crosstab( 'select product_id, department_id, req_time from product_department_time order by 1', 'select ''A'' union all select ''C'' union all select ''D''' ) as

Re: [PERFORM] How can I make use of both CPUs in a dual processor

2005-02-09 Thread Merlin Moncure
Thanks John. Well as I mentioned. I have a Dual AMD Opteron 64 2.4ghz, 15k rpm SCSI Disks, 4GB of memory. Disks are pretty fast and memory should be more than enough. Currently we dont have many concurrent connections. I run PG 8.0.1 on Fedora Core 3 When I now run the batch job, one

Re: [PERFORM] seq scan cache vs. index cache smackdown

2005-02-15 Thread Merlin Moncure
It seems inevitable that Postgres will eventually eliminate that redundant layer of buffering. Since mmap is not workable, that means using O_DIRECT to read table and index data. What about going the other way and simply letting the o/s do all the caching? How bad (or good) would the

Re: [PERFORM] seq scan cache vs. index cache smackdown

2005-02-21 Thread Merlin Moncure
Magnus Hagander wrote: I don't think that's correct either. Scatter/Gather I/O is used to SQL Server can issue reads for several blocks from disks into it's own buffer cache with a single syscall even if these buffers are not sequential. It did make significant performance improvements

Re: [PERFORM] Postgres on RAID5

2005-03-14 Thread Merlin Moncure
Alex Turner wrote: 35 Trans/sec is pretty slow, particularly if they are only one row at a time. I typicaly get 200-400/sec on our DB server on a bad day. Up to 1100 on a fresh database. Well, don't rule out that his raid controller is not caching his writes. His WAL sync method may be

Re: [PERFORM] Speeding up select distinct

2005-03-16 Thread Merlin Moncure
Consider this query: SELECT distinct owner from pictures; [...] Any ideas, apart from more or less manually maintaining a list of distinct owners in another table ? you answered your own question. With a 20 row owners table, you should be directing your efforts there group by is faster

Re: [PERFORM] Speeding up select distinct

2005-03-16 Thread Merlin Moncure
I just wished there was a means to fully automate all this and render it transparent to the user, just like an index. Merlin Voila! Merlin p.s. normalize your data always! I have this: pictures( PictureID serial PRIMARY KEY, Owner integer NOT NULL REFERENCES users,

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

2005-04-18 Thread Merlin Moncure
d) self-join with a function ;) EXPLAIN ANALYZE SELECT * FROM (SELECT n, id2username(n) AS username FROM (SELECT DISTINCT n FROM aaa) AS values) AS v_lookup RIGHT JOIN aaa USING (n); That's pretty clever. It sure seems like the server was not caching the results of the function...maybe

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

2005-04-21 Thread Merlin Moncure
John A Meinel [EMAIL PROTECTED] writes: Joel Fradkin wrote: Postgres was on the second run Total query runtime: 17109 ms. Data retrieval runtime: 72188 ms. 331640 rows retrieved. How were you measuring data retrieval time? I suspect he's using pgadmin. We've seen reports before

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

2005-04-25 Thread Merlin Moncure
In practice, we have watched Windows evolve in such a fashion with respect to multiuser support, and, in effect, it has never really gotten it. Microsoft started by hacking something on top of MS-DOS, and by the time enough applications had enough dependancies on the way that worked, it has

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

2005-04-25 Thread Merlin Moncure
I am waiting to here back from Josh on using cursors and trying to flatten long running views. I am a little disappointed I have not understood enough to get my analyzer to use the proper plan, we had to set seqscan off to get the select from response_line to work fast and I had to turn off

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

2005-05-13 Thread Merlin Moncure
Joel wrote: I have been following threads (in case you don't know I bought a 4 proc Dell recently) and the Opteron seems the way to go. I just called HP for a quote, but don't want to make any mistakes. [snip] At your level of play it's the DL585. Have you checked out http://www.swt.com? Merlin

Re: [PERFORM] could not send data to client:

2005-06-17 Thread Merlin Moncure
Justin wrote: I have 6 Windows PC in a test environment accessing a very small Postgres DB on a 2003 Server. The PC's access the database with a cobol app via ODBC. 3 of the PC's operate very efficiently and quickly. 3 of them do not. The 3 that do not are all new Dell XP Pro with SP2. They all

Re: [PERFORM] Performance - moving from oracle to postgresql

2005-06-27 Thread Merlin Moncure
There are some immediate questions from our engineers about performance - Oracle has one particular performance enhancement that Postgres is missing. If you do a select that returns 100,000 rows in a given order, and all you want are rows 99101 to 99200, then Oracle can do that very

Re: [PERFORM] PERFORMANCE ISSUE ODBC x LIBPQ C++ Application

2005-06-27 Thread Merlin Moncure
Hi ! My company is evaluating to compatibilizate our system (developed in C++) to PostgreSQL. Our programmer made a lot of tests and he informed me that the performance using ODBC is very similar than using libpq, even with a big number of simultaneous connections/queries. Of course that

Re: [PERFORM] [HACKERS] How two perform TPC-H test on postgresql-8.0.2

2005-06-27 Thread Merlin Moncure
[moved to pgsql-performance] Currently I want to take a TPC-H test on postgresql-8.0.2. I have downloaded the DBGEN and QGEN from the homepage of TPC. But I encountered many problems which forced me to request some help. 1. How to load the data from flat file generated by dbgen tool?

[PERFORM] tricky query

2005-06-28 Thread Merlin Moncure
I need a fast way (sql only preferred) to solve the following problem: I need the smallest integer that is greater than zero that is not in the column of a table. In other words, if an 'id' column has values 1,2,3,4,6 and 7, I need a query that returns the value of 5. I've already worked out a

Re: [PERFORM] tricky query

2005-06-28 Thread Merlin Moncure
Not so bad. Try something like this: SELECT min(id+1) as id_new FROM table WHERE (id+1) NOT IN (SELECT id FROM table); Now, this requires probably a sequential scan, but I'm not sure how you can get around that. Maybe if you got trickier and did some ordering and limits. The above

Re: [PERFORM] tricky query

2005-06-28 Thread Merlin Moncure
John Meinel wrote: See my follow up post, which enables an index scan. On my system with 90k rows, it takes no apparent time. (0.000ms) John =:- Confirmed. Hats off to you, the above some really wicked querying. IIRC I posted the same question several months ago with no response and had

Re: [PERFORM] tricky query

2005-06-28 Thread Merlin Moncure
Merlin Moncure wrote: I need a fast way (sql only preferred) to solve the following problem: I need the smallest integer that is greater than zero that is not in the column of a table. I've already worked out a query using generate_series (not scalable) and pl/pgsql. An SQL only

Re: [PERFORM] tricky query

2005-06-28 Thread Merlin Moncure
On Tue, Jun 28, 2005 at 12:02:09 -0400, Merlin Moncure [EMAIL PROTECTED] wrote: Confirmed. Hats off to you, the above some really wicked querying. IIRC I posted the same question several months ago with no response and had given up on it. I think your solution (smallest X1 not in X

  1   2   3   4   5   6   7   8   9   10   >