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 =
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 sing
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 Collecto
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.
http://developer.postgresql.org/docs/postgr
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 trigger
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 pers
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,
>
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
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
> com
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
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
> -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 C
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 fir
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
> -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
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
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
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
> 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 chunk
> 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
> "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 t
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 re
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,
> "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
"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'
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
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
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 de
"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
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
> >> condit
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 "
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
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 int
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 wo
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 dat
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 ex
Bruce Momjian 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.
> Yep, that was
Tom Lane wrote:
> Bruce Momjian 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 old
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 to
Bruce Momjian 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 active transac
Tom Lane wrote:
> Bruce Momjian 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 creat
Bruce Momjian 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 know they sh
Tom Lane wrote:
> Bruce Momjian 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
Bruce Momjian 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 might be committed goo
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
w
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 colu
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
> 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 chun
> >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 give
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 redes
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.
no
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
> > >d
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
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 whe
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 of
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 frien
> >
> 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 visibil
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
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.
Then
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 r
> >
> 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
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
incr
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 sca
> 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
"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
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 cos
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 int
12, 2005 11:42 AM
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 q
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 are
> 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'
"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, prob
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 q
"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
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 up
> 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
"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 possibili
76 matches
Mail list logo
