Re: [HACKERS] [Bizgres-general] A Guide to Constraint Exclusion (

2005-07-14 Thread Luke Lonergan
CE checks will not currently recognise STABLE functions within a query. So WHERE clauses such as DateKey CURRENT DATE will not cause exclusion because CURRENT DATE is a STABLE function. CE checks are not made when the parent table is involved in a join. Is this also the case where

Re: [HACKERS] [Bizgres-general] A Guide to Constraint Exclusion

2005-07-14 Thread Luke Lonergan
I assume this is a TODO and just not in your first batch of work? It seems like a pretty important piece eventually. Are there any fundamental difficulties with handling joins eventually? Its a reasonable size piece of work, and could not be reworked in time for 8.1. As you've said

Re: [HACKERS] [Bizgres-general] A Guide to Constraint Exclusion (

2005-07-14 Thread Luke Lonergan
Hannu, My comment was too terse. What I meant was that you can't do dynamic exclusion based upon the results of a join. i.e. PPUC2 but what about _static_ exlusion based on constraints ? I mean if there is a left side table with say a single partition having CHECK(id_order BETWEEN 1

Re: [HACKERS] [Bizgres-general] A Guide to Constraint Exclusion (

2005-07-14 Thread Luke Lonergan
Simon, SELECT FROM Fact, Dimension WHERE Fact.Key = Dimension.Key AND Dimension.DescriptiveField = 'Blah' So, what happens with this: SELECT FROM Fact, Dimension WHERE Fact.Key = Dimension.Key AND Fact.part = 100; With Fact defined with 3 partitions: CHECK(Part BETWEEN1 AND 1000)

Re: [HACKERS] [Bizgres-general] A Guide to Constraint Exclusion (

2005-07-14 Thread Luke Lonergan
Simon, It's the first time I've thought to compare the constraint predicates on joined tables based upon the join restriction. That's possible, but would take some time to work out. I've argued that such a construct is not common. I'm open to suggestions about what *is* common... I also

Re: [HACKERS] multibyte regression tests

2005-07-14 Thread Luke Lonergan
They should be completely portable (provided the script in src/test/mb runs on that platform). - Luke On 7/12/05 10:54 AM, Andrew Dunstan [EMAIL PROTECTED] wrote: Should we add the multibyte regression tests to the buildfarm test suite? Does anyone know how portable they are? I guess

Re: [HACKERS] multibyte regression tests

2005-07-15 Thread Luke Lonergan
Andrew, Good. So should we roll this up into the standard regression suite? Why are these tests separate? Is it just that they need a UTF8 encoded db rather than an SQL-ASCII encoded db? I think that was the idea - the src/test/mb kit was already there and was designed to create the UTF8

Re: [HACKERS] A Guide to Constraint Exclusion (Partitioning)

2005-07-22 Thread Luke Lonergan
Tom, On 7/22/05 3:32 PM, Tom Lane [EMAIL PROTECTED] wrote: but the scan all rows will only happen if no index is provided on DateKey in the child tables. Otherwise the planner will probably select plans like this: - Index Scan using i1 on sales_jan_dateitemoutlet

Re: [HACKERS] For review: Server instrumentation patch

2005-07-24 Thread Luke Lonergan
Tom, On 7/24/05 4:47 PM, Tom Lane [EMAIL PROTECTED] wrote: If we went down this path at all, I'd be inclined to just deprecate and eventually remove server-side COPY altogether. Not sure about the performance costs of that, though. Interesting problem, in practice you've got to transfer

Re: [HACKERS] Remote administration functionality

2005-07-31 Thread Luke Lonergan
Bruce, On 7/31/05 6:58 AM, Bruce Momjian pgman@candle.pha.pa.us wrote: TODO has: o Allow pg_hba.conf settings to be controlled via SQL This would require a new global table that is dumped to flat file for use by the postmaster. We do a similar thing for

Re: [HACKERS] Remote administration functionality

2005-07-31 Thread Luke Lonergan
Bruce, On 7/31/05 5:33 PM, Bruce Momjian pgman@candle.pha.pa.us wrote: Reload of config file and rotate log files were part of the original patch that I will try to apply. I am not sure how remote restart would work. Reload of config, refresh of IPC structures should be equivalent. It all

Re: [HACKERS] SHMMAX seems entirely broken in OS X 10.4.2

2005-08-29 Thread Luke Lonergan
Tom, On 8/29/05 5:18 PM, Tom Lane [EMAIL PROTECTED] wrote: Has anyone been able to set kern.sysv.shmmax above 4MB at all in latest OS X? I just spent a while trying what seemed every possible permutation of setting up /etc/sysctl.conf and editing /etc/rc directly, and it just fails

Re: [HACKERS] SHMMAX seems entirely broken in OS X 10.4.2

2005-08-29 Thread Luke Lonergan
Tom, On 8/29/05 6:41 PM, Tom Lane [EMAIL PROTECTED] wrote: Interesting. I wonder if there's some bit of code that thinks that SHMALL is measured in bytes (contrary to OSX's general convention that it's measured in pages). I don't know, but I agree that the behavior has changed from Panther

Re: [HACKERS] SHMMAX seems entirely broken in OS X 10.4.2

2005-08-30 Thread Luke Lonergan
Tom, On 8/29/05 9:37 PM, Tom Lane [EMAIL PROTECTED] wrote: Did that. Set shmall first, shmall second, both together in one sysctl command; no joy anywhere. Are you trying this on fully up-to-date Tiger? Did you try the values I sent earlier? If you set them both in /etc/sysctl.conf and

Re: [HACKERS] SHMMAX seems entirely broken in OS X 10.4.2

2005-08-30 Thread Luke Lonergan
Jeff, On 8/30/05 5:28 AM, Jeff Trout [EMAIL PROTECTED] wrote: Just ran software update and (besides a couple apps) it had a security update. I just did that to test this. skittlebrau:~ postgres$ grep shm /etc/rc Luke-Lonergans-Computer:~ lukelonergan$ cat /etc/sysctl.conf

Re: [HACKERS] postgresql clustering

2005-09-29 Thread Luke Lonergan
Daniel, From what I've researched, the concepts and practices seem to fall under one of two abstract categorizations: fail-over (ok... high-availability), and parallel execution (high-performance... sure). While some consider the implementation of only one of these to qualify a cluster,

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-09-29 Thread Luke Lonergan
Josh, On 9/29/05 9:54 AM, Josh Berkus josh@agliodbs.com wrote: Following an index creation, we see that 95% of the time required is the external sort, which averages 2mb/s. This is with seperate drives for the WAL, the pg_tmp, the table and the index. I've confirmed that increasing

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-09-29 Thread Luke Lonergan
Jeff, On 9/29/05 10:44 AM, Jeffrey W. Baker [EMAIL PROTECTED] wrote: On Thu, 2005-09-29 at 10:06 -0700, Luke Lonergan wrote: Looking through tuplesort.c, I have a couple of initial ideas. Are we allowed to fork here? That would open up the possibility of using the CPU and the I/O

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-09-30 Thread Luke Lonergan
Ron, On 9/30/05 1:20 PM, Ron Peacetree [EMAIL PROTECTED] wrote: That 11MBps was your =bulk load= speed. If just loading a table is this slow, then there are issues with basic physical IO, not just IO during sort operations. Bulk loading speed is irrelevant here - that is dominated by

Re: [HACKERS] postgresql clustering

2005-09-30 Thread Luke Lonergan
Dan, On 9/29/05 3:23 PM, Daniel Duvall [EMAIL PROTECTED] wrote: What about clustered filesystems? At first blush I would think the overhead of something like GFS might kill performance. Could one potentially achieve a fail-over config using multiple nodes with GFS, each having there own

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-10-03 Thread Luke Lonergan
Jeff, Josh, On 10/3/05 2:16 PM, Josh Berkus josh@agliodbs.com wrote: Jeff, Nope, LOTS of testing, at OSDL, GreenPlum and Sun. For comparison, A Big-Name Proprietary Database doesn't get much more than that either. I find this claim very suspicious. I get single-threaded reads in

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-10-03 Thread Luke Lonergan
Hannu, On 10/3/05 2:43 PM, Hannu Krosing [EMAIL PROTECTED] wrote: Just FYI, I run a count(*) on a 15.6GB table on a lightly loaded db and it run in 163 sec. (Dual opteron 2.6GHz, 6GB RAM, 6 x 74GB 15k disks in RAID10, reiserfs). A little less than 100MB sec. This confirms our findings -

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-10-05 Thread Luke Lonergan
Nope - it would be disk wait. COPY is CPU bound on I/O subsystems faster that 50 MB/s on COPY (in) and about 15 MB/s (out). - Luke -Original Message- From: Michael Stone [mailto:[EMAIL PROTECTED] Sent: Wed Oct 05 09:58:41 2005 To: Martijn van Oosterhout Cc:

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-10-05 Thread Luke Lonergan
Michael, On 10/5/05 8:33 AM, Michael Stone [EMAIL PROTECTED] wrote: real0m8.889s user0m0.877s sys 0m8.010s it's not in disk wait state (in fact the whole read was cached) but it's only getting 1MB/s. You've proven my point completely. This process is bottlenecked in the

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-10-06 Thread Luke Lonergan
Andreas, On 10/6/05 3:56 AM, Zeugswetter Andreas DAZ SD [EMAIL PROTECTED] wrote: pg relys on the OS readahead (== larger block IO) to do efficient IO. Basically the pg scan performance should match a dd if=file of=/dev/null bs=8k, unless CPU bound. Which it is. Postgres will currently do a

Re: [HACKERS] Oracle buys Innobase

2005-10-07 Thread Luke Lonergan
Snicker :-) - Luke ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [HACKERS] gprof SELECT COUNT(*) results

2005-11-26 Thread Luke Lonergan
Title: Re: [HACKERS] gprof SELECT COUNT(*) results Nice job Qingqing and Tom! The improved executor / agg performance will likely help BI / data warehouse customers a lot. Ill get some DBT-3 results to substantiate as soon as we can. - Luke On 11/26/05 12:13 AM, Qingqing Zhou [EMAIL

Re: [HACKERS] ice-broker scan thread

2005-11-29 Thread Luke Lonergan
Jeff, On 11/29/05 9:35 AM, Jeffrey W. Baker [EMAIL PROTECTED] wrote: On Tue, 2005-11-29 at 09:45 -0500, Pollard, Mike wrote: Anyway, what I did was the following. When doing a sequential scan, we were starting at the beginning of the table and scanning forward. If I threw up some

Re: [HACKERS] Consumer-grade vs enterprise-grade disk drives

2005-05-30 Thread Luke Lonergan
Tom, This is a story that is evolving. Anyone else use StorageReview? Great comprehensive drive benchmarks: http://www.storagereview.com/ Check the comparisons between 15K RPM SCSI drives and the 2004 Western Digital 10K RPM SATA (Raptor) drives. The Raptors are an interesting hybrid of

Re: [HACKERS] Consumer-grade vs enterprise-grade disk drives

2005-05-31 Thread Luke Lonergan
Jeff, If we're looking at the same benchmark (File Server DriveMark), the fastest SCSI disk is 65% faster than the fastest SATA disk. The fastest SCSI 10K disk is 25% faster than the SATA. I think it's misleading to compare drives on the basis of one benchmark. One of the things I like a

Re: [HACKERS] Consumer-grade vs enterprise-grade disk drives

2005-06-01 Thread Luke Lonergan
Hannu, RAID10 Throughput report Y-axis is type of test X-axis is number of processes Record size = 8 Kbytes Output is in ops/sec Initial write 1352.90 Rewrite 413.31 RAID5 Throughput report Y-axis is type of test X-axis is number of processes Record size = 8

Re: [HACKERS] NOLOGGING option, or ?

2005-06-01 Thread Luke Lonergan
Bruce, The problem with a new command is that it becomes unclear when you should use COPY and when LOAD DATA, and it confuses users, and has maintenance overhead. If Bizgres wants a new command name, go for it, but it is unlikely that the community release is going to go in that direction,

Re: [HACKERS] NOLOGGING option, or ?

2005-06-01 Thread Luke Lonergan
Tom, ... and instead, define some new behavior that will soon be considered broken legacy code itself? I'll not argue further about whether to have a separate LOAD command. That's not as important as fixing the performance issues in the data load path in PostgreSQL to me. However, I find it

Re: [HACKERS] NOLOGGING option, or ?

2005-06-01 Thread Luke Lonergan
Bruce, Yep, you have to double backslahses coming in as data so we can use backslash for marking null, delimiters, etc. I see no way around that, and no one since Berkeley has come up with one either. If you have an idea, we would _love_ to hear it. Apropos to the previous thread on escape

Re: [HACKERS] NOLOGGING option, or ?

2005-06-01 Thread Luke Lonergan
Yep, we would _love_ those improvements. Coming soon, probably from the guy you've never heard of :-) I am confused why you are confused. :-) Uh, how do you do the escapes if you don't double the escape character on input so you can distinguish a literal escape from one use to mark

Re: [HACKERS] NOLOGGING option, or ?

2005-06-01 Thread Luke Lonergan
Oliver, On 6/1/05 7:13 PM, Oliver Jowett [EMAIL PROTECTED] wrote: Luke Lonergan wrote: I propose an extended syntax to COPY with a change in semantics to remove the default of WITH ESCAPE '\'. Er, doesn't this break existing database dumps? Yes, one of the previously stated reasons

Re: [HACKERS] NOLOGGING option, or ?

2005-06-01 Thread Luke Lonergan
How do you distinguis an escape-delimiter used to escape a delimiter in the data from a literal escape-delimiter in the data being loaded --- it seems impossible to do. The usual approach is fine - a pair of escapes is equivalent to a literal escape. Or are you saying there would be no

Re: [HACKERS] NOLOGGING option, or ?

2005-06-01 Thread Luke Lonergan
Steve, I've been following this thread, and I'm a little confused. Could you possibly clarify what you mean, by providing a couple of lines of input as it would be formatted with escape processing turned off - containing a text field with an embedded newline and tab and a null field. Using

Re: [HACKERS] NOLOGGING option, or ?

2005-06-01 Thread Luke Lonergan
Steve, Oops. Example below should have read differently: Sample with 2 identical rows (with binary representations depicted between ): Blahblah0xaablahblah0x09blahblah0x00blahblah0xaaBlahblah0xaablahbl ah0x09blahblah0x00blahblah0xaa

Re: [HACKERS] NOLOGGING option, or ?

2005-06-02 Thread Luke Lonergan
Andreas, Escape processing would proceed as before, but the semantics would change to allow the use of different characters as the escape character, in addition to the special characters for delimiter and newline. If you mean syntax to specify escape and delimiter (and newline ?), that is

Re: [HACKERS] NOLOGGING option, or ?

2005-06-02 Thread Luke Lonergan
Oliver, Haven't you just replaced one preprocessing step with another, then? Generally not. The most common problem with the current choice of escape character is that there are *lots* of data load scenarios with backslash in the text strings. The extra preprocessing to escape them is

Re: [HACKERS] NOLOGGING option, or ?

2005-06-02 Thread Luke Lonergan
Steve, I can only think of one where it's common. Windows filenames. Nearly all weblog data then. But if you're going to support arbitrary data in a load then whatever escape character you choose will appear sometimes. If we allow an 8-bit character set in the text file, then yes, any

Re: [HACKERS] NOLOGGING option, or ?

2005-06-03 Thread Luke Lonergan
Bruce, Is there a good source of multi-byte copy data test cases? What is currently done to test the trans-coding support? (where client and server encodings are different) I notice that the regression data in the CVS version of postgres does not seem to include cases other than the ASCII data,

Re: [HACKERS] NOLOGGING option, or ?

2005-06-03 Thread Luke Lonergan
Bruce, We have two and three-byte encodings, so 16-bit seems like it wouldn't work. I am not aware of any specs except the C code itself. Ok - no problem. How about test data and cases? I see the SQL encoding examples used in src/test/regress/sql for testing encoding in SQL, but are there

Re: [HACKERS] NOLOGGING option, or ?

2005-06-03 Thread Luke Lonergan
Bruce, I have no idea what you are talking about. Again, give me facts about what we currently don't do and what you want to do. Currently: - No statement of multi-byte control character format - No tests to define or prove works flawlessly or identify when something breaks the current

Re: [HACKERS] Replication on the backend

2005-12-07 Thread Luke Lonergan
Andrew, And if postgres could actually use an infiniband fabric for clustering a single database instance across Opteron servers, that would be very impressive... That's what we do with Bizgres MPP. We've implemented an interconnect to do the data shuffling underneath the

Re: [HACKERS] Warm-cache prefetching

2005-12-08 Thread Luke Lonergan
Qingqing, On 12/8/05 8:07 PM, Qingqing Zhou [EMAIL PROTECTED] wrote: /* prefetch ahead */ __asm__ __volatile__ ( 1: prefetchnta 128(%0)\n

Re: [HACKERS] Warm-cache prefetching

2005-12-09 Thread Luke Lonergan
Bruce, It (the compute intensity optimization) is what we did for copy parsing, and it sped up by a factor of 100+. The rest of the copy path could use some work too. Yge virtual tuples in 8.1 are another example of grouping operations into more compact chunks instead of doing bits at a time.

Re: [HACKERS] Warm-cache prefetching

2005-12-09 Thread Luke Lonergan
Tom, On 12/9/05 2:14 PM, Tom Lane [EMAIL PROTECTED] wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Luke Lonergan wrote: It (the compute intensity optimization) is what we did for copy parsing, and it sped up by a factor of 100+. The changes made to COPY were portable, though. In fact

Re: [HACKERS] Which qsort is used

2005-12-12 Thread Luke Lonergan
Tom, On 12/12/05 2:47 PM, Tom Lane [EMAIL PROTECTED] wrote: As those results suggest, there can be huge differences in sort performance depending on whether the input is random, nearly sorted, nearly reverse sorted, possesses many equal keys, etc. It would be very dangerous to say

Re: [HACKERS] Which qsort is used

2005-12-12 Thread Luke Lonergan
Qingqing, On 12/12/05 5:08 PM, Qingqing Zhou [EMAIL PROTECTED] wrote: This will gives us a 5*3*4*4 = 240 tests ... Looks good - I'm not going to be able to implement this matrix of tests quickly, but each dimension seems right. Might you have time to implement these within the testing

Re: [HACKERS] Cost-based optimizers

2005-12-12 Thread Luke Lonergan
Chris, On 12/12/05 8:44 PM, Christopher Kings-Lynne [EMAIL PROTECTED] wrote: assumption of five. Thus, being able to correct mid-course is an area of enhancement for query optimizers that IBM is pursuing. Hmmm dynamic re-planning! I recently interviewed someone who is in the research group

Re: [HACKERS] Which qsort is used

2005-12-14 Thread Luke Lonergan
Qingqing, On 12/13/05 10:28 AM, Qingqing Zhou [EMAIL PROTECTED] wrote: http://www.cs.toronto.edu/~zhouqq/postgresql/sort/sort.html The source tar ball and linux 2.4G gcc 2.96 test results is on the page. There is a clear loser glibc, not sure qsortB or qsortG which is better. Great stuff -

Re: [HACKERS] Which qsort is used

2005-12-15 Thread Luke Lonergan
Qingqing, On 12/15/05 6:33 PM, Qingqing Zhou [EMAIL PROTECTED] wrote: Thanks for Greg let me take a second look at qsortB.c - there is paste-and-copy error there, so when it perform recursive sort, it calls glibc's qsort ... Really sorry, and feel a little bit gun-shy now ... After I

Re: [HACKERS] Re: Which qsort is used

2005-12-19 Thread Luke Lonergan
Martin, On 12/19/05 3:37 AM, Martijn van Oosterhout kleptog@svana.org wrote: I'm not sure whether we have a conclusion here, but I do have one question: is there a significant difference in the number of times the comparison routines are called? Comparisons in PostgreSQL are fairly expensive

Re: [HACKERS] Libpq COPY optimization

2006-01-08 Thread Luke Lonergan
Title: Re: [HACKERS] Libpq COPY optimization A note on this we see a huge performance benefit from this change on Solaris, so much so that it should be mandatory for that platform. Solaris error handling is deeper, and so the repeated redundant interrupts that this patch avoids causes 60% of

Re: [HACKERS] Blog post on EnterpriseDB...maybe off topic

2006-02-16 Thread Luke Lonergan
Christoper, On 2/15/06 11:14 PM, Christopher Kings-Lynne [EMAIL PROTECTED] wrote: Any comments on this? Is he referring to EnterpriseDB extensions that they don't make public? I've noticed a lot of press lately is mentioning their name next to ingres as an alternative to MySQL, so the MySQL

Re: [HACKERS] In Japan with Josh Berkus

2006-02-16 Thread Luke Lonergan
Title: Re: [HACKERS] In Japan with Josh Berkus Drink Sake and eat some Yakitori for us folks in the west. Maybe shake a robot hand or two while youre at it :-) - Luke On 2/16/06 2:14 PM, Bruce Momjian pgman@candle.pha.pa.us wrote: FYI, Josh Berkus and I are in Japan to give some

Re: [HACKERS] In Japan with Josh Berkus

2006-02-16 Thread Luke Lonergan
Arigato gozaimas! - Luke From: [EMAIL PROTECTED] on behalf of Satoshi Nagayasu Sent: Thu 2/16/2006 10:17 PM To: Luke Lonergan Cc: Bruce Momjian; PostgreSQL-development Subject: Re: [HACKERS] In Japan with Josh Berkus Hi all, Josh's talk is now available

Re: [HACKERS] In Japan with Josh Berkus

2006-02-16 Thread Luke Lonergan
Lonergan Sent: Thu 2/16/2006 10:57 PM To: Satoshi Nagayasu Cc: Bruce Momjian; PostgreSQL-development Subject: Re: [HACKERS] In Japan with Josh Berkus Arigato gozaimas! - Luke From: [EMAIL PROTECTED] on behalf of Satoshi Nagayasu Sent: Thu 2/16/2006 10:17 PM To: Luke

Re: [HACKERS] Blog post on EnterpriseDB...maybe off topic

2006-02-18 Thread Luke Lonergan
Josh, On 2/18/06 7:15 AM, Joshua D. Drake [EMAIL PROTECTED] wrote: EnterpriseDB is a fork of PostgreSQL that contains a reasonable level of pl/SQL (Oracle) compatibility. My understanding (and I could be wrong) is that they support packages, in, inout paramters etc.. in the same syntactical

Re: [HACKERS] Blog post on EnterpriseDB...maybe off topic

2006-02-18 Thread Luke Lonergan
Josh, On 2/18/06 7:38 AM, Luke Lonergan [EMAIL PROTECTED] wrote: I figure they'll have to do quite a lot to make progress in their chosen market, including: - SQL*Net protocol compatibility - Oracle Number datatype support - ROWID unique row identifier - Oracle Redo/Undo log format

Re: [HACKERS] Pgfoundry and gborg: shut one down

2006-02-19 Thread Luke Lonergan
FYI - as a positive enhancement, Greenplum donated a beefy server to host pgFoundry. - Luke On 2/18/06 10:34 AM, Tom Lane [EMAIL PROTECTED] wrote: Thomas Hallgren [EMAIL PROTECTED] writes: Bruce Momjian wrote: Having run had both pgfoundary and gborg for several years, I think we have to

Re: [HACKERS] TOAST compression

2006-02-26 Thread Luke Lonergan
Jim, On 2/26/06 8:00 AM, Jim C. Nasby [EMAIL PROTECTED] wrote: Any idea on how decompression time compares to IO bandwidth? In other words, how long does it take to decompress 1MB vs read that 1MB vs read whatever the uncompressed size is? On DBT-3 data, I've just run some tests meant to

Re: [HACKERS] TOAST compression

2006-02-26 Thread Luke Lonergan
Jim, On 2/26/06 10:37 AM, Jim C. Nasby [EMAIL PROTECTED] wrote: So the cutover point (on your system with very fast IO) is 4:1 compression (is that 20 or 25%?). Actually the size of the gzipp'ed binary file on disk was 65MB, compared to 177.5MB uncompressed, so the compression ratio is 37%

Re: [HACKERS] TOAST compression

2006-02-26 Thread Luke Lonergan
Hannu, On 2/26/06 12:19 PM, Hannu Krosing [EMAIL PROTECTED] wrote: On DBT-3 data, I've just run some tests meant to simulate the speed differences of compression versus native I/O. My thought is that an external use of gzip on a binary dump file should be close to the speed of LZW on

Re: [HACKERS] TOAST compression

2006-02-26 Thread Luke Lonergan
Josh, On 2/26/06 8:04 PM, Josh Berkus josh@agliodbs.com wrote: Check out SET STORAGE. I just altered the MIVP data generator in Bizgres MPP to produce the usual 15 column table but with a 6K row size. You'd only expect a few tens of bytes variance around the 6K, and the data is randomly

Re: [HACKERS] TOAST compression

2006-02-26 Thread Luke Lonergan
While I'm waiting to figure out how to get the size of the toast table, at least I can provide the speed of query with/without assumed compression on the 6K text columns. To insure that we're actually accessing the data in the rows, I do a regexp query on the TOASTed rows: mpptestdb=# select

Re: [HACKERS] Dead Space Map

2006-02-27 Thread Luke Lonergan
Heikki, On 2/27/06 9:53 AM, Heikki Linnakangas [EMAIL PROTECTED] wrote: My current implementation stores a bitmap of 32k bits in the special space of every 32k heap pages. Each bit in the bitmap corresponds one heap page. The bit is set every time a tuple is updated, and it's cleared by

Re: [HACKERS] PostgreSQL Anniversary Summit, Call for

2006-03-03 Thread Luke Lonergan
Peter, I'm asking our performance lead, Ayush Parashar, to develop a talk proposal that will discuss performance of Postgres, including enhancements like the on-disk bitmap index, sort improvements, etc. We'd also like to discuss the business intelligence use-cases and where parallelism is

Re: [HACKERS] Merge algorithms for large numbers of tapes

2006-03-07 Thread Luke Lonergan
Tom, fewer passes when T is large. Do you want to try that? Two passes is the state-of-the-practice on external disk sorts. If we┬╣re looking to replace the tape sort approach, I would hope for a two pass approach, with the merge pass avoided in the case of unidirectional access. - Luke

Re: [HACKERS] Merge algorithms for large numbers of tapes

2006-03-07 Thread Luke Lonergan
to implement the merge. - Luke On 3/7/06 8:03 PM, Tom Lane [EMAIL PROTECTED] wrote: Luke Lonergan [EMAIL PROTECTED] writes: Two passes is the state-of-the-practice on external disk sorts. There is no such thing as a fixed number of passes regardless of available memory and size of the data. regards

Re: [HACKERS] Merge algorithms for large numbers of tapes

2006-03-07 Thread Luke Lonergan
Tom, On 3/7/06 8:03 PM, Tom Lane [EMAIL PROTECTED] wrote: Luke Lonergan [EMAIL PROTECTED] writes: Two passes is the state-of-the-practice on external disk sorts. There is no such thing as a fixed number of passes regardless of available memory and size of the data. While technically

Re: [HACKERS] Merge algorithms for large numbers of tapes

2006-03-08 Thread Luke Lonergan
Tom, On 3/8/06 7:21 AM, Tom Lane [EMAIL PROTECTED] wrote: Simon Riggs [EMAIL PROTECTED] writes: 1. Earlier we had some results that showed that the heapsorts got slower when work_mem was higher and that concerns me most of all right now. Fair enough, but that's completely independent of

Re: [HACKERS] Merge algorithms for large numbers of tapes

2006-03-08 Thread Luke Lonergan
Jim, On 3/8/06 9:49 AM, Jim C. Nasby [EMAIL PROTECTED] wrote: On Wed, Mar 08, 2006 at 11:20:50AM -0500, Tom Lane wrote: Not sure that follows. In particular, the entire point of the recent changes has been to extend the range in which we can use a single merge pass --- that is, write the

Re: [HACKERS] Merge algorithms for large numbers of tapes

2006-03-08 Thread Luke Lonergan
Dann, On 3/8/06 12:39 PM, Dann Corbit [EMAIL PROTECTED] wrote: Here are some suggestions of things that I know work really, really well: Can you point to an example? That might help move the discussion along. The reason to interject about the tape goo in this discussion is that we seem to

Re: [HACKERS] Merge algorithms for large numbers of tapes

2006-03-09 Thread Luke Lonergan
Jim, On 3/9/06 8:35 AM, Jim C. Nasby [EMAIL PROTECTED] wrote: Well, the reality remains though; most folks are unlikely to setup enough dedicated temp areas so that we can do one tape per disk, so it would be really good to have a sort method that didn't rely on that. Agreed - however

Re: [HACKERS] Merge algorithms for large numbers of tapes

2006-03-09 Thread Luke Lonergan
Tom, On 3/9/06 9:44 AM, Tom Lane [EMAIL PROTECTED] wrote: I think this argumentation hinges on some irrational aversion to the word tape. Given adequate work_mem, the CVS-tip behavior is exactly what you propose already (at least for the cases where we don't need random access to the sort

Re: [HACKERS] Merge algorithms for large numbers of tapes

2006-03-09 Thread Luke Lonergan
Stephen, On 3/9/06 3:48 PM, Stephen Frost [EMAIL PROTECTED] wrote: So, if we get a huge performance increase, what's wrong with: if [ sqrt(est(total)) = work_mem ]; then two-pass-sort(); else tape-sort(); fi I have something similar but less complex in mind. One of the observed

Re: [HACKERS] Merge algorithms for large numbers of tapes

2006-03-09 Thread Luke Lonergan
Tom, On 3/9/06 3:59 PM, Tom Lane [EMAIL PROTECTED] wrote: Possibly nothing. However, from an algorithmic point of view the CVS-tip code *is* two-pass-sort, given adequate work_mem and no requirement for random access. Further, the available profile data doesn't show any indication that the

Re: [HACKERS] Merge algorithms for large numbers of tapes

2006-03-09 Thread Luke Lonergan
Dann, On 3/9/06 3:56 PM, Dann Corbit [EMAIL PROTECTED] wrote: Two pass does not require sqrt(total) memory. This figure is clearly wrong. Clearly you haven't read the paper I posted previously in this thread from 1986 written by Jim Grey at Tandem. - Luke ---(end

Re: [HACKERS] random observations while testing with a 1,8B row

2006-03-10 Thread Luke Lonergan
Stefan, On 3/10/06 9:40 AM, Stefan Kaltenbrunner [EMAIL PROTECTED] wrote: I will summarize some of the just in case somebody is interested: I am! - table used has 5 integer columns non-indexed during the loads - hardware is a Dual Opteron 280 with 4 [EMAIL PROTECTED],4GHz and 16GB RAM,

Re: [HACKERS] random observations while testing with a 1,8B row

2006-03-10 Thread Luke Lonergan
Stefan, On 3/10/06 11:48 AM, Stefan Kaltenbrunner [EMAIL PROTECTED] wrote: 2 HBAs in the server, 2x2 possible paths to each LUN. 6 disks for the WAL and 12 disks for the data So - you have 18 disks worth of potential bandwidth, not factoring loss due to RAID. That's roughly 18 * 60 = 1,080

Re: [HACKERS] random observations while testing with a 1,8B row

2006-03-10 Thread Luke Lonergan
Stefan, On 3/10/06 12:23 PM, Stefan Kaltenbrunner [EMAIL PROTECTED] wrote: wrong(or rather extremely optimistic) the array itself only has two (redundant) FC-loops(@2GB )to the attached expansion chassis. The array has 2 active/active controllers (with a failover penalty) with two host

Re: [HACKERS] random observations while testing with a 1,8B row

2006-03-11 Thread Luke Lonergan
Stefan, On 3/11/06 12:21 AM, Stefan Kaltenbrunner [EMAIL PROTECTED] wrote: So - you're getting 20MB/s on loading from a potential of 200MB/s? no - I can write 110MB/s on thw WAL LUN and 110MB/s on the other LUN concurrently. The numbers you published earlier show you are getting a maximum

Re: [HACKERS] PostgreSQL Anniversary Proposals -- Important

2006-03-17 Thread Luke Lonergan
Tom, On 3/17/06 7:03 PM, Tom Lane [EMAIL PROTECTED] wrote: Speaking of which, I've been trying to think of a talk proposal and am not coming up with anything that seems terribly sexy. I've talked a couple times about the planner and am afraid people would be bored by that again. I'd be

Re: [HACKERS] Automatically setting work_mem

2006-03-17 Thread Luke Lonergan
Csaba, On 3/17/06 7:07 AM, Csaba Nagy [EMAIL PROTECTED] wrote: It worths a look at how apache Derby does with query planning, where a planned query is actually a compiled Java class, i.e. the executable byte code which will run to fetch the results, created and compiled by the planner...

Re: [HACKERS] Automatically setting work_mem

2006-03-18 Thread Luke Lonergan
Tom, On 3/17/06 9:59 PM, Tom Lane [EMAIL PROTECTED] wrote: This would buy what exactly? I guess you didn't read the other 80% of the post. In short, faster performance through more aggressive runtime compilation. A JIT for the database kernel. It's not like I'm on shaky ground here - other

Re: [HACKERS] Automatically setting work_mem

2006-03-18 Thread Luke Lonergan
Tom, On 3/17/06 12:18 PM, Tom Lane [EMAIL PROTECTED] wrote: One user with ability to enter arbitrary SQL commands can *always* blow your resource planning away. Blaming such things on work_mem is seriously misguided. Agreed - that's why we need to split this discussion into the two

Re: [HACKERS] Automatically setting work_mem

2006-03-21 Thread Luke Lonergan
Tom, On 3/21/06 2:47 PM, Tom Lane [EMAIL PROTECTED] wrote: I'm fairly unconvinced about Simon's underlying premise --- that we can't make good use of work_mem in sorting after the run building phase --- anyway. If we cut back our memory usage then we'll be forcing a significantly

Re: [HACKERS] Automatically setting work_mem

2006-03-22 Thread Luke Lonergan
Tom, On 3/21/06 3:06 PM, Tom Lane [EMAIL PROTECTED] wrote: The real problem we are facing with a whole lot of our optimization issues (not only sorting) is that it's not all that trivial to get credible experimental results that we can expect will hold up across a range of usage scenarios.

Re: [HACKERS] 8.2 planning features

2006-03-25 Thread Luke Lonergan
Satoshi, On 3/21/06 3:59 PM, satoshi nagayasu [EMAIL PROTECTED] wrote: However, we also need to know why business people want to know about the future plan. For the business people, the roadmap is used to know the software is fit to their (growing) business, not only now but in the future.

Re: [HACKERS] I have changed employers

2006-04-04 Thread Luke Lonergan
Bruce, On 4/4/06 5:06 PM, Bruce Momjian pgman@candle.pha.pa.us wrote: I am also looking forward to working with EnterpriseDB on new projects and opportunities. Congrats! - Luke ---(end of broadcast)--- TIP 1: if posting/reading through

Re: [HACKERS] Support Parallel Query Execution in Executor

2006-04-08 Thread Luke Lonergan
Greg, On 4/8/06 5:43 PM, Gregory Maxwell [EMAIL PROTECTED] wrote: For example, one case made in this thread involved bursty performance with seqscans presumably because the I/O was stalling while processing was being performed. In general this can be avoided without parallel execution

Re: [HACKERS] Support Parallel Query Execution in Executor

2006-04-08 Thread Luke Lonergan
Myron, First, this sounds really good! On 4/8/06 9:54 PM, Myron Scott [EMAIL PROTECTED] wrote: I added a little hack to the buffer code to force pages read into the buffer to stay at the back of the free buffer list until the master thread has had a chance to use it. This is the part I'm

Re: [HACKERS] Support Parallel Query Execution in Executor

2006-04-09 Thread Luke Lonergan
Myron, On 4/9/06 1:18 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I am working with Solaris on SPARC almost exclusively and I believe Josh said that Sun was the one who found the bursty behavior with scans. Has it been confirmed that this is the case on all/most platforms? It was our

Re: [HACKERS] Support Parallel Query Execution in Executor

2006-04-09 Thread Luke Lonergan
Gregory, On 4/9/06 1:36 PM, Gregory Maxwell [EMAIL PROTECTED] wrote: It might also be interesting for someone with the right testing rig on linux to try the adaptive readahead patch to see if that improves PG's ability to keep the disk busy. the adaptive readahead patch? Did I miss one? We

Re: [HACKERS] Support Parallel Query Execution in Executor

2006-04-09 Thread Luke Lonergan
Tom, On 4/9/06 9:27 AM, Tom Lane [EMAIL PROTECTED] wrote: 2. There are some low-level assumptions that no one reads in pages of a relation without having some kind of lock on the relation (consider eg the case where the relation is being dropped). A bgwriter-like process wouldn't be able to

Re: [HACKERS] Support Parallel Query Execution in Executor

2006-04-10 Thread Luke Lonergan
Hannu, On 4/10/06 2:23 AM, Hannu Krosing [EMAIL PROTECTED] wrote: The cost of fetching a page from the OS is not really much of an overhead, Have you tested this ? I have - the overhead of fetching a page from Linux I/O cache to buffer cache is about an additional 20% over fetching it

Re: [HACKERS] Support Parallel Query Execution in Executor

2006-04-10 Thread Luke Lonergan
Gregory, On 4/9/06 2:04 PM, Gregory Maxwell [EMAIL PROTECTED] wrote: It increases Linux's maximum readahead from 128K to 1meg .. and it should be smart enough that you could crank it up further without too much risk of hurting performance elsewhere. Interesting - we are now routinely using

  1   2   3   >