Win2K Questions

Please don't tinker with your email address if you're posting to lists. Grab a
hotmail account or something.

On Thursday 07 Nov 2002 4:24 pm, SV wrote:

How does the performance of PostgreSQL stack up to SQL Server 2000 or
Oracle? Is it even aimed at being an enterprise level database?

Depends on usage patterns and how you build your application. There are a
couple of oddities with workarounds: count() and max() aren't very optimised
for example. There are plenty of people who have replaced MSSQL or Oracle
with PG so for many people they are comparable. Don't forget some of the
money you save on licencing can go on better hardware.

In terms of features, replication needs work and we're still waiting on nested
transactions. An "official" replication system is due in 7.4 I think. Other
than that the 7.x versions are very mature, the imminent 7.3 has added
schemas and functions that can return a set of results (among other goodies)

What is VACUUM? I have seen this mentioned in various places, saying that
it needs to be run at times. What is it exactly, why does it need to be
run, and is there any way to automate it's running?

There are two types of vacuuming - one recovers used space, since PG uses MVCC
an update is equivalent to a delete and insert and deletions are just marked
as such, without the files being compacted.
The second is analysing the tables to keep the stats up to date. This helps PG
determine when it should use an index vs a scan.

Lastly, where can I download an executable for Win2K so I can go ahead and
take it for a spin? I already found the pgAdmin app for administration
purposes, so I just need the database engine itself. Also, any tips for
installation on 2K would be most appreciated.

Look for something called cygwin. I think you can get a binary download of it
and postgresql. It's probably easier to run it on Linux/*BSD though - it's
been running on unix-like systems for years.

--
Richard Huxton

Richard Huxton wrote:

Depends on usage patterns and how you build your application. There are a
couple of oddities with workarounds: count() and max() aren't very optimised
for example.

You can 'fix' the max() SNAFU with a new query of the form
"select field from tbl limit 1 order by field desc" (not precise syntax, but the idea is correct)

I call it a SNAFU since it I hate to have to change queries from something obvious to a more obscure format just to work around an optimizer issue.

Not sure if there is an equivalent query to make count() work faster

Charlie

--

Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com

Richard Huxton wrote:

In terms of features, replication needs work and we're still
waiting on nested transactions. An "official" replication system
is due in 7.4 I think. Other than that the 7.x versions are very
mature, the imminent 7.3 has added schemas and functions that
can return a set of results (among other goodies)

I may do nested transactions for 7.4.

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

Charles H. Woloszynski wrote:

Richard Huxton wrote:

Depends on usage patterns and how you build your application. There are a
couple of oddities with workarounds: count() and max() aren't very optimised
for example.

You can 'fix' the max() SNAFU with a new query of the form
"select field from tbl limit 1 order by field desc" (not precise
syntax, but the idea is correct)

I call it a SNAFU since it I hate to have to change queries from
something obvious to a more obscure format just to work around
an optimizer issue.

Not sure if there is an equivalent query to make count() work
faster

The problem with optimizing COUNT() is that different backends have
different tuple views, meaning the count from one backend could be
different than from another backend. I can't see how to optimize that.
Does oracle do it? Maybe by looking their redo segements. We don't
have those because redo is stored in the main table.

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

Here is a suggestion.

When a count(*) is computed (for all records) store that value and
unvalidate it if there is a later insert or delete on the table. Next
improvement would be to maintain a count per active transaction.

Bruce Momjian wrote:

but how do you handle the case where two people have two different
connections, and one starts a serializable transaction and adds n rows to
the table. For that transaction, there are x+n rows in the table, while
for the transaction started before his, there are only x rows. which is
the "right" answer?

On Fri, 8 Nov 2002, Jean-Luc Lachance wrote:

Scott,

You answered the question yourself. The operative keyword her is
*before* the transaction started.
You store the global count before the transaction. While in a
transaction, you save the number of inserted and deleted records. When
*all* parallel transactions are commited, you update the global count
with the total of of updated and deleted records. If a connection start
a new transaction before the other transactions have been
commited you take the global count plus the adjustment from the previous
transaction.

JLL

"scott.marlowe" wrote:

Only helps the case where you're getting a total count though, and
requires that there be a "count" variable for each table for each
transaction in progress, since each can have a different count. But it
doesn't help at all for

select count(*) from table where id >10000;

which is also pretty common. I think the real problem is that this is one
of those things that is quite hard to optimize in an MVCC database.

This solution may be best implemented in userland, by having a seperate
table that stores the counts of the tables you're interested in, and uses
the MVCC system to provide different counts to each transaction.

But the performance of updating that secondary table may be worse than
just running a count(*).

I doubt the black (gray??? :-) magic needed to do this will be put into
the backend of postgresql any time soon. But the userland solution is
something that could be quite useful.

On Fri, 8 Nov 2002, Jean-Luc Lachance wrote:

Scott,

unless id is indexed there is nothing that can be done with
select count(*) from table where id >10000;
Otherwise, the index should be scanned, not the table.

And, scanning a large table to get count(*) will always be worst than
maintaining your own count.

JLL

"scott.marlowe" wrote:

On Fri, 8 Nov 2002, Jean-Luc Lachance wrote:

Scott,

unless id is indexed there is nothing that can be done with
select count(*) from table where id >10000;
Otherwise, the index should be scanned, not the table.

Maybe, maybe not. If id>10000 is most of the table,
you're effectively scanning the whole table (in fact in that
case it'll probably opt to do a seq scan anyway) since you
don't know if the tuple is live until you can actually see
it.

And, scanning a large table to get count(*) will always be worst than
maintaining your own count.

The select may be faster, but in overall speed you may lose if there's
alot of contention on changing the count relative to the frequency of
reading the count.

