On Tue, 2006-01-17 at 13:53 +0100, Magnus Hagander wrote:
On this table, I do a query like:
SELECT * FROM path WHERE path LIKE 'f:/userdirs/s/super_73/%'
The estimate for this query is comlpetely off, which I beleive is the
cause for a very bad selection of a query plan when it's used in a
On Mon, 2006-01-16 at 19:03 -0500, uwcssa wrote:
Fine. The rest documentation says: For now, the test only uses
restriction clauses (those in restrictinfo_list). --Nels, Dec '92,
however, I understand it as being overridden by the
followup, which is:XXX as of 7.1, equivalence class info *is*
I have tried upping the statistics target up to 1000, with
no changes.
Any way to teach the planner about this?
In a recent thread on -perform, I opined that this case could
best be solved by using dynamic random block sampling at plan
time followed by a direct evaluation of the
Jim C. Nasby jnasby at pervasive.com writes:
a) the optimizer does a really poor job on multi-column index statistics
So it should be fixed?
And there are a *lot* of singular, natural keys.
b) If each parent record will have many children, the space savings from
using a surrogate key can
Greg Stark gsstark at mit.edu writes:
I hate knee-jerk reactions too, but just think of all the pain of people
dealing with databases where they used Social Security numbers for primary
keys. I would never use an attribute that represents some real-world datum as
a primary key any more.
I am
Andrew Dunstan andrew at dunslane.net writes:
If people would like to play, I have created a little kit to help in
creating first class enum types in a few seconds.
Isn't what we actually want possreps?
---(end of broadcast)---
TIP 6: explain
On Wed, Jan 18, 2006 at 01:08:53PM +, Leandro Guimarães Faria Corcete DUTRA
wrote:
Jim C. Nasby jnasby at pervasive.com writes:
Generally, I just use surrogate keys for everything unless performance
dictates something else.
What I am proposing is the reverse: use natural keys for
Leandro Guimarães Faria Corcete DUTRA wrote:
Andrew Dunstan andrew at dunslane.net writes:
If people would like to play, I have created a little kit to help in
creating first class enum types in a few seconds.
Isn't what we actually want possreps?
You appear to be
On Jan 18, 2006, at 22:08 , Leandro Guimarães Faria Corcete DUTRA wrote:
Jim C. Nasby jnasby at pervasive.com writes:
a) the optimizer does a really poor job on multi-column index
statistics
So it should be fixed?
Of course! Patches welcome!
Michael Glaesemann
grzm myrealbox com
Hi,
Currently due to the way unique constraints are tied to btree there is
no way to allow GiST indexes to do the same thing. The thing I'm
specifically interested in is an index where you insert ranges
(start,end) and if unique, the index will complain if they overlap. As
a side-effect, this may
I thought gistinsert had checkUnique, it was just ifdef'd out because
there was no code to enforce it... and as such, during bootstrap it was
marked as amcanunique = false. Would it be that hard to enable it?On 1/18/06, Martijn van Oosterhout kleptog@svana.org
wrote:Hi,Currently due to the way
Leandro Guimarães Faria Corcete DUTRA [EMAIL PROTECTED] writes:
Greg Stark gsstark at mit.edu writes:
I hate knee-jerk reactions too, but just think of all the pain of people
dealing with databases where they used Social Security numbers for primary
keys. I would never use an attribute
On Wed, Jan 18, 2006 at 09:15:04AM -0500, Jonah H. Harris wrote:
I thought gistinsert had checkUnique, it was just ifdef'd out because there
was no code to enforce it... and as such, during bootstrap it was marked as
amcanunique = false. Would it be that hard to enable it?
Well, it has the
I think I understand what you're saying, just that I don't think the btree index has anything to do with it.The extensibility is there for indexes to handle uniques in any way they choose. If you wanted to add a common unique index checking function for GIST, I'd just add it to GIST. It just seems
Yep, I couldn't find a better way to do it when I added
debug_query_string long ago. Unless we go to a lot of work to parse the
string, we could end up with something worse than we have now.
---
Neil Conway wrote:
While
Simon Riggs [EMAIL PROTECTED] writes:
On Tue, 2006-01-17 at 13:53 +0100, Magnus Hagander wrote:
Any way to teach the planner about this?
In a recent thread on -perform, I opined that this case could best be
solved by using dynamic random block sampling at plan time followed by a
direct
On Wed, Jan 18, 2006 at 01:08:53PM +, Leandro Guimar??es Faria Corcete
DUTRA wrote:
b) If each parent record will have many children, the space savings from
using a surrogate key can be quite large
Not such a common case.
Hmmm...
Many blog entries per user... Many blog comments per
Hi,
in my machine (win xp) i was trying to start psql (8.1.1) with
log_min_messages to debug5 (just to see the messages :) but even the
service start i cannot use psql nor pgadmin i receive an error of
server closed the connection unexpectedly
postgres=# select version();
Allow me a brief introduction. I work in a company who contracts
intelligence analysis software to the government. We are currently
developing a product which is using PostgreSQL at it's core. Due to the
licensing of the product and the integration with perl this is our first
choice in
David Scott [EMAIL PROTECTED] writes:
Is the additional overhead of keeping full tuple visibility
information inside of the index so odious to the Postgres community as
to prevent a patch with this solution from being applied back to the
head?
This has been discussed and rejected
On Wed, 2006-01-18 at 10:37 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
On Tue, 2006-01-17 at 13:53 +0100, Magnus Hagander wrote:
Any way to teach the planner about this?
In a recent thread on -perform, I opined that this case could best be
solved by using dynamic
David,You can find some of this discussion in Much Ado About COUNT(*). Related to that discussion, I had written a patch which added visibility information to the indexes.If you're interested in the patch and/or consulting, contact me offline.
-JonahOn 1/18/06, Tom Lane [EMAIL PROTECTED] wrote:
Martijn van Oosterhout kleptog@svana.org writes:
check_unique_index( ctid of inserting tuple, ctid of possibly
conflicting tuple)
I agree it's pretty ugly to have the index AM directly poking into
the heap, but adding a level of subroutine doesn't really make that
a whole lot nicer :-(.
In any
Martijn van Oosterhout kleptog@svana.org writes:
I guess what you're talking about is a constrained index, of which a
unique index is just a particular type. I suppose the actual constraint
would be one of the operators defined for the operator class (since
whatever the test is, it needs to be
On Wed, Jan 18, 2006 at 04:10:16PM -0500, Tom Lane wrote:
Martijn van Oosterhout kleptog@svana.org writes:
check_unique_index( ctid of inserting tuple, ctid of possibly
conflicting tuple)
I agree it's pretty ugly to have the index AM directly poking into
the heap, but adding a level of
On Wed, Jan 18, 2006 at 04:18:10PM -0500, Tom Lane wrote:
I think the generalization that would be appropriate for GIST is that
a unique index guarantees there are no two entries x, y such that
x ~ y, where ~ is some boolean operator nominated by the opclass. We'd
probably have to insist that
On Wed, 2006-01-18 at 12:14 -0800, David Scott wrote:
Is the additional overhead of keeping full tuple visibility
information inside of the index so odious to the Postgres community
as
to prevent a patch with this solution from being applied back to the
head? Maybe as an optional use
Martijn van Oosterhout kleptog@svana.org writes:
On Wed, Jan 18, 2006 at 04:18:10PM -0500, Tom Lane wrote:
In btree we can identify a unique page to lock for any given key value
to ensure that no one else is concurrently inserting a conflicting
key, thus usually allowing concurrent insertions
On Wed, Jan 18, 2006 at 12:14:12PM -0800, David Scott wrote:
Do commercial databases implement MVCC in a way that allows an
efficient implementation of index lookups that can avoid heap lookups?
Oracle does, but you pay in other ways. Instead of keeping dead tuples
in the main heap, they
On Wed, Jan 18, 2006 at 04:02:45PM -0500, Jonah H. Harris wrote:
David,
You can find some of this discussion in Much Ado About COUNT(*). Related
to that discussion, I had written a patch which added visibility information
to the indexes.
If you're interested in the patch and/or
Ooops, fat-finger'd -hackers...
-Original Message-
Adding -hackers back to the list.
From: Leandro Guimarães Faria Corcete Dutra
Em Seg, 2006-01-16 às 12:52 -0600, Jim C. Nasby escreveu:
On Sat, Jan 14, 2006 at 07:28:21PM +0900, Michael Glaesemann wrote:
For UPDATEs and
Andrew Dunstan wrote:
Tom Lane wrote:
Andrew Dunstan [EMAIL PROTECTED] writes:
dllwrap doesn't seem to get given LDFLAGS, and maybe doesn't honor it
either.
I wouldn't expect it to handle everything that might appear in LDFLAGS,
but maybe it ought to be given the -L
Simon Riggs [EMAIL PROTECTED] writes:
You might want to consider the thought of organised heaps as an
alternative thought to index improvements. That way there is no heap to
avoid visiting because the index is also the main data structure.
This would offer performance, but would be one of the
Comparing two ints is much, much faster than comparing two text
fields. For a small number of comparisons, it doesn't matter. When
you're joining tables together, it's a different story.
That is where data independence would come handy... like a better enum,
with possreps and hidden
Martjin,
Interesting. However, in my experience very few things have natural
keys. There are no combination of attributes for people, phone calls
or even real events that make useful natural keys.
I certainly hope that I never have to pick up one of your projects. A
table without a natural
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Josh Berkus
Sent: Wednesday, January 18, 2006 3:59 PM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Surrogate keys (Was: enums)
Martjin,
Interesting. However, in my
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Dann Corbit
Sent: Wednesday, January 18, 2006 4:04 PM
To: josh@agliodbs.com; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Surrogate keys (Was: enums)
-Original Message-
On Wed, 2006-01-18 at 18:27 -0500, Tom Lane wrote:
Imagine an index that
contains only the upper levels of a search tree --- links to what
would be the leaf level point into the associated heap. In this
design
the heap is still a heap in the sense that you can seqscan it without
any
Bruce Momjian wrote:
Could this be related to the fact that pre-8.2 makefiles were not
space-safe? I am unsure how pgxs worked on Win32 without being
space-safe.
I don't see how. In fact, pgxs seems to use short form paths anyway.
Example (from previous email):
dllwrap -o
Em Qua, 2006-01-18 às 17:22 -0600, Jim C. Nasby escreveu:
Forgive me my ignorance, but are ints inherently faster to compare than
strings, or is it just an implementation detail? Ideally, if this is so
a fully data-independent system would create a hash behind the back of
user in order
Simon Riggs [EMAIL PROTECTED] writes:
Hopefully we could avoid trying to support GIST-heaps?
Well, that would be an extra index AM that someone might or might not
get around to writing someday. I was thinking that both btree and hash
index AMs might be interesting for this, though. Hash in
On Jan 19, 2006, at 9:31 , Leandro Guimarães Faria Corcete Dutra wrote:
OK. Again, data independence should be the goal here.
snip /
Again, implementation details... levels mixup.
snip /
Lack of data implementation biting us again.
snip /
Yet real
Simon Riggs [EMAIL PROTECTED] writes:
We only need to index the row with the lowest value on any page so the main
index would get 100 times smaller. The main part of the index would not
need to be written to except when a block overflows.
BTW, the above is equivalent to saying that the
Oracle does, but you pay in other ways. Instead of keeping dead tuples
in the main heap, they shuffle them off to an 'undo log'. This has some
downsides:
Rollbacks take *forever*, though this usually isn't much of an issue
unless you need to abort a really big transaction.
It's a good point
On Thu, Jan 19, 2006 at 09:18:55AM +0800, Christopher Kings-Lynne wrote:
Oracle does, but you pay in other ways. Instead of keeping dead tuples
in the main heap, they shuffle them off to an 'undo log'. This has some
downsides:
Rollbacks take *forever*, though this usually isn't much of an
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
Oracle does, but you pay in other ways. Instead of keeping dead tuples
in the main heap, they shuffle them off to an 'undo log'. This has some
downsides:
Rollbacks take *forever*, though this usually isn't much of an issue
unless you need to
On Wed, Jan 18, 2006 at 08:13:59PM -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
We only need to index the row with the lowest value on any page so the main
index would get 100 times smaller. The main part of the index would not
need to be written to except when a block
Jim C. Nasby [EMAIL PROTECTED] writes:
Would this open the door for allowing tables to be maintained in CLUSTER
order (at least at the block level if not within the blocks)? Though I
have no idea how you'd handle page splits without a lot of pain
I think the way you'd attack that is by
Maybe it goes better into Advocacy or something, but I have found a quote by
database big-wigs that I strongly disagree with:
From:
http://www.db.ucsd.edu/cse132B/Thirdmanifesto.pdf
We have this.
PROPOSITION 1.4: Unique Identifiers (UIDs) for records should be assigned by
the DBMS only if a
On Jan 19, 2006, at 10:34 , Dann Corbit wrote:
http://www.db.ucsd.edu/cse132B/Thirdmanifesto.pdf
PROPOSITION 1.4: Unique Identifiers (UIDs) for records should be
assigned by the DBMS only if a user-defined primary key is not
available.
snip /
An immutable primary key has an extra
-Original Message-
From: Michael Glaesemann [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 18, 2006 5:48 PM
To: Dann Corbit
Cc: Leandro Guimarães Faria Corcete Dutra; Jim C. Nasby; pgsql-
[EMAIL PROTECTED]
Subject: Re: [HACKERS] Surrogate keys (Was: enums)
On Jan 19, 2006,
Hi Hackers,
I'm trying the following TODO item:
[Indexes]
- Add fillfactor to control reserved free space during index creation
I have already made an patch and it seemed to work well.
$ ./pgbench -i -s 10
# select relpages from pg_class where relname = 'accounts_pkey';
relpages |
On Thu, 2006-01-19 at 09:18 +0800, Christopher Kings-Lynne wrote:
Oracle does, but you pay in other ways. Instead of keeping dead tuples
in the main heap, they shuffle them off to an 'undo log'. This has some
downsides:
Rollbacks take *forever*, though this usually isn't much of an
Andrew Dunstan wrote:
Bruce Momjian wrote:
Could this be related to the fact that pre-8.2 makefiles were not
space-safe? I am unsure how pgxs worked on Win32 without being
space-safe.
I don't see how. In fact, pgxs seems to use short form paths anyway.
Example (from
Dann,
The primary key should be immutable, meaning that its value should not be
changed during the course of normal operations of the database.
Why? I don't find this statement to be self-evident. Why would we have ON
UPDATE CASCADE if keys didn't change sometimes?
At any rate, the use
I am testing the performance of postgresql on a set of workloads. However,
the output significantly affects the performance evaluation. Is there a way
to by-pass all output of select statements so the timing reflects only the
query evaluation process?
thanks a lot
On Wed, Jan 18, 2006 at 10:35:48PM -0500, uwcssa wrote:
I am testing the performance of postgresql on a set of workloads. However,
the output significantly affects the performance evaluation. Is there a way
to by-pass all output of select statements so the timing reflects only the
query
Bruce Momjian wrote:
Andrew Dunstan wrote:
Bruce Momjian wrote:
Could this be related to the fact that pre-8.2 makefiles were not
space-safe? I am unsure how pgxs worked on Win32 without being
space-safe.
I don't see how. In fact, pgxs seems to use short form
Michael Glaesemann [EMAIL PROTECTED] writes:
As far as I can tell, the only difference between your position,
Dann, and Date and Darwen's, is that you think no natural key is
immutable.
DD's examples of natural keys are worth a second look though:
If a primary key exists for a collection
Hello,
Odd problem with unique indexes:
8.0.5 64 bit (Quad Opteron)
100 tables, each table has same layout, 1 million rows per table. The
problem persists within multiple tables
but only within the set of 100 tables.
I have a composite unique key on each table:
uniq1 UNIQUE, btree
Tom Lane [EMAIL PROTECTED] writes:
Martijn van Oosterhout kleptog@svana.org writes:
I guess what you're talking about is a constrained index, of which a
unique index is just a particular type. I suppose the actual constraint
would be one of the operators defined for the operator class
I've been working on getting the system to pass regression tests cleanly
when forcing a cache flush at every possible instant. The main tests
pass now (in 8.1 --- HEAD remains broken pending lookup_rowtype_tupdesc
fix), but contrib is still crashing. On investigation the problem turns
out to be
If a primary key exists for a collection that is known never to change,
for example social security number, student registration number, or
employee number, then no additional system-assigned UID is required.
In point of fact Social security numbers *can* change.
--
greg
Tom Lane [EMAIL PROTECTED] writes:
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
Oracle does, but you pay in other ways. Instead of keeping dead tuples
in the main heap, they shuffle them off to an 'undo log'. This has some
downsides:
Rollbacks take *forever*, though this usually
Joshua D. Drake [EMAIL PROTECTED] writes:
Odd problem with unique indexes:
What's the database's locale? This could be the same problem fixed in
8.0.6, if the locale has weird ideas about what string equality means.
regards, tom lane
---(end of
David Scott [EMAIL PROTECTED] writes:
Since I am sure everyone is tired of the intro by now, I'll get to the
questions:
...
Is there any way to modify PostgreSQL to allow index lookups without heap
validation that doesn't involve re-writing the MVCC implementation of
keeping dead rows on the
Greg Stark [EMAIL PROTECTED] writes:
You pay in Oracle when you read these records too. If there are pending
updates you have to do a second read to the rollback segment to get the old
record. This hits long-running batch queries especially hard since by the time
they finish a large number of
Greg Stark [EMAIL PROTECTED] writes:
I wonder if the bitmap can actually be one bit per page actually.
Yeah, I think we'd agreed that per-page was the way to go. Per-tuple
bitmaps are painful to manage because of the variable number of tuples
per page. And really all you need to know is
On Wed, Jan 18, 2006 at 03:58:50PM -0800, Josh Berkus wrote:
Martjin,
Interesting. However, in my experience very few things have natural
keys. There are no combination of attributes for people, phone calls
or even real events that make useful natural keys.
I certainly hope that I
Tom Lane [EMAIL PROTECTED] writes:
This would still support REINDEX (which changes pg_class.relfilenode in
order to replace the physical file) and ALTER INDEX SET TABLESPACE.
But you couldn't make any meaningful changes in the definition of an
index, such as changing its column set, operator
Greg Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
But you couldn't make any meaningful changes in the definition of an
index, such as changing its column set, operator classes, partial-index
predicate, etc, except by dropping and recreating it.
The only example that
71 matches
Mail list logo
