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 the COUNT aggregate.

A few of my customers (some familiar with Oracle) are confused by the
amount of time it takes PostgreSQL to come up with the result and are
hesitating to use it because they think it’s too slow. I’ve tried to
explain to them why it is slow, but in doing so I’ve come to see that
it may be worth working on.

I've reviewed the many messages regarding COUNT(*) and have looked
through some of the source (8.0-RC4) and have arrived at the following
questions:

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...0/msg00245.php)

2. What do you think about a separate plan type such as IndexOnlyScan?
Good/stupid/what is he on?

3. Assuming that Bruce’s aforementioned statement is correct, what
hidden performance bottlenecks might there be?

4. What is the consensus of updating a per-relation value containing
the row counts?

Though not exactly like PostgreSQL, Oracle uses MVCC and performs an
index scan on a unique value for all unqualified counts. Admittedly,
counts are faster than they used to be, but this is always a complaint
I hear from open source users and professionals alike.

I’ve been pretty busy, and I still need to get the user/group quota
working with 8.0 and forward the diffs to you all, but I would be
willing to work on speeding up the count(*) if you guys give me
your input.

As always, keep up the good work!

Respectfully,

Jonah H. Harris, Senior Web Administrator
Albuquerque TVI
505.224.4814



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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 redesigns may be
>required, even at a late stage of coding
>- write visibility code into the index
>- write an additional node type to handle the new capability
>- microarchitecture performance testing so you know whether its really
>worthwhile, covering a range of cases
>- add code to the optimiser to so it can estimate the cost of using this
>and to know when to do this
>- add a column to the catalog to record whether an index has the
>visibility option
>- add code to the parser to invoke the option
>- update pg_dump so that it correctly dumps tables with that option
>- copy and adapt all of the existing tests for the new mechanism
>- document it
>
>If you really want to do all of that, I'm sure you'd get help, but
>mostly it will be you that has to drive the change through.
>
>There are some other benefits of that implementation:
>You'd be able to vacuum the index (only), allowing index access to
>remain reasonably constant, even as the table itself grew from dead
>rows.
>
>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.
>
>
>
Simon,

I am willing to take it on and I understand that the workload is mine.
As long as everyone gives me some suggestions, I'm good it being optional.

-Jonah

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Greg Stark <gsstark@mit.edu> 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 which stores a record type with 20 columns each of
> which have a 100 byte string?

Yes, and yes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Jeff Davis <jdavis-pgsql@empires.org> 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 often

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 which stores a record type with 20 columns each of
which have a 100 byte string?

--
greg


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

> 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...age-toast.html
>

Thanks for the link. It looks like it breaks it up into chunks of about
2KB. I think the conversation was mostly assuming the tables were
somewhat closer to the size of an index. If you have more than 2KB per
tuple, pretty much anything you do with an index would be faster I would
think.

My original concern was if I had a table like (x int) and then postgres
broke the visibility information away form that, that would cause
serious performance problems if postgres had to do a join just to do
"select ... where x = 5". Right?

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 often

Regards,
Jeff Davis




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

>>>>> "Jonah" == Jonah H Harris <jharris@tvi.edu> 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
Jonah> spend money on implementing this feature if it wouldn't be
Jonah> used. Remember guys, I'm just trying to help.

Systems like DB2 don't implement versioning schemes. As a result there
is no need to worry about maintaining visibility in
indexes. Index-only plans are thus viable as they require no change in
the physical structure of the index and no overhead on
update/delete/insert ops.

I don't know about Oracle, which I gather is the only commercial
system to have something like MVCC.

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Jeff Davis <jdavis-pgsql@empires.org> 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 that is interested in a just-modified
row has to block until the writer completes or rolls back.

People who hang around Postgres too long tend to think that MVCC is the
obviously correct way to do things, but much of the rest of the world
thinks differently ;-)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> 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 world
find it difficult to wrap their heads around MVCC. A big part of this
is because Gray's original paper on transaction isolation defines the
different levels based on what kind of lock acquisitions they involve.

A very nice alternative approach to defining transaction isolation is
"Generalized isolation level definitions" by Adya, Liskov and O'Neill
that appears in ICDE 2000.

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

> 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 around Postgres too long tend to think that MVCC is the
> obviously correct way to do things, but much of the rest of the world
> thinks differently ;-)

Well, that would explain why everyone is so happy with PostgreSQL's
concurrent access performance.

Thanks for the information, although I'm not sure I wanted to be
reminded about complicated locking issues ( I suppose I must have known
that at one time, but perhaps I surpressed it ;-)

Regards,
Jeff Davis


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Tue, 2005-01-18 at 12:45 -0800, Sailesh Krishnamurthy wrote:
> >>>>> "Jonah" == Jonah H Harris <jharris@tvi.edu> 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
> Jonah> spend money on implementing this feature if it wouldn't be
> Jonah> used. Remember guys, I'm just trying to help.
>
> Systems like DB2 don't implement versioning schemes. As a result there
> is no need to worry about maintaining visibility in
> indexes. Index-only plans are thus viable as they require no change in
> the physical structure of the index and no overhead on
> update/delete/insert ops.
>
> I don't know about Oracle, which I gather is the only commercial
> system to have something like MVCC.
>

Perhaps firebird/interbase also? Someone mentioned that on these lists,
I'm not sure if it's true or not.

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
compliant?

Regards,
Jeff Davis


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html