Re: [HACKERS] Hash Join cost estimates

2013-04-12 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > I'm certainly curious about those, but I'm also very interested in the > > possibility of making NTUP_PER_BUCKET much smaller, or perhaps variable > > depending on the work_mem setting. > > Not sure about that. That would make th

Re: [HACKERS] Hash Join cost estimates

2013-04-05 Thread Stephen Frost
* Matthias (nitrogen...@gmail.com) wrote: > >In this example, hashing the large table is actually 2 seconds *faster* > >than hashing the small table (again, all on my laptop). > > Are you running the laptop on battery? When I've benchmarked pgsql > last time I used my laptop as well and it only oc

Re: [HACKERS] Hash Join cost estimates

2013-04-05 Thread Matthias
In this example, hashing the large table is actually 2 seconds *faster* than hashing the small table (again, all on my laptop). Are you running the laptop on battery? When I've benchmarked pgsql last time I used my laptop as well and it only occured to me after a lot of trying that laptops (

Re: [HACKERS] Hash Join cost estimates

2013-04-04 Thread k...@rice.edu
On Thu, Apr 04, 2013 at 04:16:12PM -0400, Stephen Frost wrote: > * Stephen Frost (sfr...@snowman.net) wrote: > > It does look like reducing bucket depth, as I outlined before through > > the use of a 2-level hashing system, might help speed up > > ExecScanHashBucket, as it would hopefully have very

Re: [HACKERS] Hash Join cost estimates

2013-04-04 Thread Tom Lane
Stephen Frost writes: > Looking with opannotate, there's two main hotspots in > ExecScanHashBucket: > 12846 17.4001 :hashTuple = > hashtable->buckets[hjstate->hj_CurBucketNo]; > and > 22100 29.9348 :hashTuple = hashTuple->next; Those are, of course, pretty trivial statements;

Re: [HACKERS] Hash Join cost estimates

2013-04-04 Thread Stephen Frost
* Stephen Frost (sfr...@snowman.net) wrote: > It does look like reducing bucket depth, as I outlined before through > the use of a 2-level hashing system, might help speed up > ExecScanHashBucket, as it would hopefully have very few (eg: 1-2) > entries to consider instead of more. Along those same

Re: [HACKERS] Hash Join cost estimates

2013-04-04 Thread Stephen Frost
* Stephen Frost (sfr...@snowman.net) wrote: > 8504125241524.3624 72.3115mcount.c:60 > libc-2.15.so __mcount_internal > 28370280785 8.1274 80.4389_mcount.S:33 > libc-2.15.so mcount [...] And as a side-note, I

Re: [HACKERS] Hash Join cost estimates

2013-04-04 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > perf or oprofile reveal anything? Here's what we get from oprofile (perhaps not too surprising): Hash the small table / scan the big table: samples cum. samples %cum. % linenr info image name symbol name 167374

Re: [HACKERS] Hash Join cost estimates

2013-04-04 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > > What I'm trying to get at in this overall email is: why in the world is > > it so expensive to do hash lookups? > > perf or oprofile reveal anything? Working on a test case actually- I've got one now: http://snowman.net/~sfrost/test_case2.sql In this ex

Re: [HACKERS] Hash Join cost estimates

2013-04-04 Thread Tom Lane
Stephen Frost writes: > I've been fiddling with this on the very much larger overall database > where this test case came from and have found that hashing the large > table can actually be *faster* and appears to cause a more consistent > and constant amount of disk i/o (which is good). Interesti

Re: [HACKERS] Hash Join cost estimates

2013-04-04 Thread Stephen Frost
Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > So that's at least going in the right direction. I agree that this is going in the right direction; it certainly would make the plan that I *expect* to be chosen more likely, however.. I've been fiddling with this on the very much larger overall

Re: [HACKERS] Hash Join cost estimates

2013-04-01 Thread Tom Lane
I wrote: > Perhaps what we should do is charge the hash_qual_cost only for some > small multiple of the number of tuples that we expect will *pass* the > hash quals, which is a number we have to compute anyway. The multiple > would represent the rate of hash-code collisions we expect. I tried the

