pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCOUNT(*) again (was Re: [HACKERS] Index/Function organized table layout)
6+ messages / 3 participants
[nested] [flat]
* COUNT(*) again (was Re: [HACKERS] Index/Function organized table layout)
@ 2003-10-04 16:07 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-10-04 16:07 UTC (permalink / raw)
To: Hannu Krosing <hannu@tm.ee>; +Cc: Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance@postgresql.org, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Hannu Krosing <hannu@tm.ee> writes:
> Christopher Browne kirjutas R, 03.10.2003 kell 00:57:
>> A while back I outlined how this would have to be done, and for it to
>> be done efficiently, it would be anything BUT simple.
> Could this be made a TODO item, perhaps with your attack plan.
If I recall that discussion correctly, no one including Christopher
thought the attack plan was actually reasonable.
What this keeps coming down to is that an optimization that helps only
COUNT(*)-of-one-table-with-no-WHERE-clause would be too expensive in
development and maintenance effort to justify its existence.
At least if you insist on an exact, MVCC-correct answer. So far as I've
seen, the actual use cases for unqualified COUNT(*) could be handled
equally well by an approximate answer. What we should be doing rather
than wasting large amounts of time trying to devise exact solutions is
telling people to look at pg_class.reltuples for approximate answers.
We could also be looking at beefing up support for that approach ---
maybe provide some syntactic sugar for the lookup, maybe see if we can
update reltuples in more places than we do now, make sure that the
autovacuum daemon includes "keep reltuples accurate" as one of its
design goals, etc etc.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: COUNT(*) again (was Re: [HACKERS] Index/Function
@ 2003-10-04 20:59 Hannu Krosing <hannu@tm.ee>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Hannu Krosing @ 2003-10-04 20:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance@postgresql.org, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Tom Lane kirjutas L, 04.10.2003 kell 19:07:
> Hannu Krosing <hannu@tm.ee> writes:
> > Christopher Browne kirjutas R, 03.10.2003 kell 00:57:
> >> A while back I outlined how this would have to be done, and for it to
> >> be done efficiently, it would be anything BUT simple.
>
> > Could this be made a TODO item, perhaps with your attack plan.
>
> If I recall that discussion correctly, no one including Christopher
> thought the attack plan was actually reasonable.
>
> What this keeps coming down to is that an optimization that helps only
> COUNT(*)-of-one-table-with-no-WHERE-clause would be too expensive in
> development and maintenance effort to justify its existence.
Please read further in my email ;)
The point I was trying to make was that faster count(*)'s is just a side
effect. If we could (conditionally) keep visibility info in indexes,
then this would also solve the problem fo much more tricky question of
index-structured tables.
Count(*) is *not* the only query that could benefit from not needing to
go to actual data table for visibilty info, The much more needed case
would be the "inveres time series" type of queries, which would
otherways trash cache pages badly.
----------------------------
Hannu
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: COUNT(*) again (was Re: [HACKERS] Index/Function organized table layout)
@ 2003-10-04 21:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Hannu Krosing <hannu@tm.ee>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-10-04 21:15 UTC (permalink / raw)
To: Hannu Krosing <hannu@tm.ee>; +Cc: Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance@postgresql.org, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Hannu Krosing <hannu@tm.ee> writes:
> The point I was trying to make was that faster count(*)'s is just a side
> effect. If we could (conditionally) keep visibility info in indexes,
I think that's not happening, conditionally or otherwise. The atomicity
problems alone are sufficient reason why not, even before you look at
the performance issues.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: COUNT(*) again (was Re: [HACKERS] Index/Function organized
@ 2003-10-05 04:20 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 2003-10-05 04:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hannu Krosing <hannu@tm.ee>; Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance@postgresql.org, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Tom Lane wrote:
> Hannu Krosing <hannu@tm.ee> writes:
> > The point I was trying to make was that faster count(*)'s is just a side
> > effect. If we could (conditionally) keep visibility info in indexes,
>
> I think that's not happening, conditionally or otherwise. The atomicity
> problems alone are sufficient reason why not, even before you look at
> the performance issues.
What are the atomicity problems of adding a create/expire xid to the
index tuples?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: COUNT(*) again (was Re: [HACKERS] Index/Function organized table layout)
@ 2003-10-05 06:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-10-05 06:08 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Hannu Krosing <hannu@tm.ee>; Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance@postgresql.org, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> I think that's not happening, conditionally or otherwise. The atomicity
>> problems alone are sufficient reason why not, even before you look at
>> the performance issues.
> What are the atomicity problems of adding a create/expire xid to the
> index tuples?
You can't update a tuple's status in just one place ... you have to
update the copies in the indexes too.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: COUNT(*) again (was Re: [HACKERS] Index/Function organized
@ 2003-10-05 13:36 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2003-10-05 13:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hannu Krosing <hannu@tm.ee>; Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance@postgresql.org, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> I think that's not happening, conditionally or otherwise. The atomicity
> >> problems alone are sufficient reason why not, even before you look at
> >> the performance issues.
>
> > What are the atomicity problems of adding a create/expire xid to the
> > index tuples?
>
> You can't update a tuple's status in just one place ... you have to
> update the copies in the indexes too.
But we don't update the tuple status for a commit, we just mark the xid
as committed. We do have lazy status bits that prevent later lookups in
pg_clog, but we have those in the index already also.
What am I missing?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-10-05 13:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-04 16:07 COUNT(*) again (was Re: [HACKERS] Index/Function organized table layout) Tom Lane <tgl@sss.pgh.pa.us>
2003-10-04 20:59 ` Re: COUNT(*) again (was Re: [HACKERS] Index/Function Hannu Krosing <hannu@tm.ee>
2003-10-04 21:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-05 04:20 ` Re: COUNT(*) again (was Re: [HACKERS] Index/Function organized Bruce Momjian <pgman@candle.pha.pa.us>
2003-10-05 06:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-05 13:36 ` Re: COUNT(*) again (was Re: [HACKERS] Index/Function organized Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox