Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Sat, Oct 01, 2005 at 02:19:06AM -0400, Tom Lane wrote:
>> Ah, here it is:
>> http://archives.postgresql.org/pgsql-hackers/2004-03/msg00502.php
> Would an ALTER INDEX SET STATISTICS form be possible?
It's not so much the table/index misnomer that's bot
On Sat, Oct 01, 2005 at 02:19:06AM -0400, Tom Lane wrote:
> I wrote:
> > I seem to recall bringing up the question of whether
> > we could find a less implementation-specific way of commanding this
> > behavior, but I can't find it in the archives right now.
>
> Ah, here it is:
> http://archives.p
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
> [EMAIL PROTECTED] On Behalf Of Tom Lane
> Sent: Friday, September 30, 2005 11:02 PM
> To: Jeffrey W. Baker
> Cc: Luke Lonergan; Josh Berkus; Ron Peacetree; pgsql-
> [EMAIL PROTECTED]; pgsql-performance@postgresql.org
>
I wrote:
> I seem to recall bringing up the question of whether
> we could find a less implementation-specific way of commanding this
> behavior, but I can't find it in the archives right now.
Ah, here it is:
http://archives.postgresql.org/pgsql-hackers/2004-03/msg00502.php
No responses :-(
"Jeffrey W. Baker" <[EMAIL PROTECTED]> writes:
> I think the largest speedup will be to dump the multiphase merge and
> merge all tapes in one pass, no matter how large M. Currently M is
> capped at 6, so a sort of 60GB with 1GB sort memory needs 13 passes over
> the tape. It could be done in a s
Judy definitely rates a WOW!!
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
> [EMAIL PROTECTED] On Behalf Of Gregory Maxwell
> Sent: Friday, September 30, 2005 7:07 PM
> To: Ron Peacetree
> Cc: Jeffrey W. Baker; pgsql-hackers@postgresql.org; pgsql-
> [EMAIL PROTECTE
On Sat, Oct 01, 2005 at 12:53:03AM -0400, Tom Lane wrote:
> The code does in fact honor per-column statistics targets attached to
> expression indexes, viz
>
> alter table myfuncindex alter column pg_expression_1 set statistics 100;
Aha -- that's the piece I didn't know about. I was wondering wh
On Fri, Sep 30, 2005 at 11:59:26PM -0400, Bruce Momjian wrote:
> This is expected. The main TODO items is:
>
> * Allow accurate statistics to be collected on indexes with more than
> one column or expression indexes, perhaps using per-index statistics
>
> Basically, we don't
Michael Fuhr <[EMAIL PROTECTED]> writes:
> I've noticed that row count estimates for expression indexes appear
> to rely on default_statistics_target rather than on a column's
> actual statistics target. That is, if I use ALTER TABLE SET
> STATISTICS to increase a column's statistics target and th
This is expected. The main TODO items is:
* Allow accurate statistics to be collected on indexes with more than
one column or expression indexes, perhaps using per-index statistics
Basically, we don't have multi-column or expression statistics. ANALYZE
just analyzes c
I've noticed that row count estimates for expression indexes appear
to rely on default_statistics_target rather than on a column's
actual statistics target. That is, if I use ALTER TABLE SET
STATISTICS to increase a column's statistics target and then run
ANALYZE, then estimates for non-expression
On 9/28/05, Ron Peacetree <[EMAIL PROTECTED]> wrote:
> 2= We use my method to sort two different tables. We now have these
> very efficient representations of a specific ordering on these tables. A
> join operation can now be done using these Btrees rather than the
> original data tables that inv
On 9/30/05, Ron Peacetree <[EMAIL PROTECTED]> wrote:
> 4= I'm sure we are paying all sorts of nasty overhead for essentially
> emulating the pg "filesystem" inside another filesystem. That means
> ~2x as much overhead to access a particular piece of data.
>
> The simplest solution is for us to imp
On Fri, Sep 30, 2005 at 05:54:49PM -0500, Jim C. Nasby wrote:
> On Mon, Sep 26, 2005 at 10:57:54AM -0700, Ron Mayer wrote:
> > David Fetter wrote:
> > >...log file formats in 8.0
> > >
> > >* CSV
> > >* YAML
> > >* XML
> > >* Piped logs, as Apache can do
> > >* DB handle. I know this one will
On 9/30/05, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> On Fri, Sep 30, 2005 at 10:53:22AM -0700, Joshua D. Drake wrote:
> > >
> > >So you might notice little performance hit bringing back a million rows,
> > >and most of these type of single OUT params functions only return one
> > >row/value anyway
Jim C. Nasby wrote:
> On Fri, Sep 30, 2005 at 10:53:22AM -0700, Joshua D. Drake wrote:
> > >
> > >So you might notice little performance hit bringing back a million rows,
> > >and most of these type of single OUT params functions only return one
> > >row/value anyway.
> > >There would be zero per
On Fri, 30 Sep 2005, Jim C. Nasby wrote:
ERROR: cache lookup failed for relation 1906465919
It is on an exact table name. When we retry the describe on a failure,
sometimes it works and sometimes it fails again. When it fails again
the relation number is different. Also, \d schema.table alwa
On Fri, Sep 30, 2005 at 06:58:05PM -0400, Bruce Momjian wrote:
> We don't have the ability to have to functions that take the same
> parameters and return different results because there is no facility to
> decide which function to call based on what return value is expected,
> because a simple que
On Thu, Sep 29, 2005 at 11:05:47AM -0400, Jonah H. Harris wrote:
> Has there been any movement on this? If not, I finally have some time to
> look at it.
Well, here's the original thread:
http://archives.postgresql.org/pgsql-hackers/2005-08/msg00637.php
I think the problem is that the testing isn
I have perused the tuple sort stuff.
The good:
The documentation of the sort algorithm from Knuth's TAOCP was
beautifully done. Everyone who writes an algorithm should credit the
original source like this, and also where it deviates. That was done
very nicely.
The bad:
With random access, tape
On Thu, Sep 29, 2005 at 09:28:38PM +0800, Christopher Kings-Lynne wrote:
>
> >CREATE SEQUENCE ai_id;
> >CREATE TABLE badusers (
> > id int DEFAULT nextval('ai_id') NOT NULL,
> > UserName varchar(30),
> > Date datetime DEFAULT '-00-00 00:00:00' NOT NULL,
> > Reason varchar(200),
> > Admin
On Fri, Sep 30, 2005 at 10:53:22AM -0700, Joshua D. Drake wrote:
> >
> >So you might notice little performance hit bringing back a million rows,
> >and most of these type of single OUT params functions only return one
> >row/value anyway.
> >There would be zero perceivable difference in performan
25MBps should not be a CPU bound limit for IO, nor should it be
an OS limit. It should be something ~100x (Single channel RAM)
to ~200x (dual channel RAM) that.
For an IO rate of 25MBps to be pegging the CPU at 100%, the CPU
is suffering some combination of
A= lot's of cache misses ("cache thrash
On Wed, Sep 28, 2005 at 07:25:46PM +0100, Simon Riggs wrote:
> Include the Discriminator as a column in A and it will be inherited by
> all A1, A2, A3.
> e.g. concrete_class char(1) not null
> This will add 1 byte per row in your superclass... and requires no
I thought char was actually stored
On Wed, Sep 28, 2005 at 10:24:18PM +0200, Martijn van Oosterhout wrote:
> On Wed, Sep 28, 2005 at 11:00:46PM +0400, Ilia Kantor wrote:
> > Your suggestion is essentially the same as mine..
> > There exists tableoid, pretty much suited to tell between tables in the case
> > of inheritance.. I can't
On Tue, Sep 27, 2005 at 07:00:14PM +0530, Gnanavel S wrote:
> On 9/27/05, Jonah H. Harris <[EMAIL PROTECTED]> wrote:
> >
> > Were you looking for a call graph?
>
>
> Yes. I want to know the list and sequence of files involved during a call.
Total non-coder question, but is there an open-source u
On Wed, Sep 28, 2005 at 10:22:51AM -0400, Tom Lane wrote:
> "Qingqing Zhou" <[EMAIL PROTECTED]> writes:
> > "Tom Lane" <[EMAIL PROTECTED]> wrote in message
> > There is a possible sequence like this:
>
> > ALIGNOF_LONG4
> > ALIGNOF_DOUBLE 8
> > MAXIMUM_ALIGNOF 8
>
> > vs.
>
> >
On Mon, Sep 26, 2005 at 08:54:49PM -0700, Josh Berkus wrote:
> Tom,
>
> >Or, as you say, we could take the viewpoint that there are commercial
> >companies willing to take on the burden of supporting back releases, and
> >the development community ought not spend its limited resources on doing
> >
I have seen similar performance as Josh and my reasoning is as follows:
* WAL is the biggest bottleneck with its default size of 16MB. Many
people hate to recompile the code to change its default, and
increasing checkpoint segments help but still there is lot of overhead
in the rotation of W
Back in 2003 Bruce Momjian proposed adding a flag (-I) to psql to force
it into "interactive" mode. (See
http://archives.postgresql.org/pgsql-hackers/2003-11/msg00013.php for
the thread.) The proposal was rejected because there was no proven need
for it at that time. I'd like to raise this propos
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.
As I said, the obvious candidates are inefficient physical layout
and/or flawed IO code.
Until the basic IO issues are addressed, we cou
Bulk loading speed is irrelevant here - that is dominated by parsing,
which
we have covered copiously (har har) previously and have sped up by 500%,
which still makes Postgres < 1/2 the loading speed of MySQL.
Let's ask MySQL 4.0
LOAD DATA INFILE blah
0 errors, 666 warnings
SHOW
On Mon, Sep 26, 2005 at 09:07:45PM -0700, Joshua D. Drake wrote:
>
> >A nice pg_upgrade utility would make a big difference. Clearly an
> >in-place upgrade is possible, but maintaining is hard. There are two
> >broad ways of running a pg_upgrade project - one that is entirely
> >independent of the
Jim C. Nasby wrote:
> On Wed, Sep 28, 2005 at 06:07:02PM -0400, Neil Conway wrote:
> > On Wed, 2005-28-09 at 18:35 -0300, Marc G. Fournier wrote:
> > > The problem isn't whether or not they should be changed, the problem is
> > > that they were changed *during* beta AND *against* the direction tha
On Fri, 2005-30-09 at 17:47 -0500, Jim C. Nasby wrote:
> What's wrong with adding pg_cancel_backend(...) RETURNS int as an alias
> for the one that returns boolean, and document that it's deprecated and
> will be removed in the future.
You can't overload functions based on their return type alone.
On Mon, Sep 26, 2005 at 10:57:54AM -0700, Ron Mayer wrote:
> David Fetter wrote:
> >...log file formats in 8.0
> >
> >* CSV
> >* YAML
> >* XML
> >* Piped logs, as Apache can do
> >* DB handle. I know this one will be controversial.
> >[...]
> >1. Am I the only one who would wants an option fo
On Wed, Sep 28, 2005 at 06:07:02PM -0400, Neil Conway wrote:
> On Wed, 2005-28-09 at 18:35 -0300, Marc G. Fournier wrote:
> > The problem isn't whether or not they should be changed, the problem is
> > that they were changed *during* beta AND *against* the direction that
> > discussion on these c
Alvaro Herrera wrote:
On Fri, Sep 30, 2005 at 10:08:11AM +0100, Dave Page wrote:
Looks like there more unprotable code in the recent changes to pgbench
:-(
Here, the culprits are tfind() and tsearch(). These apparently aren't
"portable enough", but they seem to exist on all other p
On Mon, Sep 26, 2005 at 11:46:47AM -0400, Tom Lane wrote:
> Jochem van Dieten <[EMAIL PROTECTED]> writes:
> > On 9/26/05, Dennis Bjorklund wrote:
> >> One reason is because it's what the standard demand.
>
> > Could you cite that? The only thing I can find in the SQL standard is
> > that the hour
On Mon, Sep 26, 2005 at 06:23:06PM +0200, Andreas Pflug wrote:
> Tom Lane wrote:
> >Dennis Bjorklund <[EMAIL PROTECTED]> writes:
> >
> >>Do the sql standard say anything on the matter?
> >
> >
> >It doesn't seem very helpful. AFAICS, we should interpret storing
> >'23:59:59.99' into a TIME(0) fiel
On Thu, Sep 29, 2005 at 12:50:13AM +0300, Hannu Krosing wrote:
> On T, 2005-09-27 at 17:57 -0500, Jim C. Nasby wrote:
> > On Tue, Sep 27, 2005 at 02:47:46PM -0400, Jan Wieck wrote:
> > > On 9/24/2005 8:17 PM, Jim C. Nasby wrote:
> > >
> > > >Would it be difficult to vacuum as part of a dump? The r
http://archives.postgresql.org/pgsql-bugs/2005-02/msg00135.php
I think it may have been a bit early to disable pthread python support
(http://archives.postgresql.org/pgsql-hackers/2005-09/msg01136.php), as
Python was working fine on buildfarm member platypus. Maybe it's only an
issue with 4.x mach
On Wed, Sep 28, 2005 at 11:35:31AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Sorry, remembered it wrong. It's 'Did not find any relation named',
> > which appears to be in bin/psql/describe.c. It does occur when trying to
> > do a \d on a specific table.
>
> Hmm, no i
Luke Lonergan wrote:
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,
I see the following routines that seem to be related to sorting.
If I were to examine these routines to consider ways to improve it, what
routines should I key in on? I am guessing that tuplesort.c is the hub
of activity for database sorting.
Directory of U:\postgresql-snapshot\src\backend\acces
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
On Fri, Sep 30, 2005 at 10:08:11AM +0100, Dave Page wrote:
> Looks like there more unprotable code in the recent changes to pgbench
> :-(
Here, the culprits are tfind() and tsearch(). These apparently aren't
"portable enough", but they seem to exist on all other platforms. Maybe
we could come up
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
Ron,
> 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.
Oh, yeah. Well, that's separate from sort. See multiple posts on this
list from the GreenPlum team, the COPY patch for 8.1
On Friday 30 September 2005 11:49, Martijn van Oosterhout wrote:
> On Fri, Sep 30, 2005 at 10:20:34AM -0500, Tony Caduto wrote:
> > Tom,
> > I hardly think the overhead would be significant on modern processors, I
> > don't think the majority of users are running on Pentium 90s.( I am
> > assuming
So you might notice little performance hit bringing back a million rows,
and most of these type of single OUT params functions only return one
row/value anyway.
There would be zero perceivable difference in performance regardless of
the extra overhead for a single value/row.
Sounds like we n
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
> [EMAIL PROTECTED] On Behalf Of PFC
> Sent: Thursday, September 29, 2005 9:10 AM
> To: [EMAIL PROTECTED]
> Cc: Pg Hackers; pgsql-performance@postgresql.org
> Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
>
>
Martijn van Oosterhout wrote:
On Fri, Sep 30, 2005 at 10:20:34AM -0500, Tony Caduto wrote:
Tom,
I hardly think the overhead would be significant on modern processors, I
don't think the majority of users are running on Pentium 90s.( I am
assuming you mean a performance overhead)
Um,
Ron,
Hmmm.
60GB/5400secs= 11MBps. That's ssllooww. So the first
problem is evidently our physical layout and/or HD IO layer
sucks.
Actually, it's much worse than that, because the sort is only dealing
with one column. As I said, monitoring the iostat our top speed was
2.2mb/s.
--Josh
-
Tom Lane wrote:
However, why two zombies? That would mean that the grandchild process
started, which should mean that the pipe was already created ...
To clarify, I talk about the tcpview window and connections, and thus
zombi-connections. They both belong to the same pid and seems to point
On Fri, Sep 30, 2005 at 10:20:34AM -0500, Tony Caduto wrote:
> Tom,
> I hardly think the overhead would be significant on modern processors, I
> don't think the majority of users are running on Pentium 90s.( I am
> assuming you mean a performance overhead)
Um, please read the documention. Return
Tom Lane wrote:
Mike Rylander <[EMAIL PROTECTED]> writes:
Using that logic, a functions with one OUT param would be the same as
a function returning a rowtype with only one column,
But it's not (and no, I don't want to make it so, because the overhead
for the useless record result wou
> > If it's two zombies per minute, then I bet it's the stat
> collector and
> > stat bufferer. They are restarted by the postmaster if not
> found to
> > be running.
>
> That would make some sense, because the stat processes need
> to set up new sockets (for the pipe between them). The
>
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> If it's two zombies per minute, then I bet it's the stat collector and
> stat bufferer. They are restarted by the postmaster if not found to be
> running.
That would make some sense, because the stat processes need to set up new
sockets (for the pipe b
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> (Maybe we should audit it for security problems, just in case the
> distributors choose to include it in packages. The temp file usage for
> $TMPFILE looks "predictable," thus maybe it _is_ insecure. Apparently
> it can be made to clobber any existing
On Thu, Sep 29, 2005 at 03:24:15PM +0200, Fabien COELHO wrote:
>
> Dear Tom,
>
> >While testing the recent pgxs patch, I noticed that you can build and
> >install contrib with PGXS:
> >...
> >It seems that it ought to work to run installcheck too:
> > gmake USE_PGXS=1 installcheck
> >but this
What is the relationship between database support for clustering and grid
computing and support for distributed databases?
Two-phase COMMIT is comming in 8.1. What effect will this have in promoting
FOSS grid support or distribution solutions for Postgresql?
---(end of
Just to add a little anarchy in your nice debate...
Who really needs all the results of a sort on your terabyte table ?
I guess not many people do a SELECT from such a table and want all the
results. So, this leaves :
- Really wanting all the results, to fetch using
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 instance of PostgreSQL (but only one running at
any given moment)?
Best,
Thanks for your reply Luke.
Bizgres looks like a very promissing project. I'll be sure to follow
it.
Thanks to everyone for their comments. I'm starting to understand the
truth behind the hype and where these performance gains and hits stem
from.
-Dan
---(end of broad
>From: Josh Berkus
>Sent: Sep 29, 2005 12:54 PM
>Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
>
>The biggest single area where I see PostgreSQL external
>sort sucking is on index creation on large tables. For
>example, for free version of TPCH, it takes only 1.5 hours to
>load a 60GB
>From: Zeugswetter Andreas DAZ SD <[EMAIL PROTECTED]>
>Sent: Sep 29, 2005 9:28 AM
>Subject: RE: [HACKERS] [PERFORM] A Better External Sort?
>
>>In my original example, a sequential scan of the 1TB of 2KB
>>or 4KB records, => 250M or 500M records of data, being sorted
>>on a binary value key will
>From: Pailloncy Jean-Gerard <[EMAIL PROTECTED]>
>Sent: Sep 29, 2005 7:11 AM
>Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
>>>Jeff Baker:
>>>Your main example seems to focus on a large table where a key
>>>column has constrained values. This case is interesting in
>>>proportion to th
Dear Tom,
While testing the recent pgxs patch, I noticed that you can build and
install contrib with PGXS:
...
It seems that it ought to work to run installcheck too:
gmake USE_PGXS=1 installcheck
but this does not quite work because the pg_regress script isn't
included in the installat
On Fri, Sep 30, 2005 at 08:29:07AM +0200, Thomas Hallgren wrote:
> Magnus Hagander wrote:
>
> >Right. Please try turning it off and see if the problem goes away.
> >
> >
> No, wait! It does *not* go away. Do I need to do anything more than
> setting this in my postgresql.conf file:
>
> autovacu
Looks like there more unprotable code in the recent changes to pgbench
:-(
Regards, Dave.
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> Sent: 30 September 2005 02:17
> To: [EMAIL PROTECTED];
> [EMAIL PROTECTED]
> Subject: PGBuildfarm member snake Branch H
71 matches
Mail list logo
