Thread

Loading optimization

Gary Wesley <gary@db.stanford.edu> — 2001-01-03T18:21:30Z

Is there any advantage to having data sorted before populating it into a
table?
(In 6.5)

Gary Wesley

Re: Loading optimization

Brett W. McCoy <bmccoy@chapelperilous.net> — 2001-01-06T21:45:26Z

On Wed, 3 Jan 2001, Gary Wesley wrote:

> Is there any advantage to having data sorted before populating it into a
> table?

None at all, AFAIK.  In fact, if you are doing a bulk copy, you should
build your indexes after the data is loaded -- you'll get ebtter
performance during the load.

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
This sentence no verb.

Re: Loading optimization

Ian Harding <iharding@pakrat.com> — 2001-01-07T19:14:05Z

Gary Wesley wrote:

> Is there any advantage to having data sorted before populating it into a
> table?
> (In 6.5)
>
> Gary Wesley

Yes.  You have effectively loaded it with a clustered index.  If you
cluster an index on the sort column after loading it, the sort will remain
in effect and will speed queries/joins that use that column.

Ian

Re: Re: Loading optimization

Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2001-01-09T12:16:46Z

Ian Harding wrote:
> 
> Gary Wesley wrote:
> 
> > Is there any advantage to having data sorted before populating it into a
> > table?
> > (In 6.5)
> >
> > Gary Wesley
> 
> Yes.  You have effectively loaded it with a clustered index.  If you
> cluster an index on the sort column after loading it, the sort will remain
> in effect and will speed queries/joins that use that column.

But does postgres actually use the fact that the data is clustered? I 
keep thinking that here I could cluster all our data such that a
sequential
search is almost always a bad idea but I have no idea how to relate that
fact to postgres...
-- 
Martijn van Oosterhout <kleptog@cupid.suninternet.com>
http://cupid.suninternet.com/~kleptog/

Re: Re: Loading optimization

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-09T15:51:35Z

> Ian Harding wrote:
> > 
> > Gary Wesley wrote:
> > 
> > > Is there any advantage to having data sorted before populating it into a
> > > table?
> > > (In 6.5)
> > >
> > > Gary Wesley
> > 
> > Yes.  You have effectively loaded it with a clustered index.  If you
> > cluster an index on the sort column after loading it, the sort will remain
> > in effect and will speed queries/joins that use that column.
> 
> But does postgres actually use the fact that the data is clustered? I 
> keep thinking that here I could cluster all our data such that a
> sequential
> search is almost always a bad idea but I have no idea how to relate that
> fact to postgres...

Well, clustering certainly speeds up index access to multiple heap
values because duplicate values are all on the same heap page.  One
thing that is missing is that there is no preference for index scans for
clustered indexes.

Because the clustering is not permanent, but becomes unclustered as data
is added/modified, there is no easy way to know if the clustering is
still valid.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Re: Loading optimization

Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-09T17:59:45Z

Martijn van Oosterhout <kleptog@cupid.suninternet.com> writes:
> But does postgres actually use the fact that the data is clustered?

The planner has no idea that the table is clustered, and will estimate
indexscan costs on the assumption that the data is randomly ordered in
the table.  So you're likely to get a seqscan plan for queries where
indexscan would actually be faster.  This is something we need to fix,
but the main problem is accounting for the fact that the clustered order
will degrade over time as data is added/updated.  See past discussions
in pghackers.

