In article [EMAIL PROTECTED],
Dawid Kuroczko [EMAIL PROTECTED] writes:
Use
EXPLAIN SELECT * FROM yourcountedtable;
Planner seems to track estimated statistics on-the-fly. :)
You can even wrap EXPLAIN SELECT in a pgsql function if you
need it.
Do you know how to do that? A function
Harald Fuchs [EMAIL PROTECTED] writes:
FOR row IN EXECUTE 'EXPLAIN SELECT * FROM ' || tbl LOOP
fails with the following message:
ERROR: cannot open non-SELECT query as cursor
[ checks CVS history... ] Use 8.0.2 or later.
regards, tom lane
Chris Browne wrote:
None of those transactions have COMMITted, so there are some 78 tuples
in limbo spread across 16 transactions.
If there were some single secret place with a count, how would you
suggest it address those 78 tuples and 16 transactions that aren't yet
(and maybe never will
On Fri, Jul 08, 2005 at 07:12:26 -0700,
Steve Wampler [EMAIL PROTECTED] wrote:
Hmmm, I understand this and don't doubt it, but out of curiousity, how
does the current SELECT COUNT(*) handle this? It doesn't lock the entire
It only counts tuples visible to the current transaction.
table
Bruno Wolff III wrote:
No, it is an exact count.
Yes, for the transaction, but it's an approximation of the number of
tuples in the table - which is probably what the people who worry about
its cost are more interested in (an approximate count for the table).
I'm also claiming that a true count
So, leave COUNT(*) alone. But it would be very handy to have a
way to get an approximate table size that is more accurate than is
provided by a pg_class.reltuples that is only updated on vacuums.
Create 2 sequences, one for counting tuple additions and one for
counting tuple deletions.
When
On 7/8/05, Steve Wampler [EMAIL PROTECTED] wrote:
None of those transactions have COMMITted, so there are some 78 tuples
in limbo spread across 16 transactions.
If there were some single secret place with a count, how would you
suggest it address those 78 tuples and 16 transactions that
On Fri, Jul 08, 2005 at 08:07:27AM -0700, Steve Wampler wrote:
Bruno Wolff III wrote:
No, it is an exact count.
Yes, for the transaction, but it's an approximation of the number of
tuples in the table - which is probably what the people who worry about
its cost are more interested in (an
On 7/8/05, Rod Taylor [EMAIL PROTECTED] wrote:
Create 2 sequences, one for counting tuple additions and one for
counting tuple deletions.
When you INSERT a tuple, bump the added sequence (select nextval());
When you DELETE a tuple, bump the deleted sequence (select nextval());
To
Steve Wampler [EMAIL PROTECTED] writes:
So, leave COUNT(*) alone. But it would be very handy to have a
way to get an approximate table size that is more accurate than is
provided by a pg_class.reltuples that is only updated on vacuums.
If you want something cheap, you could use the same
On Fri, 2005-07-08 at 17:34 +0200, Dawid Kuroczko wrote:
On 7/8/05, Rod Taylor [EMAIL PROTECTED] wrote:
Create 2 sequences, one for counting tuple additions and one for
counting tuple deletions.
When you INSERT a tuple, bump the added sequence (select nextval());
When you DELETE a
Tom Lane wrote:
Steve Wampler [EMAIL PROTECTED] writes:
So, leave COUNT(*) alone. But it would be very handy to have a
way to get an approximate table size that is more accurate than is
provided by a pg_class.reltuples that is only updated on vacuums.
If you want something cheap, you could
which wouldn't have helped.) If true, I can handle that parsing myself
easily enough without exposing RelationGetNumberOfBlocks.
Is there a way to get EXPLAIN results in a non-text-formatted way for
easier use ?
I'm asking, because it seems the feature set grows by the minute in
Steve Wampler [EMAIL PROTECTED] writes:
Tom Lane wrote:
If you want something cheap, you could use the same technique the
planner uses nowadays: take RelationGetNumberOfBlocks() (which is
guaranteed accurate) and multiply by reltuples/relpages.
Yes - this would be an excellent approximation
I'm also claiming that a true count for any active table is
meaningless and am *not* suggesting that effort be spent on trying
to produce such a true count.
That's a pretty big assumption that would in fact be WRONG.
We have managers interested in counting the number of objects we have
around
Christopher Browne wrote:
I'm also claiming that a true count for any active table is
meaningless and am *not* suggesting that effort be spent on trying
to produce such a true count.
That's a pretty big assumption that would in fact be WRONG.
Please reread the message from Bruno and
On Fri, Jul 08, 2005 at 06:08:03PM +0200, PFC wrote:
which wouldn't have helped.) If true, I can handle that parsing myself
easily enough without exposing RelationGetNumberOfBlocks.
Is there a way to get EXPLAIN results in a non-text-formatted way
for easier use ?
Hello all you PostgreSQL/SQL gurus!
I've started using PostgreSQL pretty recently, and I am quite
disturbed about the performance of a simple SELECT COUNT(*) FROM
table. What should (in my mind) be a nearly instantaneous operation
instead takes nearly 700ms in a table with only 87k rows
On Thu, Jul 07, 2005 at 03:48:39PM -0700, Varun Mehta wrote:
I've started using PostgreSQL pretty recently, and I am quite
disturbed about the performance of a simple SELECT COUNT(*) FROM
table. What should (in my mind) be a nearly instantaneous operation
instead takes nearly 700ms in
[EMAIL PROTECTED] (Varun Mehta) writes:
If I run an EXPLAIN on this query I can see that it is doing a
sequential scan, which seems quite needless, as surely this
information is cached in some secret location.
That would in fact surely *NOT* be the case.
If you have multiple users performing
Chris Browne [EMAIL PROTECTED] writes:
[EMAIL PROTECTED] (Varun Mehta) writes:
If I run an EXPLAIN on this query I can see that it is doing a
sequential scan, which seems quite needless, as surely this
information is cached in some secret location.
[ example scenario snipped ]
If there were
21 matches
Mail list logo