Thread

Win2K Questions

(unknown) — 2002-11-07T16:24:23Z

A company I am currently doing work for has MS SQL Server 2000 running on a
dedicated host. In the near future hosting of the website and other services
is going to be brought internal. With that they face a very large cost for
purchasing SQL Server and the various licenses. I heard a few people mention
PostgreSQL as a possible solution, and at the outset it seems to support
many of the things that would be required, however, I do have a couple
questions.

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

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?

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.

Thanks,
Steve

Re: Win2K Questions

Richard Huxton <dev@archonet.com> — 2002-11-08T16:43:22Z

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

Re: Win2K Questions

Charles H. Woloszynski <chw@clearmetrix.com> — 2002-11-08T17:00:30Z

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

Re: Win2K Questions

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-11-08T17:14:59Z

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

Re: Win2K Questions

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-11-08T17:21:19Z

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

Re: Win2K Questions

Jean-Luc Lachance <jllachan@nsd.ca> — 2002-11-08T19:51:39Z

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:
> 
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org

Re: Win2K Questions

scott.marlowe <scott.marlowe@ihs.com> — 2002-11-08T21:05:10Z

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:

> 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:
> > 
> > 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
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> > 
> > http://archives.postgresql.org
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Re: Win2K Questions

Jean-Luc Lachance <jllachan@nsd.ca> — 2002-11-08T22:03:39Z

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:
> 
> 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:
> 
> > 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:
> > >
> > > 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
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org

Re: Win2K Questions

scott.marlowe <scott.marlowe@ihs.com> — 2002-11-08T22:28:40Z

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,
> 
> 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:
> > 
> > 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:
> > 
> > > 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:
> > > >
> > > > 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
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 6: Have you searched our list archives?
> > > >
> > > > http://archives.postgresql.org
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
> > >
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> > 
> > http://archives.postgresql.org
>

Re: Win2K Questions

Jean-Luc Lachance <jllachan@nsd.ca> — 2002-11-08T23:06:27Z

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:
>[...]
> 
> select count(*) from table where id >10000;

>[...]
 
> 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,
> >
> > 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:
> > >
> > > 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:
> > >
> > > > 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.

Re: Win2K Questions

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-11-08T23:25:29Z

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

Re: Win2K Questions

Neil Conway <neilc@samurai.com> — 2002-11-08T23:26:01Z

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

Re: Win2K Questions

Richard Huxton <dev@archonet.com> — 2002-11-09T16:34:16Z

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

Re: Win2K Questions

Tom Lane <tgl@sss.pgh.pa.us> — 2002-11-10T05:13:51Z

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

Re: Win2K Questions

Jean-Luc Lachance <jllachan@nsd.ca> — 2002-11-11T16:26:15Z

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

Re: Win2K Questions

Tom Lane <tgl@sss.pgh.pa.us> — 2002-11-11T17:55:32Z

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

Re: Win2K Questions

Jean-Luc Lachance <jllachan@nsd.ca> — 2002-11-11T19:50:05Z

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?

> 
>                         regards, tom lane

Re: Win2K Questions

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-11-12T04:31:02Z

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

Re: Win2K Questions

Tom Lane <tgl@sss.pgh.pa.us> — 2002-11-12T05:06:35Z

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

Re: Win2K Questions

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-11-12T05:09:27Z

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

Re: Win2K Questions

Jean-Luc Lachance <jllachan@nsd.ca> — 2002-11-12T16:05:23Z

Hey Tom,

Believe me I do not underestimate the impact.  Scanning the index is
different from navigating the index down the tree.

I work with fairly wide tables -- 20 to 30 times the width of the
indexed field, so even if the index was to double in size, it would
still be an order of magnitude smaller than the table.

The primary reason to have an index at all is that, because of its
structure, it allows quick access to the underlying record.

JLL


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