The CLUSTER implementation is so shoddy at the moment that I'm hesitant
to encourage people to use it anyway :-(.  We've got to rewrite it so
that it doesn't drop other indexes, lose constraints, break foreign
key and inheritance relationships, etc etc.

			regards, tom lane

Re: Re: Loading optimization

Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2001-01-10T04:20:24Z

On Tue, Jan 09, 2001 at 10:51:35AM -0500, Bruce Momjian wrote:
> Well, clustering certainly speeds up index access to multiple heap
> values because duplicate values are all on the same heap page.  One
> thing that is missing is that there is no preference for index scans for
> clustered indexes.

Maybe that would be the simple way, just a flag. Alternatively, have VACUUM
ANALYZE estimate the "cohesiveness" of the data...

> Because the clustering is not permanent, but becomes unclustered as data
> is added/modified, there is no easy way to know if the clustering is
> still valid.

Well, in our case the table has over 1,000,000 rows and refer to items that
would appear on a bill. Since a bill is never changed after the fact, the
clustering is always in effect.

This table is a WORM table, once data is added, it is never updated. The
question is, is this typical of very large tables? If that is the case then
generally clustering would tend to stay rather than degrade.

Also, in our case, clustering by a single index is not really sufficient.
Within a single bill we would like to cluster the items by service. I was
thinking or writing a program that would do a pg_dump, order by columns as
requested and then dump it back in. I would've done it except that
Postgresql won't use the fact that it's sorted.

I for one am hoping for progress in this area. Unnessesary sequential scans
are painful when someone is waiting on the phone...

Martijn

Re: Re: Loading optimization

Ian Harding <iharding@pakrat.com> — 2001-01-11T06:21:01Z

Tom Lane wrote:

> Martijn van Oosterhout <kleptog@cupid.suninternet.com> writes:
> > But does postgres actually use the fact that the data is clustered?
>
> The planner has no idea that the table is clustered, and will estimate
> indexscan costs on the assumption that the data is randomly ordered in
> the table.  So you're likely to get a seqscan plan for queries where
> indexscan would actually be faster.  This is something we need to fix,
> but the main problem is accounting for the fact that the clustered order
> will degrade over time as data is added/updated.  See past discussions
> in pghackers.
>
> The CLUSTER implementation is so shoddy at the moment that I'm hesitant
> to encourage people to use it anyway :-(.  We've got to rewrite it so
> that it doesn't drop other indexes, lose constraints, break foreign
> key and inheritance relationships, etc etc.
>
>                         regards, tom lane

Are the problems with CLUSTER isolated to the creation of the clustering,
or the maintenance of it?  If I cluster an index before I create any
relationships, constraints, or other indexes, (or load any data for that
matter) am I going to be OK?  BTW, Microsoft recommends creating clustered
indexes first, because creating one will cause all other existing indexes
to be dropped and recreated.  That bit makes sense, since rebuilding all
your indexes might take some time, and they have to be recreated since the
data has moved, right?

Ian

Re: Re: Loading optimization

Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-11T17:38:01Z

Ian Harding <iharding@pakrat.com> writes:
> Tom Lane wrote:
>> The CLUSTER implementation is so shoddy at the moment that I'm hesitant
>> to encourage people to use it anyway :-(.  We've got to rewrite it so
>> that it doesn't drop other indexes, lose constraints, break foreign
>> key and inheritance relationships, etc etc.

> Are the problems with CLUSTER isolated to the creation of the clustering,
> or the maintenance of it?

I guess you could consider it a bug that the clustered order is not
preserved by subsequent inserts/updates, but I don't.  Otherwise the
problem is just with creation.  That effectively does something like

	SELECT * INTO temp_NNN FROM your_table ORDER BY index_var;

and then drops your_table and renames temp_NNN into place.  So all
that's copied are the column types; you lose all other auxiliary info
about the table.

Now that I look at the code, it'd be very easy to preserve constraints
(a small change in the code would allow copying them to the new table)
so maybe we should do that.  But the other issues like inheritance
relationships can't be fixed without a fundamentally different
implementation method, one that preserves the identity (OID) of the
table.  You can find past discussions about how to do this in the
pghackers archives; it seems within reach given the changes made for
7.1, so perhaps someone will get to it in 7.2 or so.

			regards, tom lane

Re: Re: Loading optimization

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-11T18:48:29Z

> Also, in our case, clustering by a single index is not really sufficient.
> Within a single bill we would like to cluster the items by service. I was
> thinking or writing a program that would do a pg_dump, order by columns as
> requested and then dump it back in. I would've done it except that
> Postgresql won't use the fact that it's sorted.

You can create an index on all the columns, do a CLUSTER, then drop the
index and create the one you need.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Re: Loading optimization

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-11T21:06:52Z

Added to TODO (part of this is reorganization of cluster items):

* CLUSTER
        * cluster all tables at once
        * prent lose of constraints, indexes, permissions, inheritance 
        * Automatically keep clustering on a table
        * Keep statistics about clustering, perhaps during VACUUM ANALYZE
          [optimizer]


> Ian Harding <iharding@pakrat.com> writes:
> > Tom Lane wrote:
> >> The CLUSTER implementation is so shoddy at the moment that I'm hesitant
> >> to encourage people to use it anyway :-(.  We've got to rewrite it so
> >> that it doesn't drop other indexes, lose constraints, break foreign
> >> key and inheritance relationships, etc etc.
> 
> > Are the problems with CLUSTER isolated to the creation of the clustering,
> > or the maintenance of it?
> 
> I guess you could consider it a bug that the clustered order is not
> preserved by subsequent inserts/updates, but I don't.  Otherwise the
> problem is just with creation.  That effectively does something like
> 
> 	SELECT * INTO temp_NNN FROM your_table ORDER BY index_var;
> 
> and then drops your_table and renames temp_NNN into place.  So all
> that's copied are the column types; you lose all other auxiliary info
> about the table.
> 
> Now that I look at the code, it'd be very easy to preserve constraints
> (a small change in the code would allow copying them to the new table)
> so maybe we should do that.  But the other issues like inheritance
> relationships can't be fixed without a fundamentally different
> implementation method, one that preserves the identity (OID) of the
> table.  You can find past discussions about how to do this in the
> pghackers archives; it seems within reach given the changes made for
> 7.1, so perhaps someone will get to it in 7.2 or so.
> 
> 			regards, tom lane
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Re: Loading optimization

Alfred Perlstein <bright@wintelcom.net> — 2001-01-11T21:16:01Z

* Bruce Momjian <pgman@candle.pha.pa.us> [010111 13:12] wrote:
> Added to TODO (part of this is reorganization of cluster items):
> 
> * CLUSTER
>         * cluster all tables at once
>         * prent lose of constraints, indexes, permissions, inheritance 
>         * Automatically keep clustering on a table
>         * Keep statistics about clustering, perhaps during VACUUM ANALYZE
>           [optimizer]

Are there any plans (or maybe it's already done?) for a fsck-like
utility for postgresql that will automagically drop all indecies,
scan the table for bad data and then recreate the index, prefereably
with only an exclusive lock on the database being manipulated(*)?

(*) to avoid having to start up the postmaster with weird options
and/or stop activity on other databases under the postmaster's
control.

thanks,
-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

Re: Re: Loading optimization

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-11T21:17:23Z

We have talked about this, but we have not seen enought corruption cases
to warrant it.


> * Bruce Momjian <pgman@candle.pha.pa.us> [010111 13:12] wrote:
> > Added to TODO (part of this is reorganization of cluster items):
> > 
> > * CLUSTER
> >         * cluster all tables at once
> >         * prent lose of constraints, indexes, permissions, inheritance 
> >         * Automatically keep clustering on a table
> >         * Keep statistics about clustering, perhaps during VACUUM ANALYZE
> >           [optimizer]
> 
> Are there any plans (or maybe it's already done?) for a fsck-like
> utility for postgresql that will automagically drop all indecies,
> scan the table for bad data and then recreate the index, prefereably
> with only an exclusive lock on the database being manipulated(*)?
> 
> (*) to avoid having to start up the postmaster with weird options
> and/or stop activity on other databases under the postmaster's
> control.
> 
> thanks,
> -- 
> -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
> "I have the heart of a child; I keep it in a jar on my desk."
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026