Bramandia Ramadhana wrote:
Currently, I am investigating the nested loop join algorithm in
nodeNestloop.c. After reading the code, my understanding is that it performs
simple nested loop join (not block nested loop join). Is this true?
Yep.
Does postgresql support block nested loop join?
Heikki Linnakangas [EMAIL PROTECTED] writes:
Does postgresql support block nested loop join?
Nope.
We do support Hash Join though so I think the only difference is that we can't
use the hash join for cartesian joins.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
head compiles ok now, panic's over ;)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
I'm kind of curious where the value of 4 for random_page_cost came from.
IIRC, Tom, you mentioned it came from tests you ran -- were those raw i/o
tests or Postgres cost estimates compared to execution times?
Te reason I'm wondering about this is it seems out of line with raw i/o
numbers.
Gregory Stark [EMAIL PROTECTED] writes:
I'm kind of curious where the value of 4 for random_page_cost came from.
IIRC, Tom, you mentioned it came from tests you ran -- were those raw i/o
tests or Postgres cost estimates compared to execution times?
It was based on actual query execution
On Fri, Oct 10, 2008 at 01:09:48PM +0900, KaiGai Kohei wrote:
4. Metadata-level access controls. None of the proposals so far seem
to provide a complete set of access controls for the system details --
schemas, databases, c. Such controls are often requested, so I
wonder about that.
We
On Fri, Oct 10, 2008 at 01:44:49PM +0900, KaiGai Kohei wrote:
Andrew Sullivan wrote:
I want to focus on this description, because you appear to be limiting
the problem scope tremendously here. We've moved from general
security policy for database system to security policy for database
system
1) Reduced error checking.
2) The '-' is not the only character that people have used. ClearCase uses
'.' and ':' as punctuation.
3) People already have the option of translating the UUID from their
application to a standard format.
4) As you find below, and is probably possible to
Gregory Stark schrieb:
Te reason I'm wondering about this is it seems out of line with raw i/o
numbers. Typical values for consumer drives are about a sustained throughput
of 60MB/s ( Ie .2ms per 8k) and seek latency of 4ms. That gives a ratio of 20.
Server-class drives have even a ratio since
Bramandia Ramadhana [EMAIL PROTECTED] writes:
Thanks for the clarifications.
Just for curiosity, is there any reason of not having block nested-loop join
implementation? Is it rarely useful?
Oh, actually it occurs to me that we do implement something analogous to a
degenerate block nested
On Fri, Oct 10, 2008 at 7:28 AM, Mark Mielke [EMAIL PROTECTED] wrote:
Robert Haas wrote:
While we could perhaps accept only those variant formats which we
specifically know someone to be using, it seems likely that people
will keep moving those pesky dashes around, and we'll likely end up
The error on createdb happened again this morning. However, this time an
abandoned directory was not created. The full error message was:
$ createdb -E SQL_ASCII -U flyminebuild -h brian.flymine.org -T
production-flyminebuild production-flyminebuild:uniprot
createdb: database creation
Michael Renner [EMAIL PROTECTED] writes:
I think your numbers are a bit off:
For Consumer drives (7.200 RPM SATA 3.5), seek times are much worse, in the
area of 8-9ms (see [1]), but sustained sequential read numbers are noticeable
higher, around 80-90MB/sec.
I took the seek latency from
Gregory Stark [EMAIL PROTECTED] writes:
For Server Drives 3-4ms are more realistic ([2], [3]) for average seeks and
the 110-170MB/sec are highly exaggerated.
In that case both of those numbers come straight from Seagate's data sheet for
their top-of-the-line data centre drives:
On Fri, Oct 10, 2008 at 09:41:39AM -0400, Tom Lane wrote:
So I was looking for other omissions in utility.c, and I noticed that
check_xact_readonly() doesn't reject CLUSTER, REINDEX, or VACUUM.
Now the notion of read only that we're trying to enforce is pretty
weak (I think it's effectively no
Kenneth Marshall [EMAIL PROTECTED] writes:
On Fri, Oct 10, 2008 at 09:41:39AM -0400, Tom Lane wrote:
But I can't see that CLUSTER is a read-only operation even under the
weakest definitions, and I'm not seeing the rationale for REINDEX or
VACUUM here either.
CLUSTER, REINDEX, and VACUUM are
Jim Cox [EMAIL PROTECTED] wrote:
if present an INFO message is generated which displays
the schema.tblname just before actual clustering is kicked off (see
example
below).
postgres=# CLUSTER VERBOSE ;
INFO: clustering public.my_b
INFO: clustering public.my_c
INFO: clustering
Kevin Grittner wrote:
Jim Cox [EMAIL PROTECTED] wrote:
if present an INFO message is generated which displays
the schema.tblname just before actual clustering is kicked off (see
example
below).
postgres=# CLUSTER VERBOSE ;
INFO: clustering public.my_b
INFO: clustering public.my_c
INFO:
So I was looking for other omissions in utility.c, and I noticed that
check_xact_readonly() doesn't reject CLUSTER, REINDEX, or VACUUM.
Now the notion of read only that we're trying to enforce is pretty
weak (I think it's effectively no writes to non-temp tables).
But I can't see that CLUSTER is a
Thanks for the clarifications.
Just for curiosity, is there any reason of not having block nested-loop join
implementation? Is it rarely useful?
As far as I am aware of, in the case of cross product of two tables, block
nested-loop join is the most efficient algorithm.
Regards,
Bramandia R.
Robert Haas wrote:
1) Reduced error checking.
2) The '-' is not the only character that people have used. ClearCase uses
'.' and ':' as punctuation.
3) People already have the option of translating the UUID from their
application to a standard format.
4) As you find below, and is
Gregory Stark [EMAIL PROTECTED] writes:
So the use case of a real block nested loop would be doing a cartesian join of
two large tables where neither fits in RAM. That does seem like it might be
kind of narrow given how large the output would be.
Yeah. If you have a hashable join condition
Grzegorz Jaskiewicz wrote:
head compiles ok now, panic's over ;)
Tom fixed it yesterday :-)
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-hackers mailing list
On Thu, Oct 9, 2008 at 9:37 AM, Jim Cox [EMAIL PROTECTED] wrote:
Is anyone working the CLUSTER: Add VERBOSE option... TODO item listed
on the PostgreSQL Wiki? If not, would it be wise for me to use
VERBOSE handling in an existing command (e.g. VACUUM)
as a guide while adding VERBOSE to
Anyways - I only somewhat disagree. I remember the original discussions, and
I remember agreeing with the points to keep PostgreSQL UUID support thin and
rigid. It's valuable for it to be built-in to the database. It's not
necessarily valuable for PostgreSQL to support every UUID version or
Mark Mielke [EMAIL PROTECTED] writes:
Anyways - I only somewhat disagree. I remember the original discussions,
and I remember agreeing with the points to keep PostgreSQL UUID support
thin and rigid. It's valuable for it to be built-in to the database.
It's not necessarily valuable for
3) People already have the option of translating the UUID from their
application to a standard format.
Regexp, the swiss-army knife of data manipulation. ;)
While possible, it really is not that easy and efficient. At least we should
accept dashless UUIDs, so instead of tediously
Tom Lane [EMAIL PROTECTED] writes:
Gregory Stark [EMAIL PROTECTED] writes:
So the use case of a real block nested loop would be doing a cartesian join
of
two large tables where neither fits in RAM. That does seem like it might be
kind of narrow given how large the output would be.
Yeah.
Currently, we advance latestCompletedXid during
ProcArrayEndTransaction() for both commits and aborts.
If a transaction aborts, its effects are invisible to us just the same
as if the transaction is still running.
ISTM that we need not move latestCompletedXid as a result of an abort.
Only a
Thinking about how to reduce the effects of certain race conditions
makes me think about whether it is possible to make a function called
LWLockAcquireWithPriority().
We already allow queue jumping when lock mode != LW_EXCLUSIVE, so
queue jumping based upon an assigned priority rather than
Tom Lane [EMAIL PROTECTED] wrote:
The attached patch cures the leak for me
I see that the patch was applied. A CVS checkout from this morning
fixes the leak for me, too; the vmstat output stayed rock steady
during the run.
Thanks!
-Kevin
--
Sent via pgsql-hackers mailing list
Simon Riggs [EMAIL PROTECTED] writes:
This will prevent commits being stalled when we occasionally switch clog
and multixact pages, plus it also stops commits from being stalled when
there are heavy writers in progress.
Exactly how would a priority mechanism prevent stalling? If the lock is
On Fri, Oct 10, 2008 at 10:23 AM, Heikki Linnakangas
[EMAIL PROTECTED] wrote:
Kevin Grittner wrote:
Jim Cox [EMAIL PROTECTED] wrote:
if present an INFO message is generated which displays
the schema.tblname just before actual clustering is kicked off (see
example
below).
postgres=#
Gregory Stark schrieb:
But with your numbers things look even weirder. With a 90MB/s sequential speed
(91us) and 9ms seek latency that would be a random_page_cost of nearly 100!
Looks good :). If you actually want to base something on Real World
numbers I'd suggest that we collect them
On Fri, 10 Oct 2008, Gregory Stark wrote:
They don't quote sustained bandwidth for consumer drives but 50-60MB/s are the
numbers I remembered -- admittedly from more than a couple years ago. I didn't
realize 7200 RPM drives had reached such speeds yet.
The cheap ($42!) 7200RPM SATA disks I
Hi all,
Here is the latest patch and the regression tests for the temp tables
and 2PC issue.
Is there a way to stop and restart postmaster between 2 tests?
Thanks for your feedback,
Emmanuel
--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development Consulting
--
Web:
On 2008-10-10, at 16:01, Tom Lane wrote:
Well, this discussion started with the conventional wisdom about be
conservative in what you send and liberal in what you accept. I'd
still resist emitting any UUID format other than the RFC-approved one,
but I don't see anything very wrong in being
Greg Smith [EMAIL PROTECTED] wrote:
I don't think random_page_cost actually corresponds with any real
number
anymore. I just treat it as an uncalibrated knob you can turn and
benchmark the results at.
Same here. We have always found best performance in our production
environments with
2008/10/10 Tom Lane [EMAIL PROTECTED]:
Mark Mielke [EMAIL PROTECTED] writes:
Anyways - I only somewhat disagree. I remember the original discussions,
and I remember agreeing with the points to keep PostgreSQL UUID support
thin and rigid. It's valuable for it to be built-in to the database.
that only depends on definition of 'common variant'. Will it be just code
that will accept letters and digits, and trying to make that into UUID ?
You are attacking a straw man. No one is proposing that.
I think those who designed their code to produce or accept non standard
UUID, should
I think it will be as expensive to app to convert UUID to standard
format, as it would be too postgrsql.
But if psql does it - everyone would expect it to do it right. You
can't possibly detect all forms of screwed up design, and expect
application to pick it up.
All I say, is I think it
Is it problem do for non standard UUID formats pgfoundry project?
I'm not volunteering set up a pgfoundry project to maintain something
that can be accomplished with a patch that adds 19 lines of new code
(and removes 9). This functionality is useful in core because it will
Just Work. If you
On Fri, Oct 10, 2008 at 3:48 PM, Grzegorz Jaskiewicz
[EMAIL PROTECTED] wrote:
I think it will be as expensive to app to convert UUID to standard format,
as it would be too postgrsql.
But if psql does it - everyone would expect it to do it right. You can't
possibly detect all forms of screwed
2008/10/10 Robert Haas [EMAIL PROTECTED]:
Is it problem do for non standard UUID formats pgfoundry project?
I'm not volunteering set up a pgfoundry project to maintain something
that can be accomplished with a patch that adds 19 lines of new code
(and removes 9). This functionality is useful
Hi Dave,
Perhaps a 'Working with Eclipse' page under
http://wiki.postgresql.org/wiki/Development_information
I have added a link at the bottom of the page.
All the info on how to use Eclipse with Postgres can be found at
http://wiki.postgresql.org/wiki/Working_with_Eclipse
All suggestions
I dislike all own creatures - because nobody will understand so do
some wrong thing - using non standard formats is bad thing. So it's is
necessary, then who need it then he found it on pgfoundry. But why
smudge core?
I'm opposed to smudging core, but I'm in favor of this patch. :-)
Of
On Fri, Oct 10, 2008 at 9:14 PM, Emmanuel Cecchet [EMAIL PROTECTED] wrote:
Hi Dave,
Perhaps a 'Working with Eclipse' page under
http://wiki.postgresql.org/wiki/Development_information
I have added a link at the bottom of the page.
All the info on how to use Eclipse with Postgres can be
Simon Riggs [EMAIL PROTECTED] writes:
ISTM that we need not move latestCompletedXid as a result of an abort.
I see no value in this; it just makes things less consistent without
buying any noticeable performance gain.
regards, tom lane
--
Sent via pgsql-hackers mailing
Greg Smith [EMAIL PROTECTED] writes:
... So the true random/sequential ratio
reaches crazy numbers.
Bear in mind that seq_page_cost and random_page_cost are intended to
represent the time to read *and process* a page, so there's some CPU
component involved there, and this limits the ratio
Tom Lane wrote:
In particular, if the OS lays out successive file pages in a way that
provides zero latency between logically adjacent blocks, I'd bet a good
bit that a Postgres seqscan would miss the read timing every time, and
degrade to handling about one block per disk rotation.
Unless the
I don't think random_page_cost actually corresponds with any real number
anymore. I just treat it as an uncalibrated knob you can turn and
benchmark the results at.
And, frankly, not a useful knob. You get much more useful results out
of effective_cache_size and cpu_* costs than you get
Josh Berkus [EMAIL PROTECTED] writes:
I don't think random_page_cost actually corresponds with any real number
anymore. I just treat it as an uncalibrated knob you can turn and benchmark
the results at.
And, frankly, not a useful knob. You get much more useful results out of
Folks,
Magnus and I decided to take on the annual /contrib cleanup for the code
sprint here at pgWest. One of the areas we realized needs cleanup is
the use of schema with the modules -- Magnus, Bruce and I all think that
contrib modules really need to create and use their own private
Josh Berkus wrote:
3) what work was actually done on load_module() by Tom Dunstan, which
might make this unnecessary?
http://archives.postgresql.org/message-id/[EMAIL PROTECTED]
This link appears on Todo:
Improve the module installation experience (/contrib, etc)
* modules
On Fri, 10 Oct 2008, Tom Lane wrote:
In particular, if the OS lays out successive file pages in a way that
provides zero latency between logically adjacent blocks, I'd bet a good
bit that a Postgres seqscan would miss the read timing every time, and
degrade to handling about one block per disk
I noticed while working on general fixes for the certificate handling
that if we have a connection being attempted with sslmode=prefer (which
happens to be our default), we will loose error messages.
Basically, if we fail the SSL connection, we will throw away the error
message and try a
Alvaro Herrera wrote:
Josh Berkus wrote:
3) what work was actually done on load_module() by Tom Dunstan, which
might make this unnecessary?
http://archives.postgresql.org/message-id/[EMAIL PROTECTED]
This link appears on Todo:
Improve the module installation experience (/contrib, etc)
The new \ef psql command creates nicely usable CREATE OR REPLACE
FUNCTION ... text based on the function I tell it to edit, but the
text it creates *doesn't* include a final semicolon, so when I exit my
editor-of-choice after messing with my function, it doesn't run the
code I've given it until I
Magnus Hagander [EMAIL PROTECTED] writes:
I noticed while working on general fixes for the certificate handling
that if we have a connection being attempted with sslmode=prefer (which
happens to be our default), we will loose error messages.
Yeah, this came up awhile ago. I don't see any easy
Folks,
It's that time again! Purging antiquated contrib modules.
chkpass: this module is incomplete and does not implement all functions
it describes. It's not really even useful as an Example since it uses
crypt() and not any modern encryption. And Darcy hasn't touched it in 6
years.
Tom Lane wrote:
Magnus Hagander [EMAIL PROTECTED] writes:
I noticed while working on general fixes for the certificate handling
that if we have a connection being attempted with sslmode=prefer (which
happens to be our default), we will loose error messages.
Yeah, this came up awhile ago. I
Alvaro Herrera [EMAIL PROTECTED] writes:
It seems that the real way forward is to improve on that patch.
Yeah. If the schema-per-module answer were really a good answer,
we'd have done it before now. But you need more infrastructure
than just a schema to get good things to happen. Aside from
Magnus Hagander [EMAIL PROTECTED] writes:
Tom Lane wrote:
Maybe the answer is to not throw away the first error message? But
presenting both messages could be confusing too.
Do we have the infrastructure to report more than one error? I thought
we didn't...
I was thinking of merging the
Josh Berkus wrote:
But Tom hasn't done anything since April? That's what I'm asking.
What's the surprise? I gathered that Tom is itinerant. If he's not
here and we want to job to be done, somebody else must do it.
--
Alvaro Herrera
Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
Yeah, that seems like the best answer.
Seems like this patch fixes it.
Um, not for tables that don't have toast tables ...
Right, this seems better.
Note that it needs to open the toast table and grab AccessShare to get
the toast
In any case your experience doesn't match mine. On a machine with a sizable
raid controller setting random_page_cost higher does generate, as expected,
plans with more bitmap heap scans which are in fact faster.
We're running postgres backed by a NetApp 3020 via fiber and have had a lot
of
Joshua Tolley [EMAIL PROTECTED] writes:
The new \ef psql command creates nicely usable CREATE OR REPLACE
FUNCTION ... text based on the function I tell it to edit, but the
text it creates *doesn't* include a final semicolon, so when I exit my
editor-of-choice after messing with my function, it
Josh Berkus [EMAIL PROTECTED] writes:
Any objections to dropping both of these?
You should ask on -general, not here, if you are trying to find out
whether the modules have any users.
I tend to agree that chkpass is of doubtful value, but I'm not so sure
about intagg. As you said yourself, we
intagg: the aggregation function has been obsolete since 7.4 because
standard array functionality supports the same. intagg has a nice
equivalent for UNROLL, but it only works for arrays of INT, and only
one-dimensional arrays. Has not been updated since 2001.
I think this one can be
After reading Josh Berkus's email suggesting that the intagg module be
dropped, I was wondering what would be required to create a array
enumerator (variously called unnest, unroll, array_enum, and, as
contemplated by the TODO list, array_to_set). Pavel Stehule's
generate_subscripts function
On Fri, Oct 10, 2008 at 7:10 PM, Tom Lane [EMAIL PROTECTED] wrote:
Joshua Tolley [EMAIL PROTECTED] writes:
The new \ef psql command creates nicely usable CREATE OR REPLACE
FUNCTION ... text based on the function I tell it to edit, but the
text it creates *doesn't* include a final semicolon, so
On Oct 10, 2008, at 20:27, Joshua Tolley wrote:
Now, if you want to fix psql so that even with a semicolon there it
will redisplay the command buffer and wait for a return, then I'd
agree
that that's an improvement. I couldn't figure out how to get
readline
to cooperate with that ... but
On Fri, 10 Oct 2008 16:28:29 -0700
Josh Berkus [EMAIL PROTECTED] wrote:
Folks,
It's that time again! Purging antiquated contrib modules.
chkpass: this module is incomplete and does not implement all
functions it describes. It's not really even useful as an Example
since it uses crypt()
Josh Berkus wrote:
intagg: ... Has not been updated since 2001.
Really? Just a couple years ago (2005) bugs we reported were
still getting fixed in it:
http://archives.postgresql.org/pgsql-bugs/2005-03/msg00202.php
http://archives.postgresql.org/pgsql-bugs/2005-04/msg00165.php
Here's one
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Fri, Oct 10, 2008 at 09:09:51PM -0700, Ron Mayer wrote:
Josh Berkus wrote:
intagg: ... Has not been updated since 2001.
[...]
I also like intagg, because it's kinda like a hello world for
writing one kind of C extensions. I'm not saying it
75 matches
Mail list logo