Re: [HACKERS] Hash Join cost estimates

2013-04-01 Thread Robert Haas
On Mon, Apr 1, 2013 at 2:35 AM, Jeff Davis wrote: > On Sun, 2013-03-31 at 15:45 -0400, Tom Lane wrote: >> Really, when we're traipsing down a bucket >> list, skipping over bucket entries with the wrong hash code is just >> about free, or at least it's a whole lot cheaper than applying ExecQual. >>

Re: [HACKERS] Hash Join cost estimates

2013-03-31 Thread Jeff Davis
On Sun, 2013-03-31 at 15:45 -0400, Tom Lane wrote: > Really, when we're traipsing down a bucket > list, skipping over bucket entries with the wrong hash code is just > about free, or at least it's a whole lot cheaper than applying ExecQual. > > Perhaps what we should do is charge the hash_qual_cos

Re: [HACKERS] Hash Join cost estimates

2013-03-31 Thread Tom Lane
Stephen Frost writes: > * Jeff Davis (pg...@j-davis.com) wrote: >> In Stephen's case the table was only 41KB, so something still seems off. >> Maybe we should model the likelihood of a collision based on the >> cardinalities (assuming a reasonably good hash function)? > It's not really 'hash coll

Re: [HACKERS] Hash Join cost estimates

2013-03-30 Thread Stephen Frost
* Jeff Davis (pg...@j-davis.com) wrote: > In Stephen's case the table was only 41KB, so something still seems off. > Maybe we should model the likelihood of a collision based on the > cardinalities (assuming a reasonably good hash function)? It's not really 'hash collisions' that we're trying to b

Re: [HACKERS] Hash Join cost estimates

2013-03-30 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: > I think the point is that there may *not* be few hash collisions ... Right, but that's actually all entirely based on concerns over there being duplicates (hence why we consider the MCVs and ndistinct), which makes *some* sense given that we currently have

Re: [HACKERS] Hash Join cost estimates

2013-03-30 Thread Stephen Frost
Jeff, * Jeff Davis (pg...@j-davis.com) wrote: > On Thu, 2013-03-28 at 19:56 -0400, Stephen Frost wrote: > > 41K hashed, seqscan 4M: 115030.10 + 1229.46 = 116259.56 > > 4M hashed, seqscan 41K: 1229.46 + 211156.20 = 212385.66 > > I think those are backwards -- typo? Yes, sorry, those are backw

Re: [HACKERS] Hash Join cost estimates

2013-03-29 Thread Jeff Davis
On Fri, 2013-03-29 at 16:37 -0400, Tom Lane wrote: > Jeff Davis writes: > > Yes, I have run into this issue (or something very similar). I don't > > understand why the bucketsize even matters much -- assuming few hash > > collisions, we are not actually evaluating the quals any more times than >

Re: [HACKERS] Hash Join cost estimates

2013-03-29 Thread Tom Lane
Jeff Davis writes: > Yes, I have run into this issue (or something very similar). I don't > understand why the bucketsize even matters much -- assuming few hash > collisions, we are not actually evaluating the quals any more times than > necessary. So why all of the hashjoin-specific logic in dete

Re: [HACKERS] Hash Join cost estimates

2013-03-29 Thread Jeff Davis
On Thu, 2013-03-28 at 19:56 -0400, Stephen Frost wrote: > 41K hashed, seqscan 4M: 115030.10 + 1229.46 = 116259.56 > 4M hashed, seqscan 41K: 1229.46 + 211156.20 = 212385.66 I think those are backwards -- typo? > In the end, I think the problem here is that we are charging far too > much fo

[HACKERS] Hash Join cost estimates

2013-03-28 Thread Stephen Frost
All, Marty's issue w/ NestLoop reminded me that I'd put together a test case which illustrates a HashJoin doing the wrong thing. The test case is here: http://snowman.net/~sfrost/test_case.sql Simply load that up and then check out this plan: explain select * from small_table join