Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Manfred Koizar)
would write:
On Mon, 24 Jan 2005 08:28:09 -0700, Jonah H. Harris [EMAIL PROTECTED]
wrote:
UPDATE pg_user_table_counts
SET rowcount = rowcount + 1
WHERE schemaname =
Here's a possible solution... though I'm not sure about whether you find
the pg_ prefix appropriate for this context.
-- Create a Test Relation
CREATE TABLE test_tbl (
test_id BIGINT NOT NULL,
test_value VARCHAR(128) NOT NULL,
PRIMARY KEY (test_id));
-- Create COUNT
On Mon, 24 Jan 2005 08:28:09 -0700, Jonah H. Harris [EMAIL PROTECTED]
wrote:
UPDATE pg_user_table_counts
SET rowcount = rowcount + 1
WHERE schemaname = this_schemaname
AND tablename = TG_RELNAME;
This might work for small single user
Bruce Momjian wrote:
Added to TODO based on this discusion:...
* Speed up COUNT(*)
One think I think would help lots of people is if the
documentation near the COUNT aggregate explained some
of the techniques using triggers to maintain a count
for tables where this is important.
For every one
On Sun, Jan 23, 2005 at 12:36:10AM -0800, Ron Mayer wrote:
Bruce Momjian wrote:
Added to TODO based on this discusion:...
* Speed up COUNT(*)
One think I think would help lots of people is if the
documentation near the COUNT aggregate explained some
of the techniques using triggers to
Jim C. Nasby wrote:
Does anyone have working code they could contribute? It would be best to
give at least an example in the docs. Even better would be something in
pgfoundry that helps build a summary table and the rules/triggers you
need to maintain it.
Added to TODO based on this discusion:
---
* Speed up COUNT(*)
We could use a fixed row count and a +/- count to follow MVCC
visibility rules, or a single cached value could be used and
invalidated if anyone modifies
Tom Lane wrote:
Tom Lane [EMAIL PROTECTED] writes:
Manfred Koizar [EMAIL PROTECTED] writes:
Last time we discussed this, didn't we come to the conclusion, that
resetting status bits is not a good idea because of possible race
conditions?
There's no race condition,
Actually, wait a
-Original Message-
From: Jeff Davis [mailto:[EMAIL PROTECTED]
Sent: 19 January 2005 21:33
To: Alvaro Herrera
Cc: Mark Cave-Ayland; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Much Ado About COUNT(*)
To fill in some details I think what he's saying
On Thu, 20 Jan 2005 10:12:17 -
Mark Cave-Ayland [EMAIL PROTECTED] wrote:
Thanks for the information. I seem to remember something similar to
this being discussed last year in a similar thread. My only real issue
I can see with this approach is that the trigger is fired for every
row, and
D'Arcy J.M. Cain wrote:
On Thu, 20 Jan 2005 10:12:17 -
Mark Cave-Ayland [EMAIL PROTECTED] wrote:
Thanks for the information. I seem to remember something similar to
this being discussed last year in a similar thread. My only real issue
I can see with this approach is that the trigger is fired
-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED]
Sent: 20 January 2005 12:45
To: D'Arcy J.M. Cain
Cc: Mark Cave-Ayland; [EMAIL PROTECTED];
[EMAIL PROTECTED]; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Much Ado About COUNT(*)
D'Arcy J.M. Cain wrote
On Thu, Jan 20, 2005 at 01:33:10PM -, Mark Cave-Ayland wrote:
I am sure that Jeff's approach will work, however it just seems like writing
out one table entry per row is going to slow large bulk inserts right down.
I don't see how it is any slower than the approach of inserting one
entry
Mark Cave-Ayland wrote:
-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED]
Sent: 20 January 2005 12:45
To: D'Arcy J.M. Cain
Cc: Mark Cave-Ayland; [EMAIL PROTECTED];
[EMAIL PROTECTED]; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Much Ado About COUNT(*)
D'Arcy
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Jeff Davis) wrote:
I almost think to not supply an MVCC system would break the I in ACID,
would it not? I can't think of any other obvious way to isolate the
transactions, but on the other hand, wouldn't DB2 want to be ACID
Date: Wed, 12 Jan 2005 18:45:09 -0800
From: Jeff Davis [EMAIL PROTECTED]
To: Alvaro Herrera [EMAIL PROTECTED]
Cc: pgsql-hackers@postgresql.org
Subject: Re: Much Ado About COUNT(*)
Message-ID: [EMAIL PROTECTED]
(cut)
Thanks for the link. It looks like it breaks it up into chunks of about
On Wed, Jan 19, 2005 at 14:59:17 -,
Mark Cave-Ayland [EMAIL PROTECTED] wrote:
BEGIN;
INSERT INTO person (first_name, Tel) VALUES ('Fred',
'12345');
INSERT INTO person_count(id) VALUES (currval('id_seq'));
COMMIT;
So then I would use SELECT COUNT(*)
On Wed, Jan 19, 2005 at 10:16:38AM -0600, Bruno Wolff III wrote:
On Wed, Jan 19, 2005 at 14:59:17 -,
Mark Cave-Ayland [EMAIL PROTECTED] wrote:
So then I would use SELECT COUNT(*) FROM person_count whenever I wanted to
know the current number of person records. How much quicker would a
To fill in some details I think what he's saying is this:
= create table foo(...);
= create table foo_count(num int);
= insert into foo_count values(0);
= create table foo_change(num int);
then create a trigger after delete on foo that does insert into
foo_change values(-1) and a trigger after
Jonah == Jonah H Harris [EMAIL PROTECTED] writes:
Jonah Replying to the list as a whole:
Jonah If this is such a bad idea, why do other database systems
Jonah use it? As a businessperson myself, it doesn't seem
Jonah logical to me that commercial database companies would
On Tue, 2005-01-18 at 12:45 -0800, Sailesh Krishnamurthy wrote:
Jonah == Jonah H Harris [EMAIL PROTECTED] writes:
Jonah Replying to the list as a whole:
Jonah If this is such a bad idea, why do other database systems
Jonah use it? As a businessperson myself, it doesn't seem
Jeff Davis [EMAIL PROTECTED] writes:
I almost think to not supply an MVCC system would break the I in ACID,
would it not?
Certainly not; ACID was a recognized goal long before anyone thought of
MVCC. You do need much more locking to make it work without MVCC,
though --- for instance, a reader
Tom == Tom Lane [EMAIL PROTECTED] writes:
Tom People who hang around Postgres too long tend to think that
Tom MVCC is the obviously correct way to do things, but much of
Tom the rest of the world thinks differently ;-)
It works the other way too ... people who come from the locking
Certainly not; ACID was a recognized goal long before anyone thought of
MVCC. You do need much more locking to make it work without MVCC,
though --- for instance, a reader that is interested in a just-modified
row has to block until the writer completes or rolls back.
People who hang
On Thu, 13 Jan 2005 00:39:56 -0500, Tom Lane [EMAIL PROTECTED]
wrote:
A would-be deleter of a tuple would have to go and clear the known
good bits on all the tuple's index entries before it could commit.
This would bring the tuple back into the uncertain status condition
where backends would have
Manfred Koizar [EMAIL PROTECTED] writes:
On Thu, 13 Jan 2005 00:39:56 -0500, Tom Lane [EMAIL PROTECTED]
wrote:
A would-be deleter of a tuple would have to go and clear the known
good bits on all the tuple's index entries before it could commit.
This would bring the tuple back into the
Tom Lane [EMAIL PROTECTED] writes:
Manfred Koizar [EMAIL PROTECTED] writes:
Last time we discussed this, didn't we come to the conclusion, that
resetting status bits is not a good idea because of possible race
conditions?
There's no race condition,
Actually, wait a minute --- you have a
On Sun, 16 Jan 2005 20:49:45 +0100, Manfred Koizar wrote:
On Thu, 13 Jan 2005 00:39:56 -0500, Tom Lane wrote:
A would-be deleter of a tuple would have to go and clear the known
good bits on all the tuple's index entries before it could commit.
This would bring the tuple back into the uncertain
On Sun, Jan 16, 2005 at 03:22:11PM -0500, Tom Lane wrote:
Tom Lane [EMAIL PROTECTED] writes:
Manfred Koizar [EMAIL PROTECTED] writes:
Last time we discussed this, didn't we come to the conclusion, that
resetting status bits is not a good idea because of possible race
conditions?
Jim C. Nasby [EMAIL PROTECTED] writes:
Wouldn't the original proposal that had a state machine handle this?
IIRC the original idea was:
new tuple - known good - possibly dead - known dead
Only if you disallow the transition from possibly dead back to known
good, which strikes me as a rather
On Sun, 16 Jan 2005 20:01:36 -0500, Tom Lane wrote:
Jim C. Nasby writes:
Wouldn't the original proposal that had a state machine handle this?
IIRC the original idea was:
new tuple - known good - possibly dead - known dead
Only if you disallow the transition from possibly dead back to
On Sun, Jan 16, 2005 at 08:01:36PM -0500, Tom Lane wrote:
Jim C. Nasby [EMAIL PROTECTED] writes:
Wouldn't the original proposal that had a state machine handle this?
IIRC the original idea was:
new tuple - known good - possibly dead - known dead
Only if you disallow the transition from
On Sun, Jan 16, 2005 at 07:28:07PM -0600, Jim C. Nasby wrote:
On Sun, Jan 16, 2005 at 08:01:36PM -0500, Tom Lane wrote:
Jim C. Nasby [EMAIL PROTECTED] writes:
Wouldn't the original proposal that had a state machine handle this?
IIRC the original idea was:
new tuple - known good -
Jim C. Nasby [EMAIL PROTECTED] writes:
Actually, I guess I wasn't understanding the problem to begin with.
You'd never go from new tuple to known good while the transaction that
created the tuple was in-flight, right?
By definition, not.
If that's the case, I'm not sure
where there's a race
The fundamental problem is that you can't do it without adding at least
16 bytes, probably 20, to the size of an index tuple header. That would
double the physical size of an index on a simple column (eg an integer
or timestamp). The extra I/O costs and extra maintenance costs are
unattractive
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
Ah, right, I missed the connection. Hmm ... that's sort of the inverse
of the killed tuple optimization we put in a release or two back,
where an index tuple is marked as definitely dead once it's committed
dead and the
Bruce Momjian pgman@candle.pha.pa.us writes:
Tom Lane wrote:
The ugly part of this is that clearing the bit is not like setting a
hint bit, ie it's not okay if we lose that change. Therefore, each
bit-clearing would have to be WAL-logged. This is a big part of my
concern about the cost.
On Thu, 13 Jan 2005 10:29:16 -0500
Tom Lane [EMAIL PROTECTED] wrote:
Wrong. The WAL recovery environment is not capable of executing
arbitrary user-defined functions, therefore it cannot compute index
entries on its own. The *only* way we can do this is if the WAL
record stream tells exactly
D'Arcy J.M. Cain wrote:
I'm not sure why everyone wants to push this into the database anyway.
If I need to know the count of something, I am probably in a better
position to decide what and how than the database can ever do. For
example, I recently had to track balances for certificates in a
Jonah H. Harris [EMAIL PROTECTED] writes:
Tom, Bruce, and others involved in this recurring TODO discussion
First, let me start by saying that I understand this has been discussed
many times before; however, Id like to see what the current state of
affairs is regarding the possibility of
Tom, Bruce, and others involved in this recurring TODO discussion...
First, let me start by saying that I understand this has been
discussed
many times before; however, I'd like to see what the current state of
affairs is regarding the possibility of using a unique index scan to
speed up
Tom,
Thank you for your prompt response and I understand your statement
completely.
My thinking is that we may be able to implement index usage for not only
unqualified counts, but also on any query that can be satisfied by the
index itself. Index usage seems to be a feature that could speed
Jonah H. Harris [EMAIL PROTECTED] writes:
Looking at the message boards, there is significant interest in the COUNT(*)
aspect. However, rather than solely address the COUNT(*) TODO item, why not
fix
it and add additional functionality found in commercial databases as well? I
believe Oracle
Greg Stark wrote:
I think part of the problem is that there's a bunch of features related to
these types of queries and the lines between them blur.
You seem to be talking about putting visibility information inside indexes for
so index-only plans can be performed. But you're also talking about
Jonah H. Harris [EMAIL PROTECTED] writes:
My thinking is that we may be able to implement index usage for not only
unqualified counts, but also on any query that can be satisfied by the
index itself.
The fundamental problem is that you can't do it without adding at least
16 bytes, probably
Greg Stark wrote:
I think part of the problem is that there's a bunch of features
related
to
these types of queries and the lines between them blur.
You seem to be talking about putting visibility information inside
indexes for
so index-only plans can be performed. But you're also
Tom Lane wrote:
The fundamental problem is that you can't do it without adding at least
16 bytes, probably 20, to the size of an index tuple header. That would
double the physical size of an index on a simple column (eg an integer
or timestamp). The extra I/O costs and extra maintenance costs
To: Jonah H. Harris
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Much Ado About COUNT(*)
Jonah H. Harris [EMAIL PROTECTED] writes:
My thinking is that we may be able to implement index usage for not
only
unqualified counts, but also on any query that can be satisfied by the
index itself
On Wed, 2005-01-12 at 12:52 -0700, Jonah H. Harris wrote:
Tom Lane wrote:
The fundamental problem is that you can't do it without adding at least
16 bytes, probably 20, to the size of an index tuple header. That would
double the physical size of an index on a simple column (eg an integer
Jonah H. Harris said:
Tom Lane wrote:
The fundamental problem is that you can't do it without adding at least
16 bytes, probably 20, to the size of an index tuple header. That
would double the physical size of an index on a simple column (eg an
integer or timestamp). The extra I/O costs and
Jonah H. Harris [EMAIL PROTECTED] writes:
You are correct, I am proposing to add visibility to the indexes.
Then I think the only way you'll get any support is if it's an option. Since
it would incur a performance penalty on updates and deletes.
As for unqualified counts, I believe that they
No, sequential scans require slightly more i/o than index scans. More
importantly they require random access i/o instead of sequential i/o which is
much slower.
Just to clear it up, I think what you meant was the index requires
random i/o, not the table. And the word slightly depends on
Andrew Dunstan wrote:
Monetary cost is not the issue - cost in time is the issue.
cheers
andrew
We seem to be in agreement. I'm looking for faster/smarter access to
data, not the monetary cost of doing so. Isn't it faster/smarter to
satisfy a query with the index rather than sequentially
Rod Taylor wrote:
grow by about 40GB if this was done. Storage isn't that cheap when you
include the hot-backup master, various slaves, RAM for caching of this
additional index space, backup storage unit on the SAN, tape backups,
additional spindles required to maintain same performance due to
I recognize the added cost of implementing index only scans. As storage
is relatively cheap these days, everyone I know is more concerned about
faster access to data. Similarly, it would still be faster to scan the
indexes than to perform a sequential scan over the entire relation for
On Wed, 12 Jan 2005, Jonah H. Harris wrote:
Andrew Dunstan wrote:
Monetary cost is not the issue - cost in time is the issue.
We seem to be in agreement. I'm looking for faster/smarter access to data,
not the monetary cost of doing so. Isn't it faster/smarter to satisfy a
query with the index
Jon Jensen wrote:
If you're willing to do the work, and have the motivation, probably
the best thing to do is just do it. Then you can use empirical
measurements of the effect on disk space, speed of various operations,
etc. to discuss the merits/demerits of your particular implementation.
On Wed, Jan 12, 2005 at 13:42:58 -0700,
Jonah H. Harris [EMAIL PROTECTED] wrote:
We seem to be in agreement. I'm looking for faster/smarter access to
data, not the monetary cost of doing so. Isn't it faster/smarter to
satisfy a query with the index rather than sequentially scanning an
We seem to be in agreement. I'm looking for faster/smarter access to
data, not the monetary cost of doing so. Isn't it faster/smarter to
satisfy a query with the index rather than sequentially scanning an
entire relation if it is possible?
You have to scan every tuple's visibility
Jeff Davis wrote:
Does someone know exactly what oracle actually does?
some old info resides here, http://www.orsweb.com/techniques/fastfull.html
I'll try and find something more recent.
---(end of broadcast)---
TIP 8: explain analyze is your
in reporting.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Jonah H. Harris
Sent: Wednesday, January 12, 2005 8:36 PM
To: Greg Stark
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Much Ado About COUNT(*)
Greg Stark wrote:
I think part
On Wed, Jan 12, 2005 at 12:41:38PM -0800, Jeff Davis wrote:
Except then the two heaps would have to be joined somehow for every
operation. It makes sense some times to (if you have a very wide table)
split off the rarely-accessed attributes into a seperate table to be
joined one-to-one when
On Wed, Jan 12, 2005 at 14:09:07 -0700,
Jonah H. Harris [EMAIL PROTECTED] wrote:
Please keep stuff posted to the list so that other people can contribute
and learn from the discussion unless there is a particular reason to
limited who is involved in the discussion.
Bruno,
Thanks for the
On Wed, 2005-01-12 at 15:09 -0500, Rod Taylor wrote:
On Wed, 2005-01-12 at 12:52 -0700, Jonah H. Harris wrote:
Tom Lane wrote:
The fundamental problem is that you can't do it without adding at least
16 bytes, probably 20, to the size of an index tuple header. That would
double the
Bruno Wolff III wrote:
On Wed, Jan 12, 2005 at 14:09:07 -0700,
Jonah H. Harris [EMAIL PROTECTED] wrote:
Please keep stuff posted to the list so that other people can contribute
and learn from the discussion unless there is a particular reason to
limited who is involved in the discussion.
not a
Simon Riggs wrote:
Jonah,
People's objections are:
- this shouldn't be the system default, so would need to be implemented
as a non-default option on a b-tree index
- its a lot of code and if you want it, you gotta do it
Remember you'll need to
- agree all changes via the list and accept that
The index could then make sensible the reasonably common practice of
using a covered index - i.e. putting additional columns into the index
to satisfy the whole query just from the index.
I am willing to take it on and I understand that the workload is mine.
As long as everyone gives me
That mechanism exists right now, and it's called TOAST, dubbed the best
thing since sliced bread. We even have documentation for it, new as of
our latest RC:
http://developer.postgresql.org/docs/postgres/storage-toast.html
Thanks for the link. It looks like it breaks it up into chunks of
Jeff Davis [EMAIL PROTECTED] writes:
But of course, we all love toast. Everyone needs to make those wide
tables once in a while, and toast does a great job of taking those
worries away in an efficient way. I am just saying that hopefully we
don't have to seqscan a table with wide tuples very
Greg Stark [EMAIL PROTECTED] writes:
I thought toast only handled having individual large columns. So if I have a
2kb text column it'll pull that out of the table for me. But if I have 20
columns each of which have 100 bytes will it still help me? Will it kick in if
I define a single column
Jonah H. Harris wrote:
1. Is there any answer to Bruce?s last statement in the thread, ?Re:
[PERFORM] COUNT(*) again (was Re: Index/Function organized?
(http://archives.postgresql.org/pgsql-hackers/2003-10/msg00245.php)
Let me give you my ideas in the above URL and why they are probably
Bruce Momjian pgman@candle.pha.pa.us writes:
My basic idea was to keep a status bit on each index entry telling it if
a previous backend looked at the heap and determined it was valid.
Even if you could track the tuple's committed-good status reliably, that
isn't enough under MVCC. The tuple
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
My basic idea was to keep a status bit on each index entry telling it if
a previous backend looked at the heap and determined it was valid.
Even if you could track the tuple's committed-good status reliably,
that isn't enough
Bruce Momjian pgman@candle.pha.pa.us writes:
Tom Lane wrote:
Even if you could track the tuple's committed-good status reliably,
that isn't enough under MVCC.
I mentioned that:
(Oh, and you could only update the bit when all active transactions
are newer than the creation transaction so we
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
Tom Lane wrote:
Even if you could track the tuple's committed-good status reliably,
that isn't enough under MVCC.
I mentioned that:
(Oh, and you could only update the bit when all active transactions
are newer than the
Bruce Momjian pgman@candle.pha.pa.us writes:
Ah, right, I missed the connection. Hmm ... that's sort of the inverse
of the killed tuple optimization we put in a release or two back,
where an index tuple is marked as definitely dead once it's committed
dead and the deletion is older than all
76 matches
Mail list logo
