> How it will help? This is in addition to trigger proposal that came
> up earlier. With triggers it's not possible to make values visible
> across backends unless trigger updates a table, which eventually
> leads to vacuum/dead tuples problem.
>
> 1. User creates a trigger to check updates/insert
Bruce Momjian wrote:
OK, I beefed up the TODO:
* Use a fixed row count and a +/- count with MVCC visibility rules
to allow fast COUNT(*) queries with no WHERE clause(?)
I can always give the details if someone asks. It doesn't seem complex
enough for a separate TODO.detail item.
> And for those GUIs, wouldn't it be 97% as good to run an ANALYZE and give the
> approximate record counts for large tables?
Interfaces which run a COUNT(*) like that are broken by design. They
fail to consider the table may really be a view which of course could
not be cached with results like
Bruce,
> OK, I beefed up the TODO:
>
> * Use a fixed row count and a +/- count with MVCC visibility rules
> to allow fast COUNT(*) queries with no WHERE clause(?)
>
> I can always give the details if someone asks. It doesn't seem complex
> enough for a separate TODO.detail item.
Hm
Quoth [EMAIL PROTECTED] (Tom Lane):
> Bruce Momjian <[EMAIL PROTECTED]> writes:
>> We do have a TODO item:
>> * Consider using MVCC to cache count(*) queries with no WHERE clause
>
>> The idea is to cache a recent count of the table, then have
>> insert/delete add +/- records to the count. A
Bruce Momjian <[EMAIL PROTECTED]> writes:
> It doesn't seem complex enough for a separate TODO.detail item.
I thought it was, if only because it is so easy to think of wrong
implementations.
regards, tom lane
---(end of broadcast)--
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> ... and it would give the wrong answers. Unless the cache is somehow
> >> snapshot-aware, so that it can know which other transactions should be
> >> included in your count.
>
> > The cache is an ordinary table,
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> ... and it would give the wrong answers. Unless the cache is somehow
>> snapshot-aware, so that it can know which other transactions should be
>> included in your count.
> The cache is an ordinary table, with xid's on every row. I me
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > We do have a TODO item:
> > * Consider using MVCC to cache count(*) queries with no WHERE clause
>
> > The idea is to cache a recent count of the table, then have
> > insert/delete add +/- records to the count. A COUNT(*) would g
Bruce Momjian <[EMAIL PROTECTED]> writes:
> We do have a TODO item:
> * Consider using MVCC to cache count(*) queries with no WHERE clause
> The idea is to cache a recent count of the table, then have
> insert/delete add +/- records to the count. A COUNT(*) would get the
> main cached recor
Christopher Browne wrote:
> [EMAIL PROTECTED] (Jean-Luc Lachance) writes:
> > That's one of the draw back of MVCC.
> > I once suggested that the transaction number and other house keeping
> > info be included in the index, but was told to forget it...
> > It would solve once and for all the issue
On our message boards each post is a row. The powers that be like to know
how many posts there are total (In addition to 'today')-
select count(*) from posts is how it has been
done on our informix db. With our port to PG I instead select reltuples
pg_class.
We have exactly the same situation, e
In the last exciting episode, [EMAIL PROTECTED] (Jean-Luc Lachance) wrote:
> Well I can think of many more case where it would be usefull:
>
> SELECT COUNT(DISTINCT x) FROM ...
> SELECT COUNT(*) FROM ... WHERE x = ?
Those are precisely the cases that the "other databases" ALSO fall
down on.
Maint
-
>From: Dror Matalon [mailto:[EMAIL PROTECTED]
>Sent: Thu 10/2/2003 9:27 PM
>To: [EMAIL PROTECTED]
>Cc:
>Subject:Re: [PERFORM] count(*) slow on large tables
>
>
>I smell a religious war in the aii:-).
>Can you go several days in a row without doing s
Well I can think of many more case where it would be usefull:
SELECT COUNT(DISTINCT x) FROM ...
SELECT COUNT(*) FROM ... WHERE x = ?
Also having transaction number (visibility) would tip the balance more
toward index_scan than seq_scan because you do not have to look up
visibility in the data fi
On Thu, 2 Oct 2003, Christopher Browne wrote:
> I can't imagine why the raw number of tuples in a relation would be
> expected to necessarily be terribly useful.
>
We use stuff like that for reporting queries.
example:
On our message boards each post is a row. The powers that be like to know
ho
Oops! [EMAIL PROTECTED] (Dror Matalon) was seen spray-painting on a wall:
> I smell a religious war in the aii:-).
> Can you go several days in a row without doing select count(*) on any
> of your tables?
I would be more likely, personally, to run "VACUUM VERBOSE ANALYZE",
which has useful side-
Dror Matalon wrote:
I smell a religious war in the aii:-).
Can you go several days in a row without doing select count(*) on any
of your tables?
I suspect that this is somewhat a domain specific issue. In some areas
you don't need to know the total number of rows in your tables, in
others you d
MAIL PROTECTED]
Cc:
Subject:Re: [PERFORM] count(*) slow on large tables
I smell a religious war in the aii:-).
Can you go several days in a row without doing select count(*) on any
of your tables?
I suspect that this is somewhat a domain specific issue. In some areas
you don't ne
Christopher Browne <[EMAIL PROTECTED]> writes:
> It would be very hairy to implement it correctly, and all this would
> cover is the single case of "SELECT COUNT(*) FROM SOME_TABLE;"
>
> If you had a single WHERE clause attached, you would have to revert to
> walking through the tuples looking f
I smell a religious war in the aii:-).
Can you go several days in a row without doing select count(*) on any
of your tables?
I suspect that this is somewhat a domain specific issue. In some areas
you don't need to know the total number of rows in your tables, in
others you do.
I also suspect
The world rejoiced as [EMAIL PROTECTED] (Dror Matalon) wrote:
> I don't have an opinion on how hard it would be to implement the
> tracking in the indexes, but "select count(*) from some table" is, in my
> experience, a query that people tend to run quite often.
> One of the databases that I've us
I don't have an opinion on how hard it would be to implement the
tracking in the indexes, but "select count(*) from some table" is, in my
experience, a query that people tend to run quite often.
One of the databases that I've used, I believe it was Informix, had that
info cached so that it always
[EMAIL PROTECTED] (Jean-Luc Lachance) writes:
> That's one of the draw back of MVCC.
> I once suggested that the transaction number and other house keeping
> info be included in the index, but was told to forget it...
> It would solve once and for all the issue of seq_scan vs index_scan.
> It wou
That's one of the draw back of MVCC.
I once suggested that the transaction number and other house keeping
info be included in the index, but was told to forget it...
It would solve once and for all the issue of seq_scan vs index_scan.
It would simplify the aggregate problem.
Bruno Wolff III wro
On Thu, Oct 02, 2003 at 12:46:45 -0700,
Dror Matalon <[EMAIL PROTECTED]> wrote:
Please keep replies copied to the list.
> When would it happen that a tuple be invisible to the current
> transaction? Are we talking about permissions?
They could be tuples that were changed by a transaction that
Hi,
I have a somewhat large table, 3 million rows, 1 Gig on disk, and growing. Doing a
count(*) takes around 40 seconds.
Looks like the count(*) fetches the table from disk and goes through it.
Made me wonder, why the optimizer doesn't just choose the smallest index
which in my case is around 60
On Thu, Oct 02, 2003 at 12:15:47 -0700,
Dror Matalon <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have a somewhat large table, 3 million rows, 1 Gig on disk, and growing. Doing a
> count(*) takes around 40 seconds.
>
> Looks like the count(*) fetches the table from disk and goes through it.
> Made m
28 matches
Mail list logo