It could be a good thing, but you'd have to make sure that you
could accurately reproduce the count for all the various visibility
rules that it might be executed in. At the very least it'd have to
give the right results for both base serializable and read committed
(note that in the latter you may see rows that were committed by
a transaction that was not committed at the start).

Jean-Luc Lachance <jllachan@nsd.ca> writes:

unless id is indexed there is nothing that can be done with
select count(*) from table where id >10000;
Otherwise, the index should be scanned, not the table.

Indexes don't store heap tuple visibility information; you'd need to
scan the heap as well in order to determine which tuples your
transaction can see.

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

On Friday 08 Nov 2002 5:21 pm, Bruce Momjian wrote:

Charles H. Woloszynski wrote:

Not sure if there is an equivalent query to make count() work
faster

The problem with optimizing COUNT() is that different backends have
different tuple views, meaning the count from one backend could be
different than from another backend. I can't see how to optimize that.
Does oracle do it? Maybe by looking their redo segements. We don't
have those because redo is stored in the main table.

The only way I could model it when I thought about it some time ago was as
though you had a separate table "pg_table_counts" with columns (tableoid,
count) - every insert/delete would also update this table. Then the standard
transaction-id semantics would work re: visibility of the "current" value.

Of course, this only helps in the scenario of count(*) for a real table and
nothing more complicated (count distinct, views etc). I can also imagine a
fair performance hit unless you optimised quite heavily.

--
Richard Huxton

Richard Huxton <dev@archonet.com> writes:

On Friday 08 Nov 2002 5:21 pm, Bruce Momjian wrote:

The problem with optimizing COUNT() is that different backends have
different tuple views, meaning the count from one backend could be
different than from another backend. I can't see how to optimize that.

The only way I could model it when I thought about it some time ago was as
though you had a separate table "pg_table_counts" with columns (tableoid,
count) - every insert/delete would also update this table.

The problem with that is that it would create a serialization
bottleneck: if transaction A has done an insert into table X, then every
other transaction B that wants to insert or delete in X has to wait for
A to commit or abort before B can update X's row in pg_table_counts.
That is exactly the scenario that MVCC was designed to avoid.

What it comes down to is that you can optimize "select count(*) from
foo" at the expense of slowing down *every* kind of database-update
operation. We don't think that's a win.

regards, tom lane

This explains it all.

What would be involved in adding version and visibility to the index?

It would allow for scanning the index instead of the whole table for
many of the count() request.

JLL

Neil Conway wrote:

Jean-Luc Lachance <jllachan@nsd.ca> writes:

What would be involved in adding version and visibility to the index?

* Index bloat. An index entry is currently 8 bytes plus the index key,
eg 12 bytes for an int4 index. Version info would add 12 bytes.
Doubling the size of indexes would double the time for index scans.

* Update costs. Instead of one place to update when a row is updated,
now all the associated index entries would have to be updated too.

regards, tom lane

Tom Lane wrote:

Jean-Luc Lachance <jllachan@nsd.ca> writes:

What would be involved in adding version and visibility to the index?

* Index bloat. An index entry is currently 8 bytes plus the index key,
eg 12 bytes for an int4 index. Version info would add 12 bytes.
Doubling the size of indexes would double the time for index scans.

That is true for for small keys, but for varchar(20) the impact is less.

* Update costs. Instead of one place to update when a row is updated,
now all the associated index entries would have to be updated too.

The index has to be updated anyhow to reflect the new record. Doesn't
it?

Jean-Luc Lachance wrote:

Tom Lane wrote:

Jean-Luc Lachance <jllachan@nsd.ca> writes:

What would be involved in adding version and visibility to the index?

* Index bloat. An index entry is currently 8 bytes plus the index key,
eg 12 bytes for an int4 index. Version info would add 12 bytes.
Doubling the size of indexes would double the time for index scans.

That is true for for small keys, but for varchar(20) the impact is less.

* Update costs. Instead of one place to update when a row is updated,
now all the associated index entries would have to be updated too.

The index has to be updated anyhow to reflect the new record. Doesn't
it?

Actually no. Index scans can go from the index to the heap, see the
tuple is dead, and move on to the next one. We do have some code in 7.3
which updates the index tuple status bit so we know not to look again.

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Jean-Luc Lachance wrote:

The index has to be updated anyhow to reflect the new record. Doesn't
it?

Actually no. Index scans can go from the index to the heap, see the
tuple is dead, and move on to the next one.

More specifically: an UPDATE operation has to insert *new* index entries
pointing at the new version of the row. It does not presently have to
touch the index entries for the prior version of the row. Similarly,
DELETE need not modify index entries at all. To maintain version status
in index entries, both those operations would have to get slower.
(The eventual cleanup of the dead index entries is handled by VACUUM,
which we hope is not critical to interactive performance.)

I also think that Jean-Luc is underestimating the significance of the
index-bloat issue. The primary reason to have an index at all is that
it's much smaller than the table it indexes, and therefore is
considerably cheaper to scan. Increasing the size of index entries
is a fundamental blow to their usefulness.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Jean-Luc Lachance wrote:

The index has to be updated anyhow to reflect the new record. Doesn't
it?

Actually no. Index scans can go from the index to the heap, see the
tuple is dead, and move on to the next one.

More specifically: an UPDATE operation has to insert *new* index entries
pointing at the new version of the row. It does not presently have to
touch the index entries for the prior version of the row. Similarly,
DELETE need not modify index entries at all. To maintain version status
in index entries, both those operations would have to get slower.
(The eventual cleanup of the dead index entries is handled by VACUUM,
which we hope is not critical to interactive performance.)

Also, consider how hard it is to find the index entries matching a given
heap row being updated. Being able to skip that step is a big win for
UPDATE and DELETE. The nice thing is that it is updated later when
someone accesses it.

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