On Tue, 2005-05-10 at 16:44 +0300, Hannu Krosing wrote:
On T, 2005-05-10 at 16:31 +0300, Hannu Krosing wrote:
On E, 2005-05-09 at 23:30 +0100, Simon Riggs wrote:
There are 2 possibly expensive steps:
1. the conversion to AND'ed list of simple clauses (unknown
complexity)
2
On Tue, 2005-05-10 at 15:01 -0400, Tom Lane wrote:
Jim C. Nasby [EMAIL PROTECTED] writes:
On Tue, May 10, 2005 at 12:16:17AM +0100, Simon Riggs wrote:
On Mon, 2005-05-09 at 18:53 -0400, Tom Lane wrote:
I disagree. The code is there, it could use work, and what you are
basically proposing
On Tue, 2005-05-10 at 18:22 -0400, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
The cause of the performance problem has been attributed to it being a
64-bit rather than 32-bit calculation. That is certainly part of it, but
I have seen evidence that there is an Intel processor
records?
perhaps the CRC-32 routines could be written in in-line assembler
If you can do this, step right up. :-)
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
On Thu, 2005-05-12 at 13:48 +0100, Mark Cave-Ayland wrote:
From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED]
perhaps the CRC-32 routines could be written in in-line assembler
If you can do this, step right up. :-)
Best Regards, Simon Riggs
Surely there's
the scan
result Slot during ExecRestrPos.
Yeh, no problem.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 8: explain analyze is your friend
for the
difference in elapsed time, not the OS.
The performance gain is disturbing. I think its supposed to be the other
way around isn't it? Like having INT64 is supposed to be good...
Perhaps the BIOS on your systems don't correctly support 64-bit, so
mimicking it costs more.
Best Regards, Simon
reference to this.
Thanks,
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Is there any particular reason that RelOptInfo doesn't have a debugging
output function?
Seems too obvious an omission not have have some reasoning
there...perhaps just too complex and rapidly changing?
Best Regards, Simon Riggs
---(end of broadcast
On Wed, 2005-05-18 at 17:48 -0400, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
Is there any particular reason that RelOptInfo doesn't have a debugging
output function?
The planner structs involving RelOptInfo are self-referential, so if you
just do the obvious thing you
constraints, since the former are fairly useless for
partition elimination. So I can't see a reason to care whether they are
there or not, if the user knows better.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 9: the planner will ignore your
.
Does that do anything useful though?
I thought we don't reallocate until VACUUM time, whereupon we identify
any empty slots and reuse them. Who cares whether we deallocate earlier?
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 5: Have
On Fri, 2005-05-20 at 11:51 -0400, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
Doing anything to restrict dropping of inherited constraints seems like
wasted effort and potentially annoying anyhow.
Uh, why? Arguably the constraints are as much part of the parent table
. The file is said to contain
information that would help you remove backup-states.
It seems straightforward to test for the existence of that file in the
build process? Or perhaps add a special test state --enable-flextest to
perform the test during the build.
Best Regards, Simon Riggs
, then on to GNU. It'll take a while before it comes back round to
us though.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
, Simon Riggs
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
and seems fairly performant.
Judging by the number of PANICs reported, the data structure would be
mostly empty anyhow.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
If a table is created as WITHOUT OIDS, is it still possible to take
advantage of the physical tlist optimization?
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 8: explain analyze is your friend
gain this performance benefit for those willing to accept the
restrictions imposed?
Your comments are sought and are most welcome.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 8: explain analyze is your friend
that switches files
can do the work, rather than bgwriter [ or ?].
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
.
Is this a change that would be backpatched as you suggested previously?
It seems a rather large patch to change three things at once. Can the
backpatch wait until 8.1 has gone through beta to allow the changes to
be proven?
Best Regards, Simon Riggs
---(end of broadcast
deal more benefit to be had from many other areas, IMHO.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
?
The list of ideas suggested but then rejected is growing longer and we
rely on people's memory to say what was rejected previously.
The question is where do you put this idea if you reject it? :-)
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 3
On Tue, 2005-05-31 at 22:47 -0400, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
Recent test results have shown a substantial performance improvement
(+25%) if WAL logging is disabled for large COPY statements.
How much of that is left after we fix the 64-bit-CRC issue?
Well, I
On Tue, 2005-05-31 at 22:31 -0400, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
If a table is created as WITHOUT OIDS, is it still possible to take
advantage of the physical tlist optimization?
Um ... it depends. There are cases where you have to have an OID,
and cases where you
On Tue, 2005-05-31 at 22:36 -0400, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
Hmmm. I seem to recall asking myself why xl_prev existed if it wasn't
used, but passed that by. Damn.
I couldn't believe it'd been overlooked this long, either. It's the
sort of thing that you assume
LIBRARY 'library' would be the new recommended usage.
LOAD DATA... would be the new command... with most other options hanging
off of that. There's no problem with that, since that is then the same
as Oracle syntax for the load utility.
Best Regards, Simon Riggs
---(end
suggested
for handling truncated tables. Ignore the first LOAD DATA started
message until recovery completes, then truncate table if the LOAD DATA
complete message was not logged in wal.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 4
for him...
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
be
a foot-gun of large caliber indeed :-(
Oh no, not the foot gun again. I surrender.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do
On Wed, 2005-06-01 at 11:55 -0400, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
4. Optionally, we set a flag on the table showing the whole table is
frozen. Anybody writing to this table subsequently will spoil this flag.
If the flag is set, all forms of VACUUM will return success
On Wed, 2005-06-01 at 14:24 -0400, Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
On Wed, Jun 01, 2005 at 06:55:46PM +0100, Simon Riggs wrote:
We're holding the table lock and will continue to do so until end of
transaction. No transaction with an earlier id will ever see the data
, but given time schedules, I
think that we win with the above, so forget the rest.
The main COPY/LOAD DATA discussion is on another thread of this from
Alon, who has some interesting ideas and some really cool performance
results to share.
Best Regards, Simon Riggs
. :)
The project needs free-thinkers, so don't feel that you have to stick to
the TODO. Just make sure you have a very clear benefit case.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
of them, even if it did provide the
command name.]
But things will be clearer when we see the patch.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister
lock
upgrades in a relatively unpredictable manner is not good.
For CREATE TABLE AS SELECT, I think we could do this Bruce's way and
win, since we would take the same grade of lock in both cases.
Best Regards, Simon Riggs
---(end of broadcast
.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
of replies if we asked them 10 questions instead of 1. That
would allow us to cross-correlate the answers to gain an even better
picture of what is happening and what is wanted.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 5: Have you checked our
be doing this after the beta freeze is announced. If you wanted to
do this before then, I wouldn't stop you.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org
by other patches.
We're safe to remove it from the patch queue.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
to have such a far
reaching effect.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
On Thu, 2005-06-16 at 01:10 -0400, Greg Stark wrote:
Simon Riggs [EMAIL PROTECTED] writes:
If you really do need that many, you can go to the trouble of grouping
them in two levels of nesting, so you have a root table, multiple month
tables and then each month table with multiple day
sized.
Using more than that could lead to some fairly strange designs.
Anyway, lets wait and see.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
On Thu, 2005-06-16 at 00:55 -0400, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
Looks bad... but how does it look for 1000 inherited relations? My
feeling is that we should not be optimizing the case above 1000
relations. That many partitions is very unwieldy.
Well, it's not so
(subselect)) you'll see that the subselect values would be
treated as constants that could result in a bitmap index lookup.
Transforming IN () into straight joins would not take the same approach
when more than one join (i.e. 3 or more tables) was requested.
Best Regards, Simon Riggs
On Wed, 2005-11-30 at 02:10 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
[...context omitted...]
We would need to flush all the blocks in the table out of cache at
commit time, for that table only.
This seems striking close to the Old Concept of temp tables, which
we got
assume that in all we do.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
executing while the index is being built?
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 6: explain analyze is your friend
It appears that a bunch of spammers have invaded -hackers, claiming they
can avoid the heat death of the universe in exchange for 2 bytes.
Or something like that. :-)
Even my Krull-powered Spam filters can't cope.
Best Regards, Simon Riggs
---(end of broadcast
Regards, Simon Riggs
---(end of broadcast)---
TIP 6: explain analyze is your friend
and a bunch of other well known addresses sent it.
Could
be forged From fields though ;-)
Sorry Marc, just a bit of good natured humour on a Friday afternoon.
Your vigilance protects us well; thank you.
Best Regards, Simon Riggs
---(end of broadcast
On Fri, 2005-12-02 at 17:45 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
CREATE INDEX uses SnapshotAny, so the scan that feeds the build could
easily include rows added after the CREATE INDEX started. When the scan
was exhausted we could mark that last TID and return
of partition key, so its hard to spot
when two union sets have the same key to allow pushdown.
I hope to work on some of that in the 8.2 timebox, but I suspect this is
a multi-year mission.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 6
not? If it
does occur then we know it can be destroy SMP performance and on some
CPUs can lead to context switching.
Comments?
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
On Sat, 2005-12-03 at 11:49 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
These changes have almost no negative impact on run time performance and
can be implemented with minimum change. We can discuss whether the false
sharing phenomena actually occurs, but the bottom line
On Sat, 2005-12-03 at 08:47 -0500, Robert Treat wrote:
On Friday 02 December 2005 09:53, Simon Riggs wrote:
On Fri, 2005-12-02 at 02:14 -0500, Tom Lane wrote:
Greg Stark [EMAIL PROTECTED] writes:
It was a *major* new feature that many people were waiting for when
Oracle finally
On Sat, 2005-12-03 at 17:16 -0300, Alvaro Herrera wrote:
Simon Riggs wrote:
Understood. At 7.3, REINDEXing is essential, since rows never got
deleted and space was not reused. That is not the case now, hence a
REINDEX is less often required.
But it's still required or at least
times,
but its a step in the right direction.) I'm not sure I like that
particular idea much, but I'm attempting to address Neil's original
point, with which I agree.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 2: Don't 'kill -9
On Sun, 2005-12-04 at 12:49 -0300, Alvaro Herrera wrote:
Simon Riggs wrote:
ISTM we could do some of that with another GUC, lets call it
prepare_once = on. The system default is to have a prepared statement
bound to a plan on its first parameter bind. If we set this to off
On Sun, 2005-12-04 at 13:47 -0500, Pollard, Mike wrote:
Simon Riggs wrote
The system default is to have a prepared statement
bound to a plan on its first parameter bind.
We call it deferred optimization.
Do you really stop at the first parameter?
The first bind of parameters
the appearance of control is illusory.]
My solution is to replan the queries each time, rather than just once on
first parameter bind. By some mechanism; the GUC is just one of those.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 2: Don't
On Tue, 2005-12-06 at 16:12 +0200, Hannu Krosing wrote:
Ühel kenal päeval, L, 2005-12-03 kell 09:21, kirjutas Simon Riggs:
First off, I think we need to do some more work on partitioning so that
some knowledge about the union set is understood by the optimizer. At
the moment
before discussions begin.
Best Regards, Simon Riggs
---(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
the MaintIntentLock and other
backends return to normal operation.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
accessed one.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 6: explain analyze is your friend
On Wed, 2005-12-07 at 22:53 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
My view would be that the LockMgrLock is not relevant for all workloads,
but I want even more to be able to discuss whether it is, or is not, on
an accepted basis before discussions begin.
Certainly
On Wed, 2005-12-07 at 21:24 +, Simon Riggs wrote:
Following patch implements record of whether a constraint is inherited
or not, and prevents dropping of inherited constraints.
Patch posted to -patches list.
What it doesn't do:
It doesn't yet prevent dropping the parent constraint, which
On Thu, 2005-12-08 at 10:26 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
The imbalance across partitions would be a major issue because of the
difficulty of selecting a well-distributed partitioning key. If you use
the LOCKTAG, then operations on the heaviest hit tables
these things when you have
to, picking as light traffic period as you can. Most people would accept
some pretty hefty restrictions in order to get it to work.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 1: if posting/reading through Usenet
On Thu, 2005-12-08 at 11:10 +, Simon Riggs wrote:
On Wed, 2005-12-07 at 21:24 +, Simon Riggs wrote:
Following patch implements record of whether a constraint is inherited
or not, and prevents dropping of inherited constraints.
Patch posted to -patches list.
What it doesn't do
On Thu, 2005-12-08 at 09:44 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
The output you gave wasn't anything I recognize in the code. Assuming
its not already there, please can you share code you are using to find
the evidence, even if its just privately in some form?
See
guide and
tried to apply that wisdom, so it seems a good idea to go back to the
source.
I think many of these techniques are generic across architectures, so
there is much to be done in this area, IMHO. Though we must respect
portability and confirm any tuning through testing.
Best Regards, Simon
will find some
benefit, somewhere, so I think its worth the look, at the same time
checking that the compiler guys haven't solved it for us first.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner
, we need a separate
command to do that, ISTM.
ALTER TABLE ... READONLY
could set minXid = FrozenTransactionId, indicating no further VACUUMs
required, ever. We can then disallow INSERT/UPDATE/DELETE against the
table in the permissions layer.
Best Regards, Simon Riggs
required.
There are many reasons to need to supply the password as part of a
command, rather than an interactive input.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 6: explain analyze is your friend
On Sat, 2005-12-10 at 11:15 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
On Fri, 2005-12-09 at 19:41 +0100, Peter Eisentraut wrote:
Maybe we should provide a backslash command in psql
That is a good option, but not the only option required.
There are many reasons
On Sat, 2005-12-10 at 21:07 -0500, Tom Lane wrote:
In any case the design idea here
seems to be we don't care how long REINDEX takes as long as it's not
blocking anyone.
All sounds great so far. I'd like this as an option for CREATE INDEX
also.
Best Regards, Simon Riggs
at the instant of the deadlock. Knowing which tuple locks
were held would give at least some additional information.
Can we LOG that info? If the deadlock detector knows about it, surely we
can dump it out usefully.
Best Regards, Simon Riggs
---(end of broadcast
required.
Does this seem like a reasonable agenda
Yup - tis good.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
transformations. I'd be interested if you
get the chance to look at that.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
into one.
If we do this, we would could possibly avoid the need to full REINDEX
entirely.
If this method checks out we could do one of:
- make VACUUM do this always
- add an option for REINDEX: CLEAN/COMPRESS/VACUUM etc to do this upon
command only
Best Regards, Simon Riggs
On Wed, 2005-12-21 at 19:07 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
While we scan, if we found two adjacent pages, both of which have less
than (say) 40% rows, we could re-join or unsplit those pages together.
Curiously enough, this has been thought of before
any issues you see.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
. It
would be easy enough to extend this so that it also works for INSERT,
UPDATE and DELETE.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On Thu, 2005-12-22 at 12:12 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
Currently, CTAS optimization requires a heap_sync during ExecEndPlan. It
would be easy enough to extend this so that it also works for INSERT,
UPDATE and DELETE.
If you tried to do it that way you'd
On Thu, 2005-12-22 at 10:40 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
Sorry, I missed that.
And you evidently still didn't understand it. Locking both pages does
not fix the problem, because it doesn't guarantee that there's not a
concurrent indexscan in flight from
for performance reasons, however
the choice is yours.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
On Thu, 2005-12-22 at 17:36 -0500, Stephen Frost wrote:
* Simon Riggs ([EMAIL PROTECTED]) wrote:
On Thu, 2005-12-22 at 21:18 +0100, Martijn van Oosterhout wrote:
Considering WAL bypass is code for breaks PITR
No it isn't. All of the WAL bypass logic does *not* operate when PITR
The TODO list has an item add missing date selectivity, which is a
hang-over from 1999 or before.
Is this still an issue? Can somebody describe exactly what it is?
I can't find the exact discussion of what the problem was/is, so its
better to ask directly...
Best Regards, Simon Riggs
restart the load.
But that doesn't mean you want to lose the data that is already there if
you crash.
That's exactly what COPY LOCK has been designed to do. It is analagous
to Oracle's sql*loader direct path nologging mode.
Best Regards, Simon Riggs
---(end of broadcast
, it is a
transactional log archival. So, other parts of your thinking are
slightly off - but not by much. The only way to do a partial recovery is
to follow the PITR notes.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 9: In versions below
more closely before this became a
TODO item, IMHO.
If someone has a 100 GB table, they would not appreciate the table being
truncated if a transaction to load 1 GB of data aborts, forcing recovery
of the 100 GB table.
Best Regards, Simon Riggs
---(end of broadcast
On Tue, 2005-12-27 at 23:00 -0500, Bruce Momjian wrote:
Simon Riggs wrote:
The TODO list has an item add missing date selectivity, which is a
hang-over from 1999 or before.
Is this still an issue? Can somebody describe exactly what it is?
I can't find the exact discussion of what
of
failure, though with higher performance hit than current PITR. I'll look
into that some more - but no promises.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 6: explain analyze is your friend
, as a database designer.
Best Regards, Simon Riggs
--
Martijn van Oosterhout wrote:
-- Start of PGP signed section.
On Mon, Dec 26, 2005 at 12:03:27PM +, Simon Riggs wrote:
I would not be against such a table-level
other restrictions also. Anyone?
Best Regards, Simon Riggs
---(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
On Thu, 2005-12-29 at 11:37 -0500, Bruce Momjian wrote:
Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
My view would be that this thread has been complex because everybody has
expressed a somewhat different requirement, which could be broken down
as:
1. The need for a multi
to perform PITR, you must use WAL Archive Mode.
If you backed-up in WAL Archive Mode, you can perform an Archive
Recovery.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
, and, assuming we want EXCLUSIVE
for more than just COPY, we need to add ALTER TABLE EXCLUSIVE.
Would you mind stating again what you mean, just so I can understand
this? Your summary isn't enough.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 3
right.
Not like we've not been here before, eh?
[I'll look at the tech another day]
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
601 - 700 of 8408 matches
Mail list logo